本文共 11364 字，大约阅读时间需要 37 分钟。
[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 - 64biSCOTT@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 selectedSCOTT@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 selectedSCOTT@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> @ver1PORT_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 Productioncreate 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')转载地址：http://lzsgx.baihongyu.com/