Re: Views, predicates and partition-elimination

It's not supposed to be possible !

There is a bug in 8.0.5, notionally fixed in 8.0.6.3, to do with histograms, indexes and partitions. This relates to Oracle using table-level stats at one point, and summed partition-level stats at another point in its calculations. This does look like the same problem, since the calculated costs appear to be about consistent with the plans.

I also have a problem with 8.0.5 at present which seems to match your problem - i.e. Oracle losing sight of the fact that it has a hard-wired partition-elimination clause, and your problem (arithmetically) looks
very close to that one.

The problem with 10053 traces is that
they keep changing even with minor changes in version number. You should have a
line near the top of each execution analysis listing the required partition range by
partition number. This is a feature that may have appeared later in 8.1.6 though.

Email the plans direct to me - I may not be able to look at them straight away, but I'll try to find some time.

Do you have table-level statistics and
partition-level statistics (user_tables and user_tab_partitions, user_indexes and
user_ind_partitions) or have some of
them gone blank.

If you can experiment -

    what happens if you drop the histograms ?     what happens if you compute statistics ?

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Paul Parker wrote in message <3ae19d60.2242507_at_news.bellatlantic.net>...


>On Sat, 21 Apr 2001 10:55:40 +0100, "Jonathan Lewis"

><jonathan_at_jlcomp.demon.co.uk> wrote:

>

>>

>>Which version of Oracle ?

>>

>

>8.1.6.0

>

>>What is the calculated cost when the predicates

>>are  in the order:

>>    >where

>>    >model_code = '1234'

>>    >and

>>    >pay_dealer_date between '01-jan-1998' and '31-dec-1998';

>>

>

> SELECT STATEMENT    Cost = 2555

>  2.1 PARTITION RANGE ITERATOR   17-20

>     3.1 TABLE ACCESS BY LOCAL INDEX ROWID CLAIM_DEFECT  17-20

> 4.1 BITMAP CONVERSION TO ROWIDS

>    5.1 BITMAP INDEX SINGLE VALUE CDTX007  17-20

>

>>To what degree have you analyzed the table

>>and the index ?

>>

>

>Both tables and indexes estimated using 5 percent.  Histograms (using

>default 75 buckets) on all indexed columns

>

>>Is the code really using constants, or bind variables ?

>>If constants, are any of the data sets skewed.

>

>Only constants.  There is some skew, but the presence of histograms

>and the resultant use of the index appears to be correct.  The only

>puzzle is different no. of partitions which is ITERATED through.

>

>>

>>Does it make any difference if you deliberately

>>coerce the date strings to date type ?

>>

>

>No.

>>

>

>On examing 10053, the output only displays statistics from the 4

>partitions used in STMT2 which seems to suggest that it has already

>decided which partitions to use.  When exactly does this elimination

>happen?

>

>Thanx for your help

>