| Performance: partition/index not used [message #357407] |
Wed, 05 November 2008 04:26  |
wakula
Messages: 150
Registered: February 2008
Location: Poland
|
Senior Member |
|
|
Hello,
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  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
