There are several scanning methods under Oracle 10g, note that the last scanning method, when the partition column is calculated, will not walk the partition, which is calculated with the index column will result in the same as the index.
--Scan individual partitions PARTITION RANGE single
--Continuous scanning of multiple partitions PARTITION RANGE ITERATOR
--Non-sequential scanning of multiple partitions PARTITION RANGE inlist
--Scan all partitions PARTITION RANGE all
sql> drop table T_range purge;
Sql> CREATE TABLE T_range (ID number not NULL PRIMARY KEY, test_date Date) partition by range (test_date)
(
Partition p_1 values less than (to_date (' 2013-12-01 ', ' yyyy-mm-dd ')),
Partition p_2 values less than (to_date (' 2014-02-01 ', ' yyyy-mm-dd ')),
Partition P_3 values less than (to_date (' 2014-03-01 ', ' yyyy-mm-dd ')),
Partition P_4 values less than (to_date (' 2014-04-01 ', ' yyyy-mm-dd ')),
Partition P_5 values less than (to_date (' 2014-05-01 ', ' yyyy-mm-dd ')),
Partition P_6 values less than (to_date (' 2014-06-01 ', ' yyyy-mm-dd ')),
Partition P_max values less than (MAXVALUE)
) nologging;
sql> Insert/*+append */into T_range select RowNum,
To_date (To_char (sysdate-140, ' J ') +
Trunc (Dbms_random.value (0, 80)),
' J ')
From dual
Connect by rownum <= 100000;
sql> commit;
sql> exec dbms_stats.gather_table_stats (user, ' T_range ');
Sql> Select To_char (t.test_date, ' yyyy-mm '), COUNT (1) from T_range t
Group BY To_char (T.test_date, ' yyyy-mm ');
To_char COUNT (1)
------- ----------
2014-01 38803
2014-03 11242
2013-12 15107
2014-02 34848
Sql> Set Autotrace traceonly
--Scan a single partition
Sql> SELECT * from t_range where test_date = to_date (' 2014-04-28 ', ' yyyy-mm-dd ');
Execution plan
----------------------------------------------------------
Plan Hash value:3010141842
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22 | 2 (0) | 00:00:01 | | |
| 1 | PARTITION RANGE single| | 1 | 22 | 2 (0) | 00:00:01 | 5 | 5 |
|* 2 | TABLE ACCESS Full | T_range | 1 | 22 | 2 (0) | 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-filter ("Test_date" =to_date (' 2014-04-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss '))
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
3 Consistent gets
0 physical Reads
0 Redo Size
327 Bytes sent via sql*net to client
374 bytes received via sql*net from client
1 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
0 rows processed
--Continuous scanning of multiple partitions
Sql> SELECT * from T_range
where Test_date <= to_date (' 2014-04-28 ', ' yyyy-mm-dd ')
and Test_date >= to_date (' 2014-02-28 ', ' yyyy-mm-dd ');
Execution plan
----------------------------------------------------------
Plan Hash value:1921532398
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12556 | 147k| 28 (0) | 00:00:01 | | |
| 1 | PARTITION RANGE iterator| | 12556 | 147k| 28 (0) | 00:00:01 | 3 | 5 |
|* 2 | TABLE ACCESS Full | T_range | 12556 | 147k| 28 (0) | 00:00:01 | 3 | 5 |
----------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-filter ("Test_date" >=to_date (' 2014-02-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and
"Test_date" <=to_date (' 2014-04-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
956 consistent gets
0 physical Reads
0 Redo Size
309138 Bytes sent via sql*net to client
9515 bytes received via sql*net from client
832 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
12453 rows processed
--Non-sequential scanning of multiple partitions
Sql> SELECT * from T_range
where test_date = to_date (' 2014-04-28 ', ' yyyy-mm-dd ')
or test_date = to_date (' 2014-02-28 ', ' yyyy-mm-dd ');
Execution plan
----------------------------------------------------------
Plan Hash value:2021067984
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1678 | 20136 | 21 (0) | 00:00:01 | | |
| 1 | PARTITION RANGE inlist| | 1678 | 20136 | 21 (0) | 00:00:01 | KEY (I) | KEY (I) |
|* 2 | TABLE ACCESS Full | T_range | 1678 | 20136 | 21 (0) | 00:00:01 | KEY (I) | KEY (I) |
--------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-filter ("Test_date" =to_date (' 2014-02-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') OR
"Test_date" =to_date (' 2014-04-28 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
175 consistent gets
0 physical Reads
0 Redo Size
22646 Bytes sent via sql*net to client
1265 bytes received via sql*net from client
Sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1211 rows processed
--Scan all partitions
Sql> SELECT * from T_range where To_char (test_date, ' yyyy-mm-dd ') = ' 2014-04-01 ';
Execution plan
----------------------------------------------------------
Plan Hash value:2128486036
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 994 | 11928 | 59 (4) | 00:00:01 | | |
| 1 | PARTITION RANGE all| | 994 | 11928 | 59 (4) | 00:00:01 | 1 | 7 |
|* 2 | TABLE ACCESS Full | T_range | 994 | 11928 | 59 (4) | 00:00:01 | 1 | 7 |
-----------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
2-filter (To_char (internal_function ("Test_date"), ' yyyy-mm-dd ') = ' 2014-04-01 ')
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
272 consistent gets
0 physical Reads
0 Redo Size
327 Bytes sent via sql*net to client
374 bytes received via sql*net from client
1 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
0 rows processed
