in-list扩展 "inlist iterator" "concatenation"

来源：这里教程网时间：2026-03-03 11:52:48 作者：

实验目的：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行。