ITPub博客

首页 > 数据库 > Oracle > [20180926]查询相似索引.txt

[20180926]查询相似索引.txt

原创 Oracle 作者:lfree 时间:2018-09-26 09:03:43 0 删除 编辑

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


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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2859
  • 访问量
    6645832