ITPub博客

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

原创 Oracle 作者:oracle单细胞 时间:2018-08-11 11:29:34 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29741328/viewspace-2199850/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-07-07

  • 博文量
    16
  • 访问量
    10545