Re: How to use Partition in Query

From: <stevedhoward_at_gmail.com>
Date: 10 Apr 2006 16:37:46 -0700
Message-ID: <1144712266.401455.47230@v46g2000cwv.googlegroups.com>

If I am understanding you correctly, you are selecting half the data all the time. In this case. Oracle will still full scan the table (as we would expect), but it should also prune the partition, eliminating roughly half the I/O. See below for a simple test case.

SQL> create table t0410(c number) partition by range(c) 2 (partition part_1 values less than (50000), 3 partition part_max values less than(maxvalue)) 4 /

Table created.

SQL> create table t0410_a (c number);

Table created.

SQL> begin
  2 for i in 1..100000 loop
  3 insert into t0410 values(i);
  4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> begin
  2 for i in 1..100000 loop
  3 insert into t0410_a values(i);
  4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select * from t0410 where c= 20000;

Execution Plan

Plan hash value: 136084398

| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)|
Time     | Pstart| Pstop |


------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |       |     1 |    13 |    20   (5)|
00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|       |     1 |    13 |    20   (5)|
00:00:01 |     1 |     1 |
|*  2 |   TABLE ACCESS FULL    | T0410 |     1 |    13 |    20   (5)|
00:00:01 |     1 |     1 |


------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

2 - filter("C"=20000)

Note

dynamic sampling used for this statement

Statistics

          0  recursive calls
          0  db block gets
         85  consistent gets
          0  physical reads
          0  redo size
        404  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select * from t0410_a where c= 20000;

Execution Plan

Plan hash value: 1827387462

| 0 | SELECT STATEMENT | | 2 | 26 | 42 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T0410_A | 2 | 26 | 42 (3)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter("C"=20000)

Note

dynamic sampling used for this statement

Statistics

          0  recursive calls
          0  db block gets
        185  consistent gets
          0  physical reads
          0  redo size
        404  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> In each case, a FTS occurs, but we do half the I/O when the partition is pruned 85 to 185), as measured by consistent gets.

Regards,

Steve Received on Mon Apr 10 2006 - 18:37:46 CDT