[20180808]exists and not exists.txt
[20180808]exists and not exists.txt
--//生產系統遇到的一個效能問題,透過例子來說明:
1.環境:
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t1 as select * from all_objects;
Table created.
SCOTT@test01p> create table t2 as select object_id,'1' flag from t1;
Table created.
SCOTT@test01p> select max(object_id) from t2;
MAX(OBJECT_ID)
--------------
107828
SCOTT@test01p> update t2 set flag='0' where object_id=107828;
1 row updated.
SCOTT@test01p> commit ;
Commit complete.
SCOTT@test01p> create index i_t2_flag on t2(flag);
Index created.
--//分析表,並且t2的flag欄位建立直方圖.
execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't1',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 ',Cascade => True ,No_Invalidate => false);
execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't2',Estimate_Percent => NULL,Method_Opt => 'FOR ALL COLUMNS SIZE 1 for columns flag size 10 ',Cascade => True ,No_Invalidate => false);
`
2.測試:
SCOTT@test01p> alter session set statistics_level=all;
Session altered.
SCOTT@test01p> select object_name from t1 where not exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='1' );
OBJECT_NAME
--------------------
T1
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d4qcxhmwy49r1, child number 0
-------------------------------------
select object_name from t1 where not exists (select 1 from t2 where
t2.object_id=t1.object_id and t2.flag='1' )
Plan hash value: 629543484
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 728 (100)| | 1 |00:00:00.24 | 1667 | 1511 | | | |
|* 1 | HASH JOIN RIGHT ANTI| | 1 | 899 | 33263 | 1672K| 728 (1)| 00:00:01 | 1 |00:00:00.24 | 1667 | 1511 | 5536K| 3056K| 5658K (0)|
|* 2 | TABLE ACCESS FULL | T2 | 1 | 89876 | 614K| | 46 (3)| 00:00:01 | 89876 |00:00:00.02 | 152 | 0 | | | |
| 3 | TABLE ACCESS FULL | T1 | 1 | 89877 | 2633K| | 421 (1)| 00:00:01 | 89877 |00:00:00.11 | 1515 | 1511 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T2@SEL$2
3 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
2 - filter("T2"."FLAG"='1')
--//仔細看id-2.過濾條件是 2 - filter("T2"."FLAG"='1').這樣即使你建立索引在t2.flag也不會使用.因為flag='1'佔大多數.
--//實際上對於當前應用改成如下是等效的.因為flag僅僅兩種取值'0','1'.
SCOTT@test01p> select object_name from t1 where exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='0' );
OBJECT_NAME
--------------------
T1
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1y5xvtwz0u11f, child number 0
-------------------------------------
select object_name from t1 where exists (select 1 from t2 where
t2.object_id=t1.object_id and t2.flag='0' )
Plan hash value: 1273788863
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 423 (100)| | 1 |00:00:00.19 | 1518 | 1512 | | | |
|* 1 | HASH JOIN RIGHT SEMI | | 1 | 1 | 37 | 423 (1)| 00:00:01 | 1 |00:00:00.19 | 1518 | 1512 | 2168K| 2168K| 697K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.04 | 3 | 1 | | | |
|* 3 | INDEX RANGE SCAN | I_T2_FLAG | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.04 | 2 | 1 | | | |
| 4 | TABLE ACCESS FULL | T1 | 1 | 89877 | 2633K| 421 (1)| 00:00:01 | 89877 |00:00:00.12 | 1515 | 1511 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T2@SEL$2
3 - SEL$5DA710D3 / T2@SEL$2
4 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
3 - access("T2"."FLAG"='0')
--//實際上到具體應用object_id欄位是主鍵,如果在上面建立索引,邏輯讀更小.
CREATE UNIQUE INDEX SCOTT.pk_t1 ON SCOTT.T1 (OBJECT_ID);
ALTER TABLE SCOTT.T1 ADD CONSTRAINT pk_t1 PRIMARY KEY (OBJECT_ID);
CREATE UNIQUE INDEX SCOTT.pk_t2 ON SCOTT.T2 (OBJECT_ID);
ALTER TABLE SCOTT.T2 ADD CONSTRAINT pk_t2 PRIMARY KEY (OBJECT_ID);
SCOTT@test01p> select object_name from t1 where exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='0' );
OBJECT_NAME
--------------------
T1
SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1y5xvtwz0u11f, child number 0
-------------------------------------
select object_name from t1 where exists (select 1 from t2 where
t2.object_id=t1.object_id and t2.flag='0' )
Plan hash value: 4193600567
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 3 (100)| | 1 |00:00:00.04 | 6 | 2 | | | |
| 1 | NESTED LOOPS | | 1 | | | | | 1 |00:00:00.04 | 6 | 2 | | | |
| 2 | NESTED LOOPS | | 1 | 1 | 37 | 3 (0)| 00:00:01 | 1 |00:00:00.03 | 5 | 1 | | | |
| 3 | SORT UNIQUE | | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T2 | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 | | | |
|* 5 | INDEX RANGE SCAN | I_T2_FLAG | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 0 | | | |
|* 6 | INDEX UNIQUE SCAN | PK_T1 | 1 | 1 | | 0 (0)| | 1 |00:00:00.03 | 2 | 1 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 30 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 | 1 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
4 - SEL$5DA710D3 / T2@SEL$2
5 - SEL$5DA710D3 / T2@SEL$2
6 - SEL$5DA710D3 / T1@SEL$1
7 - SEL$5DA710D3 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T2"."FLAG"='0')
6 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
Note
-----
- this is an adaptive plan
--//而select object_name from t1 where not exists (select 1 from t2 where t2.object_id=t1.object_id and t2.flag='1' );執行計劃不變.不再貼出.
--//我有時候想開發寫sql程式碼過腦子沒有,有時候真的很無語很無奈...
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2199463/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- EXISTS、IN、NOT EXISTS、NOT IN(zt)
- 永久有效的 webstorm license server 20180808WebORMServer
- [20180808]Null value to Dynamic SQL.txtNullSQL
- exists()、not exists() 、in()、not in()用法以及效率差異
- sql：delete if exists還是drop if exists?SQLdelete
- In和exists使用及效能分析(二)：exists的使用
- in、exists與索引索引
- Oralce 使用SQL中的exists 和not exists 用法詳解SQL
- In和exists使用及效能分析(三)：in和exists的效能分析
- elasticsearch之exists查詢Elasticsearch
- [精選] SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析SQL
- not in 和 not exists 比較和用法
- mysql 關於exists 和in分析MySql
- [20180928]exists與cardinality.txt
- 對線面試官：SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析面試SQL
- 【原始碼】Redis exists命令bug分析原始碼Redis
- beego報錯 table name: `xxx` not existsGo
- exists與in子查詢優化優化
- mybatis exists 中使用代替in關鍵字MyBatis
- Oracle中exists和in的效能差異Oracle
- Python BUG FileExistsError: [Errno 17] File exists: xxxPythonError
- SQL語句中exists和in的區別SQL
- Elasticsearch Java High Level REST Client（Exists API）ElasticsearchJavaRESTclientAPI
- 關於hibernate的 No row with the given identifier existsIDE
- 【MySQL】NOT EXISTS優化的一個案例MySql優化
- PostgreSQL DBA(107) - pgAdmin(Don't do this：NOT IN vs NOT EXISTS)SQL
- PTSQLServer中exists和except用法介紹wkaSQLServer
- 蘊含式（包含EXISTS語句的分析）
- in、exists操作與null的一點總結Null
- In和exists使用及效能分析(一)：in的使用
- Swap file "/etc/sysconfig/.iptables.swp" already exists!
- ou have not concluded your merge (MERGE_HEAD exists)
- MYSQL 中 exists 語句執行效率變低MySql
- 不要再問我 in，exists 走不走索引了索引
- sql中in和exists的原理及使用場景。SQL
- git使用報錯fatal: remote origin already exists.GitREM
- MySQL防止重複插入相同記錄 insert if not existsMySql
- 《mModel does not exists !? 》Laravel-admin 控制器建立出錯Laravel