There are several scanning methods in oracle 10 Gb. Note that the last scanning method does not take partitions when calculating the partition columns, this is the same as the calculation of index columns, resulting in the failure to use indexes.
-- Scan a SINGLE PARTITION RANGE SINGLE
-- Continuously scans multiple partitions for PARTITION RANGE ITERATOR
-- Discontinuous scanning of multiple partitions: PARTITION RANGE INLIST
-- Scan full 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 ('2017-12-01 ', 'yyyy-mm-dd ')),
Partition p_2 values less than (to_date ('1970-02-01 ', 'yyyy-mm-dd ')),
Partition p_3 values less than (to_date ('2017-03-01 ', 'yyyy-mm-dd ')),
Partition p_4 values less than (to_date ('2017-04-01 ', 'yyyy-mm-dd ')),
Partition p_5 values less than (to_date ('1970-05-01 ', 'yyyy-mm-dd ')),
Partition p_6 values less than (to_date ('2017-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 ('1970-04-28 ', 'yyyy-mm-dd ');
Execution Plan
----------------------------------------------------------
Plan hash value: 3010141842
Bytes --------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes --------------------------------------------------------------------------------------------------
| 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 |
Bytes --------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("TEST_DATE" = TO_DATE ('2017-04-28 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss '))
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
3 consistent gets
0 physical reads
0 redo size
327 bytes sent via SQL * Net to client
374 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
-- Scan multiple partitions consecutively
SQL> select * from t_range
Where test_date <= to_date ('2017-04-28 ', 'yyyy-mm-dd ')
And test_date> = to_date ('1970-02-28 ', 'yyyy-mm-dd ');
Execution Plan
----------------------------------------------------------
Plan hash value: 1921532398
Bytes ----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes ----------------------------------------------------------------------------------------------------
| 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 |
Bytes ----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("TEST_DATE"> = TO_DATE ('2017-02-28 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') AND
"TEST_DATE" <= TO_DATE ('2017-04-28 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss '))
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
956 consistent gets
0 physical reads
0 redo size
309138 bytes sent via SQL * Net to client
9515 bytes encoded ed via SQL * Net from client
832 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12453 rows processed
-- Scan multiple partitions consecutively
SQL> select * from t_range
Where test_date = to_date ('2017-04-28 ', 'yyyy-mm-dd ')
Or test_date = to_date ('1970-02-28 ', 'yyyy-mm-dd ');
Execution Plan
----------------------------------------------------------
Plan hash value: 2021067984
Bytes --------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes --------------------------------------------------------------------------------------------------
| 0 | select statement | 1678 | 20136 | 21 (0) | 00:00:01 |
| 1 | partition range inlist | 1678 | 20136 | 21 (0) | 00:00:01 | KEY (I) |
| * 2 | table access full | T_RANGE | 1678 | 20136 | 21 (0) | 00:00:01 | KEY (I) |
Bytes --------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter ("TEST_DATE" = TO_DATE ('2017-02-28 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') OR
"TEST_DATE" = TO_DATE ('2017-04-28 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss '))
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
175 consistent gets
0 physical reads
0 redo size
22646 bytes sent via SQL * Net to client
1265 bytes encoded ed via SQL * Net from client
82 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') = '2017-04-01 ';
Execution Plan
----------------------------------------------------------
Plan hash value: 2128486036
Bytes -----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes -----------------------------------------------------------------------------------------------
| 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 |
Bytes -----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
2-filter (TO_CHAR (INTERNAL_FUNCTION ("TEST_DATE"), 'yyyy-MM-dd') = '2017-04-01 ')
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
272 consistent gets
0 physical reads
0 redo size
327 bytes sent via SQL * Net to client
374 bytes encoded ed via SQL * Net from client
1 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
