分享程序员开发的那些事...
更新时间：2022-09-13 10:53:42
[20150727]''与NULL.txt
--最近在优化sql语句时遇到''(中间没有空格)与null的情况，做一个例子来说明：
1.建立测试环境:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
SCOTT@test> set NULL NULL
SCOTT@test> select ''c10 ,NULL c10 from dual ;
C10 C10
---------- ----------
NULL NULL
--可以发现''与NULL在oracle表示是一致的，其它数据库不是很清楚。
create table t1 as select object_name v1,object_name v2,lpad('x',100,'x') pad from dba_objects ;
create table t2 as select object_name v1,object_name v2,lpad('x',100,'x') pad from dba_objects ;
create index i_t1_v1 on t1(v1);
create index i_t1_v2 on t1(v2);
create index i_t2_v1 on t2(v1);
create index i_t2_v2 on t2(v2);
--分析表，忽略。
2.继续测试:
SCOTT@test> alter session set statistics_level=all;
Session altered.
SELECT *
FROM (SELECT '' v1, v2, pad FROM t1
UNION ALL
SELECT v1, '' v2, pad FROM t2)
WHERE v1 = 'TY' OR v2 = 'TY';
SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bk8yag9trhymm, child number 1
-------------------------------------
SELECT * FROM (SELECT '' v1, v2, pad FROM t1 UNION ALL SELECT v1, '' v2, pad FROM t2)
WHERE v1 = 'TY' OR v2 = 'TY'
Plan hash value: 1505077622
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------
| 1 | VIEW | | 1 | 1020 | 183K| 497 (1)| 00:00:06 | 0 |00:00:00.01 | 2204 |
| 2 | UNION-ALL | | 1 | | | | | 0 |00:00:00.01 | 2204 |
|* 3 | TABLE ACCESS FULL| T1 | 1 | 510 | 64260 | 248 (1)| 00:00:03 | 0 |00:00:00.01 | 1102 |
|* 4 | TABLE ACCESS FULL| T2 | 1 | 510 | 64260 | 248 (1)| 00:00:03 | 0 |00:00:00.01 | 1102 |
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / from$_subquery$_001@SEL$1
2 - SET$1
3 - SEL$2 / T1@SEL$2
4 - SEL$3 / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((''='TY' OR "V2"='TY'))
4 - filter((''='TY' OR "V1"='TY'))
--可以发现一个现象，oracle两个表t1,t2选择全部扫描。
3.如果我们单独带入，相当于：
SELECT '' v1, v2, pad FROM t1 where '' = 'TY' OR v2 = 'TY';
SCOTT@test> SELECT '' v1, v2, pad FROM t1 where '' = 'TY' OR v2 = 'TY';
no rows selected
SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6d5b1w0ndb77n, child number 0
-------------------------------------
SELECT '' v1, v2, pad FROM t1 where '' = 'TY' OR v2 = 'TY'
Plan hash value: 3617692013
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS FULL| T1 | 1 | 510 | 64260 | 249 (1)| 00:00:03 | 0 |00:00:00.01 | 1102 |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((''='TY' OR "V2"='TY'))
--而如果换成NULL。
SCOTT@test> SELECT '' v1, v2, pad FROM t1 where NULL = 'TY' OR v2 = 'TY';
no rows selected
SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 74h56c2ftjjmv, child number 0
-------------------------------------
SELECT '' v1, v2, pad FROM t1 where NULL = 'TY' OR v2 = 'TY'
Plan hash value: 2539912583
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 2 | 252 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | I_T1_V2 | 1 | 2 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V2"='TY')
--很明显这样能使用索引，不过这样写好像语法不是很对,指NULL = 'TY'.
3.像上面的语句如果写成：
SELECT *
FROM (SELECT NULL v1, v2, pad FROM t1
UNION ALL
SELECT v1, NULL v2, pad FROM t2)
WHERE v1 = 'TY' OR v2 = 'TY';
SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID axaypqysnayrw, child number 0
-------------------------------------
SELECT * FROM (SELECT NULL v1, v2, pad FROM t1 UNION ALL SELECT v1, NULL v2, pad FROM t2) WHERE v1 =
'TY' OR v2 = 'TY'
Plan hash value: 2324945452
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
| 1 | VIEW | | 1 | 4 | 736 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 4 |
| 2 | UNION-ALL | | 1 | | | | | 0 |00:00:00.01 | 4 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 2 | 252 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 4 | INDEX RANGE SCAN | I_T1_V2 | 1 | 2 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 2 | 252 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
|* 6 | INDEX RANGE SCAN | I_T2_V1 | 1 | 2 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / from$_subquery$_001@SEL$1
2 - SET$1
3 - SEL$2 / T1@SEL$2
4 - SEL$2 / T1@SEL$2
5 - SEL$3 / T2@SEL$3
6 - SEL$3 / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("V2"='TY')
6 - access("V1"='TY')
--看来''与null,oracley优化cbo的分析上还是存在一些问题的。以上的测试环境是10g，换成11g继续测试看看。
4.在11g下重复测试：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create table t1 as select object_name v1,object_name v2,lpad('x',100,'x') pad from dba_objects ;
create table t2 as select object_name v1,object_name v2,lpad('x',100,'x') pad from dba_objects ;
create index i_t1_v1 on t1(v1);
create index i_t1_v2 on t1(v2);
create index i_t2_v1 on t2(v1);
create index i_t2_v2 on t2(v2);
--分析表，忽略。
SELECT *
FROM (SELECT '' v1, v2, pad FROM t1
UNION ALL
SELECT v1, '' v2, pad FROM t2)
WHERE v1 = 'TY' OR v2 = 'TY';
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bk8yag9trhymm, child number 0
-------------------------------------
SELECT * FROM (SELECT '' v1, v2, pad FROM t1 UNION ALL
SELECT v1, '' v2, pad FROM t2) WHERE v1 = 'TY' OR v2 = 'TY'
Plan hash value: 2324945452
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 0 |00:00:00.01 | 6 |
| 1 | VIEW | | 1 | 4 | 736 | 8 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
| 2 | UNION-ALL | | 1 | | | | | 0 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 2 | 252 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | I_T1_V2 | 1 | 2 | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 2 | 252 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | I_T2_V1 | 1 | 2 | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / from$_subquery$_001@SEL$1
2 - SET$1
3 - SEL$2 / T1@SEL$2
4 - SEL$2 / T1@SEL$2
5 - SEL$3 / T2@SEL$3
6 - SEL$3 / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("V2"='TY')
6 - access("V1"='TY')
--很明显在11g下这个问题不存在。10g，在10g下注意。
SELECT *
FROM (SELECT NULL v1, v2, pad FROM t1
UNION ALL
SELECT v1, NULL v2, pad FROM t2)
WHERE v1 = 'TY' OR v2 = 'TY';
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID axaypqysnayrw, child number 0
-------------------------------------
SELECT * FROM (SELECT NULL v1, v2, pad FROM t1 UNION ALL
SELECT v1, NULL v2, pad FROM t2) WHERE v1 = 'TY' OR v2 = 'TY'
Plan hash value: 2324945452
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 0 |00:00:00.01 | 6 |
| 1 | VIEW | | 1 | 4 | 736 | 8 (0)| 00:00:01 | 0 |00:00:00.01 | 6 |
| 2 | UNION-ALL | | 1 | | | | | 0 |00:00:00.01 | 6 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 2 | 252 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | I_T1_V2 | 1 | 2 | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 2 | 252 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
|* 6 | INDEX RANGE SCAN | I_T2_V1 | 1 | 2 | | 3 (0)| 00:00:01 | 0 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SET$1 / from$_subquery$_001@SEL$1
2 - SET$1
3 - SEL$2 / T1@SEL$2
4 - SEL$2 / T1@SEL$2
5 - SEL$3 / T2@SEL$3
6 - SEL$3 / T2@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("V2"='TY')
6 - access("V1"='TY')