Oracle 8.1.7.2 on Sun Solaris 2.8
I have a partitioned table where each partition contains a month worth of data. Each partition contains about 4 million rows and I have 24 months online at any one time. I also have a local partitioned index on date.
My basic query is:
SELECT * FROM myTable
WHERE myDate = some_date
OR myDate = some_other_date;
If I use explicit values for some_date and some_other_date, then the query returns in 4 seconds. For example:
The Query plan correctly eliminates partitions in the index.Code:SELECT * FROM myTable
WHERE myDate = to_date('2002/09/01','YYYY/MM/DD')
OR myDate = to_date('2002/09/15','YYYY/MM/DD');
If I use bind variables in SQL*Plus for some_date and some_other_date, then the query also returns in 4 seconds. For example:
The query also comes back in about 4 seconds and choses the correct plan.Code:DECLARE
sd DATE;
ed DATE;
BEGIN
sd := to_date('2002/09/01','YYYY/MM/DD');
ed := to_date('2002/09/15','YYYY/MM/DD');
FOR x IN (
SELECT * FROM myTable
WHERE myDate = to_date('2002/09/01','YYYY/MM/DD')
OR myDate = to_date('2002/09/15','YYYY/MM/DD')) LOOP
ctr := ctr+1;
END LOOP;
END;
However, when I use bind variables through an OCI host program (either Perl DBI or Pro*C/C++) the optimizer chooses a full table scan instead of partition elimination.
We have tried the query with both a hint for the index and without. When we use a hint for the index it does a full scan on the index but still takes a long time.
When we try the query in an OCI host program with explicit values (no bind variables) the correct plan is chosen.
This problem occurs when we use either the 9.2.0.1 client or the 8.1.7.0 OCI libraries.
This table has no statistics (because of a bug we hit with partitioned tables and the CBO) so I know (I think I know, anyway) that the RBO is being used.
I'm kind of thinking it has something to do with when the statement is parsed oracle makes assumptions about the data that may not be true to figure out the query plan. When the values are presented the plan has already been figured out incorrectly.
I know there are lots of ways around this. In fact we've already implemented "Plan B" with REF CURSORS and a package. I just want to know if anybody has seen behaviour like this before or if anybody has any ideas about why this might be happening?
