实验目的：IN-LIST中"inlist iterator" 与 "concatenation"区别，研究其中原理,进而对sql调优理解。 注意连接词为含索引的列
成都做网站、成都网站建设、成都外贸网站建设,成都做网站公司-创新互联已向成百上千企业提供了,网站设计,网站制作,网络营销等服务!设计与技术结合,多年网站推广经验,合理的价格为您打造企业品质网站。
关键字： /*+USE_CONCAT */
SQL> SET LINESIZE 1000
SQL> SET LONG 9000
SQL> SET LONGCHUNKSIZE 1000
SQL> select * from user_indexes where table_name='T1';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE
UNIQUENESS COMPRESSION PREFIX_LENGTH TABLESPACE_NAME INI_TRANS
MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMN FREELISTS FREELIST_GROUPS PCT_FREE LOGGIN BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS NUM_ROWS SAMPLE_SIZE LAST_ANALYZED DEGREE
INSTANCES PARTIT TE GE SE BUFFER_POOL FLASH_CACHE CELL_FLASH_CAC USER_S DURATION PCT_DIRECT_ACCESS
------------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---------------------- ------------------ ---------------- ------------- ------------------------------------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ---------------- ---------- ----------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------ -- -- -- -------------- -------------- -------------- ------ ------------------------------ -----------------
ITYP_OWNER ITYP_NAME
------------------------------------------------------------ ------------------------------------------------------------
PARAMETERS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GLOBAL DOMIDX_STATUS DOMIDX_OPSTA FUNCIDX_STATUS JOIN_I IOT_RE DROPPE VISIBILITY DOMIDX_MANAGEMENT SEGMEN
------ ------------------------ ------------ ---------------- ------ ------ ------ ------------------ ---------------------------- ------
IDX_T1 NORMAL TEST
T1 TABLE
NONUNIQUE DISABLED TEST 2
255 65536 1048576 1 2147483645
10 YES 1 21 10000 1 1
16 VALID 10000 10000 27-3月 -18 1
1 NO N N N DEFAULT
DEFAULT DEFAULT NO
YES NO NO NO VISIBLE
YES
SQL> select * from t1 where n in (1,2,3);
N
----------
1
2
3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID bkmtcvphbgw01, child number 0
-------------------------------------
select * from t1 where n in (1,2,3)
Plan hash value: 2105407043
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | INLIST ITERATOR | | | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 3 | 12 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(("N"=1 OR "N"=2 OR "N"=3))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
2 - "N"[NUMBER,22]
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
已选择45行。
SQL> SELECT /*+USE_CONCAT */ * FROM T1 WHERE N IN(1,2,3);
N
----------
1
2
3
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced')); --强制 HINT 失效
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1fsdbt9t3hdwf, child number 0
-------------------------------------
SELECT /*+USE_CONCAT */ * FROM T1 WHERE N IN(1,2,3)
Plan hash value: 2105407043
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | INLIST ITERATOR | | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 3 | 12 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(("N"=1 OR "N"=2 OR "N"=3))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
2 - "N"[NUMBER,22]
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
已选择45行。
SQL> exec dbms_stats.gather_table_stats(ownname =>'TEST',tabname =>'T1',cascade => TRUE,method_opt =>'FOR ALL COLUMNS SIZE 1' ,no_invalidate => false ); --使共享游标失效，重新生成SQL计划
PL/SQL 过程已成功完成。
SQL> select * from t1 where n in (1,2,3);
N
----------
3
2
1
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bkmtcvphbgw01, child number 0
-------------------------------------
select * from t1 where n in (1,2,3)
Plan hash value: 4271029992
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | CONCATENATION | | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1_1 / T1@SEL$1
3 - SEL$1_2 / T1@SEL$1_2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 - SEL$1_3 / T1@SEL$1_3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$1_1")
USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
OUTLINE_LEAF(@"SEL$1_2")
OUTLINE_LEAF(@"SEL$1_3")
OUTLINE(@"SEL$1")
INDEX(@"SEL$1_1" "T1"@"SEL$1" ("T1"."N"))
INDEX(@"SEL$1_2" "T1"@"SEL$1_2" ("T1"."N"))
INDEX(@"SEL$1_3" "T1"@"SEL$1_3" ("T1"."N"))
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N"=3)
3 - access("N"=2)
4 - access("N"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - "T1".ROWID[ROWID,10], "N"[NUMBER,22]
2 - "T1".ROWID[ROWID,10], "N"[NUMBER,22]
3 - "T1".ROWID[ROWID,10], "N"[NUMBER,22]
4 - "T1".ROWID[ROWID,10], "N"[NUMBER,22]
已选择60行。