[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.显示不直观.