Hi,
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
{code}
SELECT * FROM TABLE_XXXXXXXX
WHERE COL_VAR_2 = 'ZZZZZZ'
AND COL_DATE_1 BETWEEN TO_DATE ('201307010100','YYYYMMDDHH24MI') AND TO_DATE ('201309010100','YYYYMMDDHH24MI')
AND COL_VAR_1 = 'S'
AND COL_NUM_1 = '1'
ORDER BY COL_DATE_1, COL_VAR_1, COL_VAR_3, COL_VAR_4, COL_VAR_5
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11218 (100)| | | 63 |00:00:07.02 | 4658 | 1526 | | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 4 | 11218 (1)| 913 | 975 | 63 |00:00:07.02 | 4658 | 1526 | | | |
| 2 | SORT ORDER BY | | 63 | 4 | 11218 (1)| | | 63 |00:00:07.02 | 4658 | 1526 | 2048 | 2048 | 2048 (0)|
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE_XXXXXXXX | 63 | 4 | 11217 (1)| 913 | 975 | 63 |00:00:07.01 | 4658 | 1526 | | | |
|* 4 | INDEX SKIP SCAN | INDEX_PK | 63 | 1 | 11213 (1)| 913 | 975 | 63 |00:00:06.84 | 4595 | 1463 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("COL_DATE_1">=TO_DATE(' 2013-07-01 01:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "COL_VAR_1"='S' AND "COL_VAR_2"='ZZZZZZ' AND
"COL_NUM_1"=1 AND "COL_DATE_1"<=TO_DATE(' 2013-09-01 01:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(("COL_VAR_2"='ZZZZZZ' AND "COL_NUM_1"=1 AND "COL_VAR_1"='S'))
INDEX_PK primary key index partitioned by COL_DATE_1 (day partitions):
POS COLUMN_NAME
--- -----------
1 COL_DATE_1
2 COL_VAR_1
3 COL_VAR_2
4 COL_VAR_3
5 COL_VAR_4
6 COL_VAR_5
7 COL_NUM_1
8 COL_DATE_2
SELECT * FROM TABLE_XXXXXXXX
WHERE COL_VAR_2 = 'ZZZZZZ'
AND COL_DATE_1 = TO_DATE ('201307010100','YYYYMMDDHH24MI')
AND COL_VAR_1 = 'S'
AND COL_NUM_1 = '1'
ORDER BY COL_DATE_1, COL_VAR_1, COL_VAR_3, COL_VAR_4, COL_VAR_5
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 (100)| | | 1 |00:00:00.01 | 4 | | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 1 | 5 (20)| 913 | 913 | 1 |00:00:00.01 | 4 | | | |
| 2 | SORT ORDER BY | | 1 | 1 | 5 (20)| | | 1 |00:00:00.01 | 4 | 2048 | 2048 | 2048 (0)|
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| TABLE_XXXXXXXX | 1 | 1 | 4 (0)| 913 | 913 | 1 |00:00:00.01 | 4 | | | |
|* 4 | INDEX RANGE SCAN | INDEX_PK | 1 | 1 | 3 (0)| 913 | 913 | 1 |00:00:00.01 | 3 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("COL_DATE_1"=TO_DATE(' 2013-07-01 01:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "COL_VAR_1"='S' AND "COL_VAR_2"='ZZZZZZ' AND
"COL_NUM_1"=1)
filter("COL_NUM_1"=1)
{code}
Shouldn't be RANGE SCAN both access methods?