SQL>
SQL> create table t
2 as
3 select to_char( to_date('01-jan-1995','dd-mon-yyyy')+rownum, 'yyyymmdd' ) str_date,
4 to_date('01-jan-1995','dd-mon-yyyy')+rownum date_date
5 from all_objects
6 where rownum < 20
7 /
Table created.
SQL>
SQL> create index i1 on t(str_date);
Index created.
SQL>
SQL> create index t_date_date_idx on t(date_date);
Index created.
SQL>
SQL> analyze table t compute statistics
2 for table
3 for all indexes
4 for all indexed columns;
Table analyzed.
SQL>
SQL> set autotrace on explain
SQL> select * from t
2 where str_date between '20001231' and '20100101' and rownum < 20;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 508354683
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 |
|* 1 | COUNT STOPKEY | | | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<20)
2 - filter("STR_DATE">='20001231' AND "STR_DATE"<='20100101')
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> select * from t where date_date between to_date('20001231','yyyymmdd') and to_date('20100101','yyyymmdd') and rownum<20;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 508354683
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 |
|* 1 | COUNT STOPKEY | | | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<20)
2 - filter("DATE_DATE">=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "DATE_DATE"<=TO_DATE('2010-01-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL>
SQL> select * from t
2 where str_date between '20001231' and '20100101' and rownum<20;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 508354683
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 |
|* 1 | COUNT STOPKEY | | | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<20)
2 - filter("STR_DATE">='20001231' AND "STR_DATE"<='20100101')
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> select * from t where date_date between to_date('20001231','yyyymmdd') and to_date('20100101','yyyymmdd')
2 and rownum < 20;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 508354683
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 1 |
|* 1 | COUNT STOPKEY | | | | |
|* 2 | TABLE ACCESS FULL| T | 1 | 20 | 1 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<20)
2 - filter("DATE_DATE">=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "DATE_DATE"<=TO_DATE('2010-01-01 00:00:00',
'yyyy-mm-dd hh24:mi:ss'))
Note
-----
- cpu costing is off (consider enabling it)
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t;
Table dropped.