必须要有等值的条件才能走索引吗?
SQL> explain plan for
2 select mdata_sn, data_channel_sn, getdate, mdata_val, syn_clock
3 from cd_moni_h_data
4 where getdate =to_date('2010','yyyy');
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 349188874
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Byte
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| CD_MONI_H_DATA | 1 | 2
|* 2 | INDEX RANGE SCAN | CD_MONI_H_DATA_IDX_2 | 1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("GETDATE"=TO_DATE('2010','yyyy'))
14 rows selected
SQL>
SQL> explain plan for
2 select mdata_sn, data_channel_sn, getdate, mdata_val, syn_clock
3 from cd_moni_h_data
4 where getdate >=to_date('2010','yyyy') and getdate
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3461087540
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 439M| 21016 (19)
|* 1 | FILTER | | | |
| 2 | PARTITION RANGE ITERATOR| | 17M| 439M| 21016 (19)
| 3 | PARTITION HASH ALL | | 17M| 439M| 21016 (19)
|* 4 | TABLE ACCESS FULL | CD_MONI_H_DATA | 17M| 439M| 21016 (19)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE('2010','yyyy')
4 - filter("GETDATE">=TO_DATE('2010','yyyy') AND "GETDATE"
17 rows selected
SQL>
SQL> explain plan for
2 select mdata_sn, data_channel_sn, getdate, mdata_val, syn_clock
3 from cd_moni_h_data
4 where getdate >to_date('2010','yyyy') and getdate
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3461087540
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17M| 439M| 21016 (19)
|* 1 | FILTER | | | |
| 2 | PARTITION RANGE ITERATOR| | 17M| 439M| 21016 (19)
| 3 | PARTITION HASH ALL | | 17M| 439M| 21016 (19)
|* 4 | TABLE ACCESS FULL | CD_MONI_H_DATA | 17M| 439M| 21016 (19)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE('2010','yyyy')
4 - filter("GETDATE">TO_DATE('2010','yyyy') AND "GETDATE"
17 rows selected