[20160314]關于sql寫法問題使用=還是用in.txt
--昨天看http://www.dbaxiaoyu.com/archives/2618，我第一次看到的感覺應該可以轉化為連接配接查詢,順便做一個例子重複測試:
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@book> create table t as select * from dba_objects ;
Table created.
SCOTT@book> create unique index pk_t on t(object_id);
Index created.
-- create index i_t_DATA_OBJECT_ID on t(DATA_OBJECT_ID);
--分析Method_Opt => 'FOR ALL COLUMNS SIZE 1 '。
2.測試:
SCOTT@book> alter session set statistics_level=all ;
Session altered.
SCOTT@test01p> select * from t where data_object_id = ( select data_object_id from t where object_id=40);
OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NA SHARING E O
------ -------------------- ---------- ---------- -------------- ----------------------- ------------------- ------------------- ------------------- ------- - - - ---------- ---------- ------------- - -
SYS I_OBJ5 40 40 INDEX 2013-06-28 09:03:29 2013-06-28 09:03:29 2013-06-28:09:03:29 VALID N N N 4 NONE Y
Plan hash value: 159606559
----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 431 (100)| | 1 |00:00:00.18 | 1546 | 1539 |
|* 1 | TABLE ACCESS FULL | T | 1 | 11 | 1265 | 429 (1)| 00:00:01 | 1 |00:00:00.18 | 1546 | 1539 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 |
|* 3 | INDEX UNIQUE SCAN | PK_T | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 0 |
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$2 / T@SEL$2
3 - SEL$2 / T@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATA_OBJECT_ID"=)
3 - access("OBJECT_ID"=40)
--如果仔細看執行計劃，可以發現并不存在連接配接，執行計劃先選擇3->2->1.如果換成in:
SCOTT@test01p> select * from t where data_object_id in ( select data_object_id from t where object_id=40);
Plan hash value: 2201121422
| 1 | NESTED LOOPS | | 1 | 1 | 122 | 431 (1)| 00:00:01 | 1 |00:00:00.18 | 1546 | 1539 |
|* 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 |
|* 4 | TABLE ACCESS FULL | T | 1 | 1 | 115 | 429 (1)| 00:00:01 | 1 |00:00:00.18 | 1543 | 1539 |
1 - SEL$5DA710D3
2 - SEL$5DA710D3 / T@SEL$2
3 - SEL$5DA710D3 / T@SEL$2
4 - SEL$5DA710D3 / T@SEL$1
2 - filter("DATA_OBJECT_ID" IS NOT NULL)
4 - filter(("DATA_OBJECT_ID" IS NOT NULL AND "DATA_OBJECT_ID"="DATA_OBJECT_ID"))
--使用nested loop連接配接，作者認為不會出現連接配接查詢，如果看outline部分,可以發現使用in查詢時提示使用unnest,感覺作者使用錯提示.
--改寫如下:
select * from t where data_object_id = ( select /*+ unnest */ data_object_id from t where object_id=40);
| 0 | SELECT STATEMENT | | 1 | | | 431 (100)| | 1 |00:00:00.20 | 1546 | 1539 |
| 1 | NESTED LOOPS | | 1 | 1 | 122 | 431 (1)| 00:00:01 | 1 |00:00:00.20 | 1546 | 1539 |
|* 4 | TABLE ACCESS FULL | T | 1 | 1 | 115 | 429 (1)| 00:00:01 | 1 |00:00:00.20 | 1543 | 1539 |
--很明顯使用unnest提示後,執行計劃也可以改寫成連接配接查詢.甚至能交換次序.
select /*+ leading(t) */ * from t where data_object_id = ( select /*+ unnest */ data_object_id from t where object_id=40);
--執行計劃如下:
Plan hash value: 268617269
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 | | | 431 (100)| | 1 |00:00:00.18 | 1545 | 1539 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 122 | 431 (1)| 00:00:01 | 1 |00:00:00.18 | 1545 | 1539 | 1978K| 1092K| 2425K (0)|
|* 2 | TABLE ACCESS FULL | T | 1 | 8182 | 918K| 429 (1)| 00:00:01 | 8182 |00:00:00.15 | 1542 | 1539 | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 | 0 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_T | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | 0 | | | |
3.使用= 最大的問題是僅僅傳回單行才行,多行才會報錯.
--改成非唯一索引看看.
drop index pk_t;
create index pk_t on t(object_id);
--檢視執行計劃:
Plan hash value: 145684969
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
| 0 | SELECT STATEMENT | | 1 | | | 431 (100)| | 1 |00:00:00.19 | 1546 | 1540 |
|* 1 | TABLE ACCESS FULL | T | 1 | 11 | 1265 | 429 (1)| 00:00:01 | 1 |00:00:00.19 | 1546 | 1540 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.02 | 3 | 1 |
|* 3 | INDEX RANGE SCAN | PK_T | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.02 | 2 | 1 |
--可以看出這個問題關鍵在于查詢内層走的索引必須唯一,傳回單行,這樣的查詢才可以轉換為連接配接查詢.如果不确定傳回的行數,執行計劃
--不會采用連接配接查詢.
4.在使用10053看看:
create unique index pk_t on t(object_id);
SCOTT@test01p> @ 10053on 12
SCOTT@test01p> Select * from t where data_object_id = ( select /*+ unnest */ data_object_id from t where object_id=40);
SCOTT@test01p> @ 10053off
Final query after transformations:******* UNPARSED QUERY IS *******
ELECT /*+ UNNEST */ "T"."OWNER" "OWNER","T"."OBJECT_NAME" "OBJECT_NAME","T"."SUBOBJECT_NAME"
"SUBOBJECT_NAME","T"."OBJECT_ID" "OBJECT_ID","T"."DATA_OBJECT_ID" "DATA_OBJECT_ID","T"."OBJECT_TYPE"
"OBJECT_TYPE","T"."CREATED" "CREATED","T"."LAST_DDL_TIME" "LAST_DDL_TIME","T"."TIMESTAMP" "TIMESTAMP","T"."STATUS"
"STATUS","T"."TEMPORARY" "TEMPORARY","T"."GENERATED" "GENERATED","T"."SECONDARY" "SECONDARY","T"."NAMESPACE"
"NAMESPACE","T"."EDITION_NAME" "EDITION_NAME","T"."SHARING" "SHARING","T"."EDITIONABLE"
"EDITIONABLE","T"."ORACLE_MAINTAINED" "ORACLE_MAINTAINED"
FROM "SCOTT"."T" "T","SCOTT"."T" "T"
WHERE "T"."DATA_OBJECT_ID" = "T"."DATA_OBJECT_ID"
AND "T"."OBJECT_ID" = 40
kkoqbc: optimizing query block SEL$841DDE77 (#1)
--可以發現最終查詢轉化為以上查詢.
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)