[20160314]關於sql寫法問題使用=還是用in

lfree發表於2016-03-14
[20160314]關於sql寫法問題使用=還是用in.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@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);
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: 2201121422
----------------------------------------------------------------------------------------------------------------------------------------
| 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 |  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 |
|*  3 |    INDEX UNIQUE SCAN         | PK_T |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |      0 |
|*  4 |   TABLE ACCESS FULL          | T    |      1 |      1 |   115 |   429   (1)| 00:00:01 |      1 |00:00:00.18 |    1543 |   1539 |
----------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / T@SEL$2
   3 - SEL$5DA710D3 / T@SEL$2
   4 - SEL$5DA710D3 / T@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DATA_OBJECT_ID" IS NOT NULL)
   3 - access("OBJECT_ID"=40)
   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);

Plan hash value: 2201121422
----------------------------------------------------------------------------------------------------------------------------------------
| 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.20 |    1546 |   1539 |
|   1 |  NESTED LOOPS                |      |      1 |      1 |   122 |   431   (1)| 00:00:01 |      1 |00:00:00.20 |    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 |
|*  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);

select * from t where data_object_id = ( select /*+ unnest */ data_object_id from t where object_id=40);

--檢視執行計劃:
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 |
------------------------------------------------------------------------------------------------------------------------------------------------
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)

--可以看出這個問題關鍵在於查詢內層走的索引必須唯一,返回單行,這樣的查詢才可以轉換為連線查詢.如果不確定返回的行數,執行計劃
--不會採用連線查詢.

4.在使用10053看看:

drop index pk_t;
create unique index pk_t on t(object_id);

SCOTT@test01p> @ 10053on 12
Session altered.

SCOTT@test01p> Select * from t where data_object_id = ( select /*+ unnest */ 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

SCOTT@test01p> @ 10053off
Session altered.

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)

--可以發現最終查詢轉化為以上查詢.


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

相關文章