This example still doesn't seem to work
properly by the way - it's just that some
of the other funnies that started to show
up did improve somewhat.
--
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
Jonathan Lewis wrote in message
<987935671.2658.0.nnrp-10.9e984b29_at_news.demon.co.uk>...
>
>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
>>>
>>>
>>
>
>
Received on Sun Apr 22 2001 - 05:57:30 CDT
