Home » RDBMS Server » Performance Tuning » Performance: partition/index not used (Oracle 10.2G)
Performance: partition/index not used [message #357407] Wed, 05 November 2008 04:26 Go to next message
Messages: 150
Registered: February 2008
Location: Poland
Senior Member

We have identified a strange problem:
SELECT COUNT(1) FROM source_table
WHERE eventdate >= :startdate
AND eventdate <= :enddate
AND GroupID = :groupid
AND companyID = :companyid
AND SubGroupID = 0

This query is executed using DBMS_SQL on a table that is partitioned by eventdate.
Index is set on (eventdate,GroupID,companyID,SubGroupID).
source_table is (eventdate DATE,GroupID number, companyID number, subgroupID number, datavalue custom_type_object).
I have executed this 3 times:
1) startdate = enddate => took less than 1 second
2) startdate and enddate hard-coded as strings (TO_CHAR(startdate), etc) => took less than 1 second
3) startdate and enddate specified as bound variables => took 30x longer

The order is preserved (just to show that this is not caused by the caching mechanism).

I have no access to the DB stats - but I believe that either partitions were not used correctly or the index was not used.
Can anyone tell what caused this strange problem?

[Updated on: Wed, 05 November 2008 05:19]

Report message to a moderator

Re: Performance: partition/index not used [message #357627 is a reply to message #357407] Thu, 06 November 2008 01:28 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I assume the slower one still used a single date value - not a big range.

If you are partitioning monthly, then a range as small as 3-4 days will be faster to full-partition-scan than using an index.

Oracle figures that on the balance of probability, the bind variables are going to be more often farther apart than closer together.

With v11g, Oracle can adapt its plan for different sets of bind values. But with 10g and ealier it picks one plan and sticks to it. So you either ALWAYS index or ALWAYS full scan.

If you want to always index, then add an INDEX hint or better still a CARDINALITY hint.

Ross Leishman
Previous Topic: Index a system view
Next Topic: Performance Issue: Steps that need to take care of
Goto Forum:

Current Time: Sun Oct 23 01:58:06 CDT 2016

Total time taken to generate the page: 0.04886 seconds