Re: partition

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 20 Feb 2007 23:43:46 +0100
Message-ID: <45DB79A2.9070602@gmail.com>

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 Received on Tue Feb 20 2007 - 16:43:46 CST