Re: how to partition pruning when joining tables

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 15 Jun 2005 22:50:18 +0200
Message-ID: <d8q4ab$4m5$00$1@news.t-online.com>

linda schrieb:
> Hi,
>
> I found it that you need to hard-code the partition key values in the
> where clause for oracle to do partition pruning. If you join tables
> together and one of the table has the partition key values in it,
> oracle somehow won't prune partitions. Example:
>
> -- will prune
> --pmt_cyl_st_id is the partition key
>
> select /*+ PARALLEL(lfs 16) */
> fncl_ast_id,ln_lpi_dt
> from ln_fncl_st lfs
> where lfs.pmt_cyl_st_id in (300638318, 300638330) ;
>
> -- won't prune
> insert into ids(id) values (300638318);
> insert into ids(id) values (300638330);
> commit;
> select /*+ PARALLEL(lfs 16) */
> fncl_ast_id,ln_lpi_dt
> from ln_fncl_st lfs, ids
> where lfs.pmt_cyl_st_id = ids.id
> ;
>
> Do you ever experience the same? How can you make Oracle to prune
> partition when the values are in a table?
>
> Thanks,
> Linda
>

There are many issues on Metalink regarding partition pruning , maybe you hit one of them, it is difficult to guess with so little information you have provided. But, (assuming you have a range partitions ) consider this - maybe it is appliable in your case:

SQL> create table p_table(id number,text varchar2(100))

   2 partition by range(id)(

   3  partition p1 values less than(100),
   4  partition p2 values less than(200),
   5  partition p3 values less than(300),
   6  partition p4 values less than(400),
   7  partition p5 values less than(500),
   8  partition p6 values less than(600),
   9  partition p7 values less than(700),
  10  partition p8 values less than(800),
  11  partition p9 values less than(900),

  12 partition p10 values less than(1000))   13 /

Tabelle wurde angelegt.

SQL> create table ids(id number);

Tabelle wurde angelegt.

SQL> begin

   2 for i in 1..999 loop
   3 insert into p_table values(i,i||' Line');    4 end loop;
   5 end;
   6 /

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> insert into ids values(295);

1 Zeile wurde erstellt.

SQL> c/295/567

   1* insert into ids values(567)
SQL> r

   1* insert into ids values(567)

1 Zeile wurde erstellt.

SQL> commit;

Transaktion mit COMMIT abgeschlossen.

SQL> set autotrace on explain
SQL> select text from p_table p,ids i where p.id=i.id;

TEXT

---

 

---

 

---

 

---

295 Line
567 Line

Ausführungsplan

---

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=820 Bytes=639
           60)

    1    0   HASH JOIN (Cost=6 Card=820 Bytes=63960)
    2    1     TABLE ACCESS (FULL) OF 'IDS' (Cost=2 Card=82 Bytes=1066)
    3    1     PARTITION RANGE (ALL)
    4    3       TABLE ACCESS (FULL) OF 'P_TABLE' (Cost=3 Card=820 Byte
           s=53300)

SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'IDS')

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> select text from p_table p,ids i where p.id=i.id;

TEXT

---

 

---

 

---

 

---

295 Line
567 Line

Ausführungsplan

---

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=820 Bytes=565
           80)

    1    0   NESTED LOOPS (Cost=4 Card=820 Bytes=56580)
    2    1     TABLE ACCESS (FULL) OF 'IDS' (Cost=3 Card=2 Bytes=8)
    3    1     PARTITION RANGE (ITERATOR)
    4    3       TABLE ACCESS (FULL) OF 'P_TABLE' (Cost=1 Card=410 Byte
           s=26650)

SQL> Notice the change in plan from PARTITION RANGE (ALL) to PARTITION RANGE (ITERATOR) after statistics on lookup table were collected.

Best regards

Maxim Received on Wed Jun 15 2005 - 15:50:18 CDT