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

2025-04-14 技术教程

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

UNIQUENESSCOMPRESSIONPREFIX_LENGTH TABLESPACE_NAMEINI_TRANS

MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE PCT_THRESHOLD INCLUDE_COLUMNFREELISTS FREELIST_GROUPSPCT_FREE LOGGINBLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUSNUM_ROWS SAMPLE_SIZE LAST_ANALYZEDDEGREE

INSTANCESPARTIT TE GE SE BUFFER_POOLFLASH_CACHECELL_FLASH_CAC USER_S DURATIONPCT_DIRECT_ACCESS

------------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---------------------- ------------------ ---------------- ------------- ------------------------------------------------------------ ---------- ---------- -------------- ----------- ----------- ----------- ------------ ------------- -------------- ---------- --------------- ---------- ------ ---------- ----------- ------------- ----------------------- ----------------------- ----------------- ---------------- ---------- ----------- -------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------ -- -- -- -------------- -------------- -------------- ------ ------------------------------ -----------------

ITYP_OWNERITYP_NAME

------------------------------------------------------------ ------------------------------------------------------------

PARAMETERS

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

GLOBAL DOMIDX_STATUSDOMIDX_OPSTA FUNCIDX_STATUSJOIN_I IOT_RE DROPPE VISIBILITYDOMIDX_MANAGEMENTSEGMEN

------ ------------------------ ------------ ---------------- ------ ------ ------ ------------------ ---------------------------- ------

IDX_T1NORMALTEST

T1TABLE

NONUNIQUEDISABLEDTEST2

25565536104857612147483645

10 YES1211000011

16 VALID1000010000 27-3月 -181

1NONNNDEFAULT

DEFAULTDEFAULTNO

YESNONONOVISIBLE

YES

SQL> select * from t1 where n in (1,2,3);

----------

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

SQL_IDbkmtcvphbgw01, child number 0

-------------------------------------

select * from t1 where n in (1,2,3)

Plan hash value: 2105407043

----------------------------------------------------------------------------

----------------------------------------------------------------------------

|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);

----------

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced')); --强制HINT 失效

PLAN_TABLE_OUTPUT

SQL_ID1fsdbt9t3hdwf, child number 0

-------------------------------------

SELECT /*+USE_CONCAT */ *FROM T1 WHERE N IN(1,2,3)

Plan hash value: 2105407043

----------------------------------------------------------------------------

----------------------------------------------------------------------------

|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);

----------

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT

SQL_IDbkmtcvphbgw01, child number 0

-------------------------------------

select * from t1 where n in (1,2,3)

Plan hash value: 4271029992

----------------------------------------------------------------------------

----------------------------------------------------------------------------

|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行。

声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。

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

热门

最新文章

随机文章

TAGS标签

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

相关推荐

相关推荐

热门

最新文章

随机文章

TAGS标签