Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: partition

Re: partition

From: James <jwilkie1_at_gmail.com>
Date: 21 Feb 2007 09:30:05 -0800
Message-ID: <1172079005.574353.294730@s48g2000cws.googlegroups.com>


On Feb 21, 10:00 am, Maxim Demenko <mdeme..._at_arcor.de> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Great! Thanks alot!

James Received on Wed Feb 21 2007 - 11:30:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US