SQL> select object_id,CREATED from dba_objects where rownum < 10; OBJECT_ID CREATED---------- ------------------ 20 25-JUL-18 46 25-JUL-18 28 25-JUL-18 15 25-JUL-18 29 25-JUL-18 3 25-JUL-18 25 25-JUL-18 41 25-JUL-18 54 25-JUL-189 rows selected.select object_name from function where to_char(created,‘yyyymmdd hh24:mi‘)=‘20180725 12:51‘;20180725 12:54drop table tab;create table tab(id number(20),datetime date);declarei number;dd date;BEGINdd := sysdate;i:= 0;for x in 1..5000 loop --if mod(i,100)=0 theninsert into tab(id,datetime) values(i,dd);dd := dd+1; i := i+1;end loop;END;/create table tab(id varchar2(20),datetime date);1289 20220429 13:5829-APR-22************************测试1************************select * from tab where id=1289 and datetime=‘29-APR-22‘;select * from tab where id=1289 and datetime=‘29-APR-22‘;SQL> no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 1995730731--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TAB | 1 | 21 | 5 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(TO_NUMBER("ID")=1289 AND "DATETIME"=‘29-APR-22‘)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 5 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 400 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed************************测试2************************select * from tab where id=‘1289‘ and datetime=‘29-APR-22‘;select * from tab where id=‘1289‘ and datetime=‘29-APR-22‘;SQL> no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 1995730731--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TAB | 1 | 21 | 5 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("ID"=‘1289‘ AND "DATETIME"=‘29-APR-22‘)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 5 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 400 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed刷新cache之后再执行SQL> select * from tab where id=‘1289‘ and datetime=‘29-APR-22‘;no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 1995730731--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 21 | 5 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TAB | 1 | 21 | 5 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("ID"=‘1289‘ AND "DATETIME"=‘29-APR-22‘)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 16 consistent gets 14 physical reads 0 redo size 400 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed************************测试3************************select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘;SQL> select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘;ID DATETIME-------------------- --------------1289 29-04-22 13:58Execution Plan----------------------------------------------------------Plan hash value: 1995730731--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TAB | 1 | 13 | 5 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("ID"=‘1289‘ AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyy ymmdd‘)=‘20220429‘)Statistics---------------------------------------------------------- 62 recursive calls 0 db block gets 51 consistent gets 0 physical reads 0 redo size 600 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed************************测试4************************create index tab_index on tab(id,to_char(datetime,‘yyyymmdd‘));SQL> /ID DATETIME-------------------- --------------1289 29-04-22 13:58Execution Plan----------------------------------------------------------Plan hash value: 4028735706-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 13 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | TAB_INDEX | 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=‘1289‘ AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘ 20220429‘)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 3 physical reads 0 redo size 600 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed************************测试5************************新创建的表select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘;SQL> / ID DATETIME---------- -------------- 1289 29-04-22 22:42Execution Plan----------------------------------------------------------Plan hash value: 1995730731--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 22 | 5 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TAB | 1 | 22 | 5 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("ID"=1289 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyym mdd‘)=‘20220429‘)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 17 consistent gets 14 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed************************测试5************************create index tab_index on tab(id,to_char(datetime,‘yyyymmdd‘));SQL> select * from tab where id=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME---------- -------------- 1289 29-04-22 22:42Execution Plan----------------------------------------------------------Plan hash value: 4028735706-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 28 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | TAB_INDEX | 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=1289 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘20 220429‘)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 10 recursive calls 0 db block gets 27 consistent gets 3 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed************************测试6************************select * from tab where id=1289 and to_char(datetime,‘yyyymmdd‘)=‘20220429‘;SQL> select * from tab where id=1289 and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME---------- -------------- 1289 29-04-22 22:42Execution Plan----------------------------------------------------------Plan hash value: 4028735706-----------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-----------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 28 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | TAB_INDEX | 1 | | 1 (0)| 00:00:01 |-----------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=1289 AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘20 220429‘)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 7 recursive calls 0 db block gets 25 consistent gets 0 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed************************测试6************************select * from tab where to_char(id)=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘;SQL> select * from tab where to_char(id)=‘1289‘ and to_char(datetime,‘yyyymmdd‘)=‘20220429‘; ID DATETIME---------- -------------- 1289 29-04-22 22:42Execution Plan----------------------------------------------------------Plan hash value: 1995730731--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 28 | 5 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TAB | 1 | 28 | 5 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(TO_CHAR("ID")=‘1289‘ AND TO_CHAR(INTERNAL_FUNCTION("DATETIME"),‘yyyymmdd‘)=‘20220429‘)Note----- - dynamic sampling used for this statement (level=2)Statistics---------------------------------------------------------- 5 recursive calls 0 db block gets 35 consistent gets 0 physical reads 0 redo size 599 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed