Re: partition

On Feb 20, 4:43 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> James schrieb:
>
>
>
>
>
> > Hi,
>
> > I have a view that joins two partitioned (range) tables. They have
> > the same partition key - quarter of the year.
>
> > The view looks like this:
> > create view my_view as(
> > select a.id, a.name, a.quarter, b.id, b.name, b.quarter
> > from tab1 a, tab2 b
> > where a.id = b.id(+) and a.quarter = b.quarter(+))
>
> > And I am invoking the view like this:
> > select * from my_view where quarter = 4
>
> > Due to the outter join, Oracle always scans all 8 partitions from both
> > tables. What do I have to do to get it to realize that I am only
> > asking for the data of the forth quarter and use the proper
> > partition? Thanks!
>
> > James.
>
> Could you provide the Oracle version, exact DDL and explain plan for
> your query?
> In my testcase partition pruning works perfectly ...
>
> SQL> create table tab1(id number,name varchar2(100),quarter number)
> 2 partition by range(quarter)
> 3 (partition p1 values less than(2),
> 4 partition p2 values less than(3),
> 5 partition p3 values less than(4),
> 6 partition p4 values less than(5)
> 7 )
> 8 /
>
> Table created.
>
> SQL> create table tab2(id number,name varchar2(100),quarter number)
> 2 partition by range(quarter)
> 3 (partition p1 values less than(2),
> 4 partition p2 values less than(3),
> 5 partition p3 values less than(4),
> 6 partition p4 values less than(5)
> 7 )
> 8 /
>
> Table created.
>
> SQL> create or replace view my_view as(
> 2 select a.id,
> 3 a.name,
> 4 a.quarter,
> 5 b.id b_id,
> 6 b.name b_name,
> 7 b.quarter b_quarter
> 8 from tab1 a, tab2 b
> 9 where a.id = b.id(+) and a.quarter = b.quarter(+))
> 10 /
>
> View created.
>
> SQL> explain plan for
> 2 select * from my_view where quarter=4;
>
> Explained.
>
> SQL> select * from table(dbms_xplan.display);
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------­--------------------------------------------------
> Plan hash value: 1498317803
>
> ---------------------------------------------------------------------------­---------------------
> | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
> Time | Pstart| Pstop |
> ---------------------------------------------------------------------------­---------------------
> | 0 | SELECT STATEMENT | | 1 | 156 | 5 (20)|
> 00:00:01 | | |
> |* 1 | HASH JOIN OUTER | | 1 | 156 | 5 (20)|
> 00:00:01 | | |
> | 2 | PARTITION RANGE SINGLE| | 1 | 78 | 2 (0)|
> 00:00:01 | 4 | 4 |
> |* 3 | TABLE ACCESS FULL | TAB1 | 1 | 78 | 2 (0)|
> 00:00:01 | 4 | 4 |
> | 4 | PARTITION RANGE SINGLE| | 1 | 78 | 2 (0)|
> 00:00:01 | 4 | 4 |
> |* 5 | TABLE ACCESS FULL | TAB2 | 1 | 78 | 2 (0)|
> 00:00:01 | 4 | 4 |
> ---------------------------------------------------------------------------­---------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 1 - access("A"."ID"="B"."ID"(+) AND "A"."QUARTER"="B"."QUARTER"(+))
> 3 - filter("A"."QUARTER"=4)
> 5 - filter("B"."QUARTER"(+)=4)
>
> Note
> -----
> - dynamic sampling used for this statement
>
> 23 rows selected.
>
> Best regards
>
> Maxim- Hide quoted text -
>
> - Show quoted text -

Thanks for your help. We have Oracle 9i (9.2.07).

I tried your test cases on my database and the explain plan looks like this:

Row# PLAN_TABLE_OUTPUT

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)
17
18	Note: cpu costing is off

I am not sure how or Oracle is setup differently. But I did notice we did not use dynamic sampling. Thoughts? Thanks again!

James. Received on Wed Feb 21 2007 - 08:33:58 CST