OraFAQ Forum: Performance Tuning » Query with aggregate function not using index

Home » RDBMS Server » Performance Tuning » Query with aggregate function not using index

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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 #285939 is a reply to message #285938]

Thu, 06 December 2007 02:49

Michel Cadot
Messages: 68665
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

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)

Report message to a moderator

Re: Query with aggregate function not using index [message #285946 is a reply to message #285943]

Thu, 06 December 2007 03:08

Michel Cadot
Messages: 68665
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

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

What is the data type of DIMDATEID column?

Report message to a moderator

Re: Query with aggregate function not using index [message #286037 is a reply to message #286015]

Thu, 06 December 2007 06:15

ndahafa
Messages: 16
Registered: November 2006

Junior Member

DIMDATEID is a numeric field

I tried the query with an index hint, no difference.

Report message to a moderator

Re: Query with aggregate function not using index [message #286080 is a reply to message #286037]

Thu, 06 December 2007 07:21

Michel Cadot
Messages: 68665
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

Report message to a moderator

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

Report message to a moderator

Previous Topic:	Outer Join is very very slow
Next Topic:	the query is too slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 10:53:37 CDT 2024