本文首先会介绍自动类型转换的缺点,然后阐述Oracle自动类型转换的规则,并结合实例分析自动类型转换可能造成的问题。
SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL;
DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMMDD;
DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL;
TO_DATE(
--------
20210611
--下面的出错了,因为自动转换后SYSDATE变为字符串,格式是YYYYMMDD。
DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL;
SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL
*
第 1 行出现错误:
ORA-01830: 日期格式图片在转换整个输入字符串之前结束
DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMM;
会话已更改。
DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMM) FROM DUAL;
TO_DAT
------
202106
DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL;
SELECT TO_DATE(SYSDATE,YYYYMMDD) FROM DUAL
*
第 1 行出现错误:
ORA-01840: 输入值对于日期格式不够长
DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT = YYYYMONDD;
会话已更改。
DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMONDD) FROM DUAL;
TO_DATE(SYSDAT
--------------
20216月 11
DINGJUN123>ALTER SESSION SET NLS_DATE_LANGUAGE = AMERICAN;
会话已更改。
DINGJUN123>SELECT TO_DATE(SYSDATE,YYYYMONDD) FROM DUAL;
TO_DATE(SYSD
------------
2021JUN11
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t(name VARCHAR2(10));
表已创建。
DINGJUN123>INSERT INTO t VALUES(1);
已创建 1 行。
DINGJUN123>INSERT INTO t VALUES(abc);
已创建 1 行。
DINGJUN123>COMMIT;
提交完成。
DINGJUN123>CREATE INDEX idx_t ON t (name);
索引已创建。
DINGJUN123>SELECT * FROM t
2 WHERE name = 1;
ERROR:
ORA-01722: 无效数字
未选定行
DINGJUN123>SELECT * FROM t
2 WHERE name = 1;
NAME
------
1
DINGJUN123>EXPLAIN PLAN FOR
2 SELECT * FROM t
3 WHERE name = 1;
已解释。
DINGJUN123>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("NAME")=1)
Note
-----
- rule based optimizer used (consider using cbo)
DINGJUN123>EXPLAIN PLAN FOR
2 SELECT * FROM t
3 WHERE name = 1;
已解释。
DINGJUN123>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
Plan hash value: 2296882198
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN| IDX_T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NAME"=1)
Note
-----
- rule based optimizer used (consider using cbo)
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t
2 AS
3 SELECT SYSDATE+LEVEL done_date
4 FROM DUAL
5 CONNECT BY LEVEL < 10;
表已创建。
DINGJUN123>CREATE INDEX idx_t ON t (done_date);
索引已创建。
DINGJUN123>SET AUTOTRACE ON EXPLAIN
DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT=YYYY-MM-DD;
会话已更改。
DINGJUN123>SELECT * FROM t
2 WHERE TO_CHAR(done_date,YYYYMMDD) = 20210612;
DONE_DATE
----------
2021-06-12
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_CHAR(INTERNAL_FUNCTION("DONE_DATE"),YYYYMMDD)=202106
12)
Note
-----
- rule based optimizer used (consider using cbo)
DINGJUN123>SELECT * FROM t
2 WHERE done_date >= TO_DATE(20210612,YYYYMMDD)
3 AND done_date < TO_DATE(20210613,YYYYMMDD);
DONE_DATE
--------------
2021-06-12
执行计划
----------------------------------------------------------
Plan hash value: 2296882198
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN| IDX_T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DONE_DATE">=TO_DATE(2021-06-12 00:00:00, yyyy-mm-dd
hh24:mi:ss) AND "DONE_DATE"2021-06-13 00:00:00, yyyy-mm-dd
hh24:mi:ss))
Note
-----
- rule based optimizer used (consider using cbo)
OK,索引生效,符合预期,其实很少遇到不能对右值进行改写或不能改写SQL条件而必须要对左值进行转换的情况,如果真遇到这种情况,可以考虑函数索引。
DINGJUN123>SELECT 5*10+ab FROM DUAL;
SELECT 5*10+ab FROM DUAL
*
第 1 行出现错误:
ORA-01722: 无效数字
DINGJUN123>SELECT 5*10+11 FROM DUAL;
5*10+11
------------
61
自动类型转换矩阵图
DINGJUN123>SELECT parameter,value
2 FROM NLS_SESSION_PARAMETERS
3 WHERE parameter in (NLS_DATE_FORMAT,NLS_DATE_LANGUAGE) ;
PARAMETER VALUE
---------------------------------------- ---------------------------------
NLS_DATE_FORMAT YYYY-MM-DD
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t
2 (x VARCHAR2(100));
表已创建。
DINGJUN123>INSERT INTO t VALUES(SYSDATE);
已创建 1 行。
DINGJUN123>SELECT x FROM t;
X
---------------------
2021-06-11
DINGJUN123>SET SERVEROUTPUT ON
DINGJUN123>DECLARE
2 var CHAR(10);
3 BEGIN
4 SELECT 1
5 INTO var
6 FROM DUAL;
7 DBMS_OUTPUT.PUT_LINE(var is || var || ,the length is || LENGTH(var));
8 END;
9 /
var is 1 ,the length is 10
PL/SQL 过程已成功完成。
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t AS
2 SELECT CAST(3 AS NUMBER(2)) AS id FROM DUAL;
表已创建。
DINGJUN123>SELECT id/8 FROM t;
ID/8
-----------------
.375
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t(x VARCHAR2(100));
表已创建。
DINGJUN123>SELECT * FROM t WHERE x = 1;
未选定行
DINGJUN123>SET AUTOTRACE ON EXPLAIN
DINGJUN123>SELECT * FROM t WHERE x = 1;
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | TABLE ACCESS FULL| T |
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("X")=1)
Note
-----
- rule based optimizer used (consider using cbo)
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t(x BINARY_FLOAT);
表已创建。
DINGJUN123>INSERT INTO t VALUES(1234567);
已创建 1 行。
DINGJUN123>INSERT INTO t VALUES(123456789);
已创建 1 行。
DINGJUN123>COLUMN x FORMAT 999999999
DINGJUN123>SELECT * FROM t;
X
----------
1234567
123456792
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t
2 ( x VARCHAR2(10));
表已创建。
DINGJUN123>INSERT INTO t VALUES(TO_CLOB(121212121212));
INSERT INTO t VALUES(TO_CLOB(121212121212))
*
第 1 行出现错误:
ORA-12899: 列 "DINGJUN123"."T"."X" 的值太大 (实际值: 12, 最大值: 10)
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t(x BINARY_DOUBLE);
表已创建。
DINGJUN123>DROP TABLE t1;
表已删除。
DINGJUN123>CREATE TABLE t1(x BINARY_FLOAT);
表已创建。
DINGJUN123>INSERT INTO t1 VALUES(3.42E+37F);
已创建 1 行。
DINGJUN123>INSERT INTO t
2 SELECT x FROM t1;
已创建 1 行。
DINGJUN123>SELECT x FROM t;
X
----------
3.42E+037
已选择 1 行。
DINGJUN123>SELECT x FROM t1;
X
----------
3.42E+037
已选择 1 行。
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t(x BINARY_DOUBLE);
表已创建。
DINGJUN123>INSERT INTO t VALUES(1.79769313486E+39);
已创建 1 行。
DINGJUN123>DROP TABLE t1;
表已删除。
DINGJUN123>CREATE TABLE t1(x BINARY_FLOAT);
表已创建。
DINGJUN123>INSERT INTO t1
2 SELECT x FROM t;
已创建 1 行。
DINGJUN123>SELECT * FROM t;
X
----------
1.798E+039
已选择 1 行。
DINGJUN123>SELECT x FROM t1;
X
----------
Inf
已选择 1 行。
DINGJUN123>DROP TABLE t;
表已删除。
DINGJUN123>CREATE TABLE t
2 AS SELECT TO_DATE(2021-06-12,YYYY-MM-DD) x
3 FROM DUAL;
表已创建。
DINGJUN123>SELECT * FROM t WHERE x = 2021-06-12;
SELECT * FROM t WHERE x = 2021-06-12
*
第 1 行出现错误:
ORA-01861: 文字与格式字符串不匹配
DINGJUN123>ALTER SESSION SET NLS_DATE_FORMAT=YYYY-MM-DD;
会话已更改。
DINGJUN123>SELECT * FROM t WHERE x = 2021-06-12;
X
----------
2021-06-12
DINGJUN123>SELECT REPLACE(12345,4) x FROM DUAL;
X
--------
1235
DINGJUN123>SELECT 10 + 0 x FROM DUAL;
X
----------------
10
DINGJUN123>SELECT 10 || 0 x FROM DUAL;
X
------
100
DINGJUN123>SELECT ROWID FROM t;
ROWID
------------------
AAAPCiAAEAAAVfUAAA
DINGJUN123>SELECT * FROM t
2 WHERE ROWID = AAAPCiAAEAAAVfUAAA;
X
----------------
2021-06-12
字符类型内部转换表
DINGJUN123>SET SERVEROUTPUT ON
DINGJUN123>DECLARE
2 a CHAR(4):=ab ;
3 b VARCHAR2(4):=ab;
4 BEGIN
5 IF a = b THEN
6 DBMS_OUTPUT.PUT_LINE(a = b);
7 ELSE
8 DBMS_OUTPUT.PUT_LINE(a <> b);
9 END IF;
10 END;
11 /
a <> b
PL/SQL 过程已成功完成。
--返回4000,LPAD如果第1个参数是字符类型,最大只能是4000个字节
DINGJUN123>SELECT LENGTH(TO_CLOB(LPAD(a,6000,a)))
2 FROM DUAL;
LENGTH(TO_CLOB(LPAD(A,6000,A)))
-----------------------------------
4000
--返回6000,LPAD如果第1个参数是CLOB,那么最大可以达到CLOB最大长度
DINGJUN123>SELECT LENGTH(TO_CLOB(LPAD(TO_CLOB(a),6000,a))) len
2 FROM DUAL;
LEN
----------
6000
--返回6000,SUBSTR也可以接受CLOB列,则返回CLOB
DINGJUN123>SELECT LENGTH(SUBSTR(TO_CLOB(LPAD(TO_CLOB(a),6000,a)),1,6000)) len
2 FROM DUAL;
LEN
----------
6000
--报错,INITCAP不接受CLOB列,自动类型转换只允许最多4000个字节
DINGJUN123>SELECT LENGTH(INITCAP(TO_CLOB(LPAD(TO_CLOB(a),6000,a)))) len
2 FROM DUAL;
SELECT LENGTH(INITCAP(TO_CLOB(LPAD(TO_CLOB(a),6000,a)))) len
*
第 1 行出现错误:
ORA-22835: 缓冲区对于 CLOB 到 CHAR 转换或 BLOB 到 RAW 转换而言太小 (实际: 6000, 最大: 4000)
--正确,取最大4000字节
DINGJUN123>SELECT LENGTH(INITCAP(TO_CLOB(LPAD(TO_CLOB(a),4000,a))))
2 FROM DUAL;
LEN
----------
4000
DINGJUN123>SET SERVEROUTPUT ON
--在PL/SQL中LPAD(a,6000,a)是6000字节,但是在SQL中只能取到4000字节
--在PLSQL中LPAD(a,6000,a) || a是正确的,但是在SQL中就超出了4000字节的范围,运算出错
--在PL/SQL中,超出定义的最大字节数32767也出错
DINGJUN123>DECLARE
2 v_str VARCHAR2(32767);
3 BEGIN
4 v_str := LPAD(a,6000,a);
5 DBMS_OUTPUT.PUT_LINE(LENGTH(v_str));
6 v_str := v_str ||a;
7 DBMS_OUTPUT.PUT_LINE(LENGTH(v_str));
8 v_str := LPAD(a,32768,a);
9 END;
10 /
6000
6001
DECLARE
*
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 8
DINGJUN123>SELECT LENGTH(LPAD(a,6000,a)) FROM DUAL;
LENGTH(LPAD(A,6000,A))
--------------------------
4000
已选择 1 行。
DINGJUN123>SELECT LENGTH(LPAD(a,6000,a) || a) FROM DUAL;
SELECT LENGTH(LPAD(a,6000,a) || a) FROM DUAL
*
第 1 行出现错误:
ORA-01489: 字符串连接的结果过长
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129289.html
摘要:最近解决了一个生产慢查询的问题,排查问题之后发现一些比较隐匿且容易忽略的问题。所以实际在数据库查询如下可能这里发生一次隐式转换。这次查询走的是索引。 showImg(https://segmentfault.com/img/bVbmJNK?w=6000&h=4000); Photo by Iga Palacz on Unsplash 最近解决了一个生产 SQL 慢查询的问题,排查问题之...
阅读 1347·2023-01-11 13:20
阅读 1685·2023-01-11 13:20
阅读 1133·2023-01-11 13:20
阅读 1860·2023-01-11 13:20
阅读 4101·2023-01-11 13:20
阅读 2705·2023-01-11 13:20
阅读 1386·2023-01-11 13:20
阅读 3599·2023-01-11 13:20