Re: Views, predicates and partition-elimination

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
>
>
Received on Sat Apr 21 2001 - 10:00:51 CDT