[20150727]''與NULL.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20130301]clob欄位的empty_clob與NULL.txtNull
- [20150727]使用標量子查詢小問題.txt
- [20201224]order by欄位順序與查詢條件為NULL.txtNull
- [20131212]12c新特性建表 屬性DEFAULT ON NULL.txtNull
- undefined與null與?. ??UndefinedNull
- &與&&, |與||區別
- 進與穩，時代與技術，新基建與華為雲
- 程式與執行緒、同步與非同步、阻塞與非阻塞、併發與並行執行緒非同步並行
- Promise與async/await與GeneratorPromiseAI
- in與exist , not in與not exist 的區別
- Python學習筆記 5.0 元組 與 字典 與 集合 與 公共操作 與 推導式Python筆記
- 聊聊執行緒與程式 & 阻塞與非阻塞 & 同步與非同步執行緒非同步
- jQuery與JavaScript與ajax三者的區別與聯絡jQueryJavaScript
- forms元件補充與ModelForm簡單使用與cookie與sessionORM元件CookieSession
- PHP 與 Swoole 淺析與學習PHP
- PySpark與GraphFrames的安裝與使用Spark
- PHPCookie與Session的使用與區別PHPCookieSession
- 同步與非同步 阻塞與非阻塞非同步
- define與typedef區別與聯絡
- WAS與IHS整合的安裝與配置
- 漏型與源型、PNP與NPN
- Iterator與Iterable(迭代器與可迭代)
- js == 與 ===JS
- Process與Socket，Select與Accept關係
- cookie與session的區別與聯絡CookieSession
- Java:運用while()與do....while與for（）JavaWhile
- 陣列與字串方法與相互轉換陣列字串
- Session與Cookie的區別與聯絡SessionCookie
- cookie與session的自己思考與解釋CookieSession
- GRPC與 ProtoBuf 的理解與總結RPC
- 同步與阻塞的區別與聯絡
- 關於 in與exist , not in與not exist 的區別
- rpm與yum，at與crontab，sed命令使用
- 詳解not in與not exists的區別與用法
- memcache與memcached的區別與安裝
- 病毒預防與硬碟使用與維護硬碟
- UITableView與WKWebView的巢狀與適配UIWebView巢狀
- hive與hbase的聯絡與區別Hive