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: rage partitioning; engine not picking up partition

Re: rage partitioning; engine not picking up partition

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Tue, 28 Jan 2003 23:51:40 +1000
Message-ID: <FLuZ9.35167$jM5.89842@newsfeeds.bigpond.com>


"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:xD7Z9.5576$V6.7696_at_news.indigo.ie...

> James is correct .
>

<snip>

> > IIRC, I don't believe this to be correct. Oracle has something in 8+
> > called partitoned. For it to scan the proper partition you must have
> > a partioning index on the appropriate key.
> >
> > If you create an index and then analyze the table and check the
> > explain plan then you should be able to get the proper partition.
> > Otherwise, you can tell the query which partition to look in manually.
> >

Hi Cosmin,

No, you do *not* need an index on the partitioning column for Oracle to implement partition pruning. Oracle is quite clever enough to know that if you're only referencing columns from certain partition(s), then other partitions do not have to be scanned.

Simple example (best I can come up with at this time of night ....)

SQL> create table part_bowie (x number, y number, z number)   2 partition by range (x)

  3  (partition p1 values less than (2),
  4   partition p2 values less than (3),
  5   partition p3 values less than (maxvalue));

Table created.

SQL> insert into part_bowie values (1, 2, 3);

1 row created.

SQL> insert into part_bowie values (2, 3, 4);

1 row created.

SQL> insert into part_bowie values (3, 4, 5);

1 row created.

SQL> insert into part_bowie select * from part_bowie;

3 rows created.

SQL> / 6 rows created.

SQL> / 12 rows created.

SQL> / 24 rows created.

SQL> / 48 rows created.

SQL> / 96 rows created.

SQL> / 192 rows created.

SQL> / 384 rows created.

SQL> / 768 rows created.

SQL> / 1536 rows created.

SQL> / 3072 rows created.

SQL> / 6144 rows created.

SQL> / 12288 rows created.

SQL> / 24576 rows created.

SQL> / 49152 rows created.

SQL> commit;

Commit complete.

SQL> execute dbms_stats.gather_table_stats ('BOWIE', 'PART_BOWIE', NULL, 100, FA
LSE, 'FOR ALL COLUMNs', 1, 'DEFAULT', FALSE);

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;
SQL> select * from part_bowie;

98304 rows selected.

Execution Plan


   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=20 Card=98304 Byte
          s=884736)

   1    0   PARTITION RANGE (ALL)
   2    1     TABLE ACCESS (FULL) OF 'PART_BOWIE' (Cost=20 Card=98304
          Bytes=884736)





Statistics


          0  recursive calls
          0  db block gets
       6745  consistent gets
          0  physical reads
          0  redo size
    1284976  bytes sent via SQL*Net to client
      72582  bytes received via SQL*Net from client
       6555  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      98304  rows processed

SQL> select * from part_bowie where x > 2;

32768 rows selected.

Execution Plan


   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=14 Card=32768 Byte
          s=294912)

   1    0   PARTITION RANGE (ITERATOR)
   2    1     TABLE ACCESS (FULL) OF 'PART_BOWIE' (Cost=14 Card=32768
          Bytes=294912)





Statistics


          0  recursive calls
          0  db block gets
       2317  consistent gets
          0  physical reads
          0  redo size
     428629  bytes sent via SQL*Net to client
      24523  bytes received via SQL*Net from client
       2186  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      32768  rows processed

As others have mentioned, what was your execution plan ?

Richard Received on Tue Jan 28 2003 - 07:51:40 CST

Original text of this message

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