SQL> create table test (id number, dt date);
Table created.
SQL> insert into test select rownum,
2 to_date('07/01/2014 13:00','DD/MM/YYYY HH24:MI')+rownum/144
3 from dba_objects;
98900 rows created.
SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select * from test where dt between '10-APR-2014 15:40:00' and '10-APR-2014 17:10:00';
ID DT
---------- --------------------
13408 10-APR-2014 15:40:00
13409 10-APR-2014 15:50:00
13410 10-APR-2014 16:00:00
13411 10-APR-2014 16:10:00
13412 10-APR-2014 16:20:00
13413 10-APR-2014 16:30:00
13414 10-APR-2014 16:40:00
13415 10-APR-2014 16:50:00
13416 10-APR-2014 17:00:00
13417 10-APR-2014 17:10:00
10 rows selected.
SQL> explain plan for
2 select * from test where dt between '10-APR-2014 15:40:00' and '10-APR-2014 17:10:00';
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3739404388
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 220 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 10 | 220 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_FECHA | 10 | | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DT">=TO_DATE(' 2014-04-10 15:40:00', 'syyyy-mm-dd hh24:mi:ss') AND
"DT"<=TO_DATE(' 2014-04-10 17:10:00', 'syyyy-mm-dd hh24:mi:ss'))
SQL> explain plan for
2 select * from test where dt between to_timestamp('10-APR-2014 15:40:00') and '10-APR-2014 17:10:00';
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3739404388
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 66 | 28 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 3 | 66 | 28 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_FECHA | 414 | | 26 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DT"<=TO_DATE(' 2014-04-10 17:10:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(INTERNAL_FUNCTION("DT")>=TO_TIMESTAMP('10-APR-2014 15:40:00'))
SQL> explain plan for
2 select * from test
3 where dt between to_timestamp('10-APR-2014 15:40:00')
4 and to_timestamp('10-APR-2014 17:10:00');
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 66 | 73 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 3 | 66 | 73 (6)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("DT")>=TO_TIMESTAMP('10-APR-2014
15:40:00') AND INTERNAL_FUNCTION("DT")<=TO_TIMESTAMP('10-APR-2014
17:10:00'))
SQL> create index idx_fbi_test_dt on test(to_timestamp(dt));
create index idx_fbi_test_dt on test(to_timestamp(dt))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
SQL> create or replace function to_timestamp_det (v_date timestamp) return timestamp DETERMINISTIC
2 is
3 begin
4 return to_timestamp(v_date);
5 end;
6 /
Function created.
SQL> create index idx_fbi_test_dt on test(to_timestamp_det(dt));
Index created.
SQL> explain plan for
2 select * from test
3 where to_timestamp_det(dt) between to_timestamp_det('10-APR-2014 15:40:00')
4 and to_timestamp_det('10-APR-2014 17:10:00');
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 3697605509
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 223 | 9812 | 5 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TEST | 223 | 9812 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_FBI_TEST_DT | 401 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TO_TIMESTAMP_DET"(TO_TIMESTAMP('10-APR-2014
15:40:00'))<="TO_TIMESTAMP_DET"(TO_TIMESTAMP('10-APR-2014 17:10:00')))
3 - access("SYS"."TO_TIMESTAMP_DET"(INTERNAL_FUNCTION("DT"))>="TO_TIMESTAMP_DET"(TO_T
IMESTAMP('10-APR-2014 15:40:00')) AND "SYS"."TO_TIMESTAMP_DET"(INTERNAL_FUNCTION("DT"))<
="TO_TIMESTAMP_DET"(TO_TIMESTAMP('10-APR-2014 17:10:00')))
SQL> select * from test
2 where NVL(TO_CHAR(DT),'NULL') = 'NULL';
no rows selected
SQL> create index fbi_date on test(NVL(TO_CHAR(DT),'NULL'));
Index created.
SQL> explain plan for
2 select * from test
3 where NVL(TO_CHAR(DT),'NULL') = 'NULL';
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 66 | 73 (6)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 3 | 66 | 73 (6)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NVL(TO_CHAR(INTERNAL_FUNCTION("DT")),'NULL')='NULL')
SQL> select index_name, column_expression
2 from user_ind_expressions
3 where index_name='FBI_DATE';
INDEX_NAME COLUMN_EXPRESSION
----------------------------- ----------------------------------------------------
FBI_DATE NVL(TO_CHAR("DT",'DD-MON-YYYY HH24:MI:SS'),'NULL')
SQL> explain plan for
2 select * from test
3 where NVL(TO_CHAR(DT,'DD-MON-YYYY HH24:MI:SS'),'NULL') = 'NULL';
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 2626164178
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 891 | 30294 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 891 | 30294 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | FBI_DATE | 356 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NVL(TO_CHAR(INTERNAL_FUNCTION("DT"),'DD-MON-YYYY
HH24:MI:SS'),'NULL')='NULL')
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)