Shulou(Shulou.com)06/01 Report--

The purpose of the experiment is to distinguish "inlist iterator" from "concatenation" in IN-LIST, to study the principle of it, and then to understand the tuning of sql. Notice that the conjunction is a column with an index

Keyword: / * + 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-March-18 1

1 NO N N N DEFAULT

DEFAULT DEFAULT NO

YES NO NO NO VISIBLE

YES

SQL > select * from T1 where n in (1pm 2pm 3)

N

-

one

two

three

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 (1meme 2jue 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 rows have been selected.

SQL > SELECT / * + USE_CONCAT * / * FROM T1 WHERE N IN

N

-

one

two

three

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'));-- forcing HINT to fail

PLAN_TABLE_OUTPUT

- - - - -

SQL_ID 1fsdbt9t3hdwf, child number 0

-

SELECT / * + USE_CONCAT * / * FROM T1 WHERE N IN

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 rows have been selected.

SQL > exec dbms_stats.gather_table_stats (ownname = > 'TEST',tabname = >' T1 parallel cascade = > TRUE,method_opt = > 'FOR ALL COLUMNS SIZE 1century, no_invalidate = > false);-- invalidate the shared cursor and regenerate the SQL plan

The PL/SQL process completed successfully.

SQL > select * from T1 where n in (1pm 2pm 3)

N

-

three

two

one

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 (1meme 2jue 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]

Selected 60 rows.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.