Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: rage partitioning; engine not picking up partition
"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