Home » RDBMS Server » Performance Tuning » Query with aggregate function not using index
Query with aggregate function not using index [message #285938] Thu, 06 December 2007 02:38 Go to next message
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 #285939 is a reply to message #285938] Thu, 06 December 2007 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you have 2 indexes or 1 index on both columns?
Are you statistics up to date?
What is your Oracle version?
What is the ratio of rows for this value among the total?

Regards
Michel
Re: Query with aggregate function not using index [message #285943 is a reply to message #285939] Thu, 06 December 2007 02:59 Go to previous messageGo to next message
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 #285946 is a reply to message #285943] Thu, 06 December 2007 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle seems to estimate that it is better to scan the overall table.
Try to force the index with a hint then you'll see if this give a better answer.

Regards
Michel

[Updated on: Thu, 06 December 2007 03:09]

Report message to a moderator

Re: Query with aggregate function not using index [message #286015 is a reply to message #285938] Thu, 06 December 2007 05:21 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
What is the data type of DIMDATEID column?
Re: Query with aggregate function not using index [message #286037 is a reply to message #286015] Thu, 06 December 2007 06:15 Go to previous messageGo to next message
ndahafa
Messages: 16
Registered: November 2006
Junior Member
DIMDATEID is a numeric field

I tried the query with an index hint, no difference.
Re: Query with aggregate function not using index [message #286080 is a reply to message #286037] Thu, 06 December 2007 07:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

no difference.

No difference in what? Execution plan? Response time? Logical IO?...

Regards
Michel
Re: Query with aggregate function not using index [message #286203 is a reply to message #285938] Thu, 06 December 2007 19:45 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Outer Join is very very slow
Next Topic: the query is too slow
Goto Forum:
  


Current Time: Sun Dec 04 00:08:10 CST 2016

Total time taken to generate the page: 0.07540 seconds