in-list扩展 "inlist iterator" "concatenation"-云搜网

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

———-

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

—————————————————————————-

—————————————————————————-

| 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_ID 1fsdbt9t3hdwf, 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

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_ID bkmtcvphbgw01, 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"

相关推荐

热门推荐

分类目录