[20180926]查詢相似索引.txt

[20180926]查詢相似索引.txt

--//有時候在表上建立索引比如A,B欄位,可能又建立B欄位索引,甚至A欄位索引以及B,A欄位索引,或者還建立C,A欄位索引,

--//需要有1個指令碼查詢這些索引,可能還有必要刪除一些索引,統一協調建立合適的索引.

--//最佳化需要,做一個記錄.

1.環境:

SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID

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

IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

2.建立測試例子:

SCOTT@test01p> create table t (a number,b number,c number);

Table created.

SCOTT@test01p> create index i_t_a_b on t(a,b);

Index created.

SCOTT@test01p> create index i_t_c_b on t(c,b);

Index created. 

--//網上找到的例子:

SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_NAME

  FROM ALL_IND_COLUMNS

 WHERE COLUMN_POSITION = 1

   AND TABLE_OWNER     = UPPER ('&&1')

   AND (TABLE_OWNER, TABLE_NAME, COLUMN_NAME) IN (

SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME

 FROM (

SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME, COUNT (*) TCOUNT

 FROM ALL_IND_COLUMNS

WHERE TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')

HAVING COUNT (*)       > 1

 GROUP BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME))

 ORDER BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME, INDEX_NAME;

--//實際上這個例子在我們生產系統根本無法執行,表N多,1個小時都沒有查詢出來.而且像上面建立的索引是無法找到的.

--//因為它僅僅針對COLUMN_POSITION = 1的情況.

--//使用with改寫如下:

/* Formatted on 2018/9/25 22:19:20 (QP5 v5.227.12220.39754) */

WITH t1

     AS (SELECT TABLE_OWNER

               ,TABLE_NAME

               ,INDEX_NAME

               ,COLUMN_NAME

               ,COLUMN_POSITION

           FROM ALL_IND_COLUMNS

          WHERE TABLE_OWNER = UPPER ('&&1'))

    ,t2

     AS (  SELECT DISTINCT TABLE_OWNER

                          ,TABLE_NAME

                          ,INDEX_NAME

                          ,COLUMN_NAME

             FROM t1

            WHERE (TABLE_OWNER, TABLE_NAME, COLUMN_NAME) IN

                     (SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME

                        FROM (  SELECT TABLE_OWNER

                                      ,TABLE_NAME

                                      ,COLUMN_NAME

                                      ,COUNT (*) TCOUNT

                                  FROM T1

                                HAVING COUNT (*) > 1

                              GROUP BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME))

         ORDER BY TABLE_OWNER

                 ,TABLE_NAME

                 ,COLUMN_NAME

                 ,INDEX_NAME)

    ,t3

     AS (  SELECT TABLE_OWNER

                 ,TABLE_NAME

                 ,INDEX_NAME

                 ,LISTAGG (column_name, ', ')

                     WITHIN GROUP (ORDER BY column_position)

                     AS column_group

             FROM t1

         GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME)

SELECT TABLE_OWNER

      ,TABLE_NAME

      ,INDEX_NAME

      ,column_group

  FROM t3

 WHERE (TABLE_OWNER, TABLE_NAME, INDEX_NAME) IN

          (SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM t2);

TABLE_OWNER          TABLE_NAME           INDEX_NAME           COLUMN_GROUP

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

SCOTT                T                    I_T_A_B              A, B

SCOTT                T                    I_T_C_B              C, B

--//補充:在生產系統使用不到1秒就執行完成.

--//換一個引數OE.

Enter value for 1: OE

old   8:           WHERE TABLE_OWNER = UPPER ('&&1'))

new   8:           WHERE TABLE_OWNER = UPPER ('OE'))

TABLE_OWNER          TABLE_NAME           INDEX_NAME           COLUMN_GROUP

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

OE                   INVENTORIES          INVENTORY_IX         WAREHOUSE_ID, PRODUCT_ID

OE                   INVENTORIES          INV_PRODUCT_IX       PRODUCT_ID

OE                   ORDER_ITEMS          ITEM_ORDER_IX        ORDER_ID

OE                   ORDER_ITEMS          ORDER_ITEMS_PK       ORDER_ID, LINE_ITEM_ID

OE                   ORDER_ITEMS          ORDER_ITEMS_UK       ORDER_ID, PRODUCT_ID

OE                   ORDER_ITEMS          ITEM_PRODUCT_IX      PRODUCT_ID

6 rows selected.

--//如果使用網上的指令碼結果如下:

SCOTT@test01p> SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME, COLUMN_NAME

  2    FROM ALL_IND_COLUMNS

  3   WHERE COLUMN_POSITION = 1

  4     AND TABLE_OWNER     = UPPER ('&&1')

  5     AND (TABLE_OWNER, TABLE_NAME, COLUMN_NAME) IN (

  6             SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME

  7               FROM (

  8                             SELECT TABLE_OWNER, TABLE_NAME, COLUMN_NAME, COUNT (*) TCOUNT

  9                               FROM ALL_IND_COLUMNS

 10                              WHERE TABLE_OWNER NOT IN ('SYS','SYSTEM','OUTLN','DBSNMP')

 11                             HAVING COUNT (*)       > 1

 12   GROUP BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME))

 13   ORDER BY TABLE_OWNER, TABLE_NAME, COLUMN_NAME, INDEX_NAME;

old   4:    AND TABLE_OWNER     = UPPER ('&&1')

new   4:    AND TABLE_OWNER     = UPPER ('OE')

TABLE_OWNER          TABLE_NAME           INDEX_NAME           COLUMN_NAME

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

OE                   INVENTORIES          INV_PRODUCT_IX       PRODUCT_ID

OE                   ORDER_ITEMS          ITEM_ORDER_IX        ORDER_ID

OE                   ORDER_ITEMS          ORDER_ITEMS_PK       ORDER_ID

OE                   ORDER_ITEMS          ORDER_ITEMS_UK       ORDER_ID

OE                   ORDER_ITEMS          ITEM_PRODUCT_IX      PRODUCT_ID

--//1.結果不同,存在遺漏.

--//2.明顯感覺執行很慢.

--//3.顯示不直觀.