Re: partition

From: James <jwilkie1_at_gmail.com>
Date: 21 Feb 2007 07:55:47 -0800
Message-ID: <1172073342.974382.260270@l53g2000cwa.googlegroups.com>

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. Received on Wed Feb 21 2007 - 09:55:47 CST