Re: partition

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Wed, 21 Feb 2007 17:00:50 +0100
Message-ID: <45DC6CB2.2040006@arcor.de>

James schrieb:
> On Feb 21, 9:41 am, Maxim Demenko <mdeme..._at_arcor.de> wrote:

>> James schrieb:
>>
>>> On Feb 20, 4:43 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>> Dynamic sampling was used due to not gathered statistics, but, imho,
>> partition  pruning should happen alone due to the fact that your filter
>> is on the partition key. That is why, i asked for your original ddl for
>> tables and view/query/explain plan - without this information one can
>> only guess...
>> Do you have maxvalue defined on your partitioned tables?
>> Are you using bind variables?
>> Maybe as well, you could  generate and post here 10053 trace to see more
>> details.
>>
>> Best regards
>>
>> Maxim

>
> I am starting to think is our Oracle settings. I used your test case
> DDL exactly as you posted since it is a lot simpler than our original
> scripts.
>
> My explain plan looks like this:
> 1
> 2
> ---------------------------------------------------------------------------­---------
> 3 | Id | Operation | Name | Rows | Bytes |
> Cost |
> Pstart| Pstop |
> 4
> ---------------------------------------------------------------------------­---------
> 5 | 0 | SELECT STATEMENT | | 2 | 312
> | 5
> | | |
> 6 |* 1 | HASH JOIN OUTER | | 2 | 312
> | 5
> | | |
> 7 |* 2 | TABLE ACCESS FULL | TAB1 | 2 | 156
> | 2
> | 4 | 4 |
> 8 |* 3 | TABLE ACCESS FULL | TAB2 | 2 | 156
> | 2
> | 4 | 4 |
> 9
> ---------------------------------------------------------------------------­---------
> 10
> 11 Predicate Information (identified by operation id):
> 12 ---------------------------------------------------
> 13
> 14 1 - access("A"."ID"="B"."ID"(+) AND
> "A"."QUARTER"="B"."QUARTER"(+))
> 15 2 - filter("A"."QUARTER"=4)
> 16 3 - filter("B"."QUARTER"(+)=4)
>
>
> Could any Oracle parameters be affecting this? Which version of
> Oracle are you using? Thanks!
>
> James.
>
>

But in this case, you are accessing only 4th partition of both tables. Maybe you are confused by TABLE ACCESS FULL , but you should look at PSTART and PSTOP columns of explain plan, they indicate which partitions   are expected to be accessed.

Best regards

Maxim Received on Wed Feb 21 2007 - 10:00:50 CST