SQL执行异常系列之——隐式转换
时间:2021-07-01 10:21:17
帮助过:4人阅读
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-18
9 rows selected.
select object_name from function where to_char(created,‘yyyymmdd hh24:mi‘)=‘20180725 12:51‘;
20180725 12:54
drop table tab;
create table tab(id number(20),datetime date);
declare
i number;
dd date;
BEGIN
dd := sysdate;
i:= 0;
for x in 1..5000 loop
--if mod(i,100)=0 then
insert 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:58
29-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 selected
Execution 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 selected
Execution 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 selected
Execution 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:58
Execution 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:58
Execution 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:42
Execution 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:42
Execution 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:42
Execution 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:42
Execution 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
SQL执行异常系列之——隐式转换
标签:pre exec statistic drop row orm fun obj ips