[20150727]''與NULL.txt

lfree發表於2015-07-27

[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')

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1753237/,如需轉載,請註明出處,否則將追究法律責任。

相關文章