Query is taking very long, but it looks quite obvious...
Hi,
Customer is nagging about a query they use which isn't performing well:
SELECT
Kalender.JAAR_PERIODE,
sum(FCT_VERGOEDING.BEDRAG)
FROM
FCT_VERGOEDING,
DIM_KALENDER Kalender
WHERE
( FCT_VERGOEDING.KALENDER_ID=Kalender.KALENDER_ID )
AND
Kalender.JAAR_PERIODE > 201001
GROUP BY
Kalender.JAAR_PERIODE
It's a datawarehouse and DIM_KALENDER is a view. But that isn't the problem. Table FCT_VERGOEDING contains 69million rows and is 9,5GB large. The sum will cause some problems as well. Column KALENDER_ID contains a date in format yyyymmdd but it is defined as a number.
Recreated the table in my schema and partitioned it on kalender_id on a monthly basis (year-basis before this year though) and creating a partitioned index on it, but that isn't a solution as it's still taking very long. The number of records to check is approx. 13 million, so only a small part of the table.
Customer is nagging about a query they use which isn't performing well:
SELECT
Kalender.JAAR_PERIODE,
sum(FCT_VERGOEDING.BEDRAG)
FROM
FCT_VERGOEDING,
DIM_KALENDER Kalender
WHERE
( FCT_VERGOEDING.KALENDER_ID=Kalender.KALENDER_ID )
AND
Kalender.JAAR_PERIODE > 201001
GROUP BY
Kalender.JAAR_PERIODE
It's a datawarehouse and DIM_KALENDER is a view. But that isn't the problem. Table FCT_VERGOEDING contains 69million rows and is 9,5GB large. The sum will cause some problems as well. Column KALENDER_ID contains a date in format yyyymmdd but it is defined as a number.
Recreated the table in my schema and partitioned it on kalender_id on a monthly basis (year-basis before this year though) and creating a partitioned index on it, but that isn't a solution as it's still taking very long. The number of records to check is approx. 13 million, so only a small part of the table.
0