| Query with aggregate function not using index [message #285938] |
Thu, 06 December 2007 02:38  |
ndahafa
Messages: 16
Registered: November 2006
|
Junior Member |
|
|
I am running the following query on a table which contains more than 400 million records. The table is range partitioned by DIMDATEID (monthly), there's a bitmap index on DIMDATEID and on SUBSCRIBERID.
SELECT
count(FACT_PREPAID_CALLS_TB.SUBSCRIBERID) total_calls,
sum(FACT_PREPAID_CALLS_TB.CALLCHARGE) total_callcharge
FROM
FACT_PREPAID_CALLS_TB
WHERE
FACT_PREPAID_CALLS_TB.DIMDATEID = 20070901
when i run this query, the executin plan looks like this (in Pl/sql developer):
SELECT STATEMENT, GOAL = ALL_ROWS
SORT AGGREGATE
PARTITION RANGE ALL
TABLE ACCESS FULL
How can speed up this query? adding an index hint results in the same execution plan.
[Updated on: Thu, 06 December 2007 02:45] Report message to a moderator |
|
|
|
|
|
| Re: Query with aggregate function not using index [message #285943 is a reply to message #285939] |
Thu, 06 December 2007 02:59  |
ndahafa
Messages: 16
Registered: November 2006
|
Junior Member |
|
|
I have 1 index on DIMDATEID and another on SUBSCRIBERID.
the stats are gathered every night, and i have not loaded new data since the last analyse.
Oracle version is 10g
the particular day I'm querying has 8517822 records (I did a select(count ...) which took 0.172 seconds)
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query with aggregate function not using index [message #286203 is a reply to message #285938] |
Thu, 06 December 2007 19:45 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I think you have made a mistake about the partitioning.
If the table were partitioned on DIMDATEID, then the predicate: WHERE FACT_PREPAID_CALLS_TB.DIMDATEID = 20070901 would NOT result in a PARTITION RANGE ALL, it would result in a PARTITION RANGE SINGLE.
So either:- The table is partitioned on some other column, or
- You did not include the WHERE clause when you generated the plan.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
