Re: Views, predicates and partition-elimination

Having done a few tests (on 8.1.7.0) some very interesting anomalies show up - but bottom line - things always go a lot better if you use explicit to_date() conversions in the SQL.

(I knew I had a reason for saying that in Ch. 12/13 of the book ;)

--
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

>

>>--

>>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 <3ae0f797.342705717_at_news.bellatlantic.net>...

>>>Does anyone have any ideas?

>>>

>>>I have a table CLAIM_DEFECT, range partitioned on a date column

>>>PAY_DEALER_DATE spanning 7 years resulting in 28 partitions.  I have a

>>>view TEST_V on top of this table representing only 4 of the 7 years

>>>(PAY_DEALER_DATE between '01-jan-1997' and '31-dec-2000').

>>>

>>>As expected, the following SQL achieves the partition-elimination:

>>>(the 13 - 28 represents the start and stop partitition from the plan

>>>table)

>>>

>>>select * from test_v;

>>>

>>> SELECT STATEMENT    Cost = 33518

>>>  2.1 PARTITION RANGE ITERATOR   13-28

>>>    3.1 TABLE ACCESS FULL CLAIM_DEFECT  13-28

>>>

>>>The following SQL also behaves as expected:

>>>

>>>select * from test_v

>>>where

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

>>>

>>> SELECT STATEMENT    Cost = 8125

>>>  2.1 PARTITION RANGE ITERATOR   17-20

>>>    3.1 TABLE ACCESS FULL CLAIM_DEFECT  17-20

>>>

>>>In other words, the predicate applied to the SQL statement is used to

>>>achieve the partition-elimination, superceding that of the predicate

>>>in the view.

>>>

>>>However, if I add another predicate to the query, the order of the

>>>predicates become significant.

>>>

>>>STMT1

>>>-----

>>>select

>>>*

>>>from

>>>test_v

>>>where

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

>>>and

>>>model_code = '1234';

>>>

>>> SELECT STATEMENT    Cost = 7322

>>>  2.1 PARTITION RANGE ITERATOR   13-28

>>>    3.1 TABLE ACCESS BY LOCAL INDEX ROWID CLAIM_DEFECT  13-28

>>>      4.1 BITMAP CONVERSION TO ROWIDS

>>>        5.1 BITMAP INDEX SINGLE VALUE CDTX007 13-28

>>>

>>>STMT2

>>>-----

>>>select

>>>*

>>>from

>>>test_v

>>>where

>>>model_code = '1234'

>>>and

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

>>>

>>>  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

>>>

>>>As the sql which I am executing actually comes from a 3rd party tool,

>>>I cannot order the predicates.  Is there any other way which I can

>>>achieve the proper partition-elimination when using a view?  Is there

>>>any way I can see the sql which the optimizer actually uses when

>>>referencing a view?  10053 doesn't show the sql with the base table,

>>>only the reference to the view.

>>>

>>>Thanx in advance

>>>Paul

>>

>>

>