Re: Views, predicates and partition-elimination

Which version of Oracle ?

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

To what degree have you analyzed the table and the index ?

Is the code really using constants, or bind variables ? If constants, are any of the data sets skewed.

Does it make any difference if you deliberately coerce the date strings to date type ?

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