[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);
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)
--可以发现最终查询转化为以上查询.