检查重复索引,ORACLE中关于外键缺少索引的探讨和总结

--Use the below T-SQL script to generate the complete list of duplicate XML indexes in a given database:

 

;WITH    XMLTable

          AS ( SELECT   OBJECT_NAME(x.object_id) AS TableName ,

                        SCHEMA_NAME(o.schema_id) AS SchemaName ,

                        x.object_id ,

                        x.name ,

                        x.index_id ,

                        x.using_xml_index_id ,

                        x.secondary_type ,

                        CONVERT(NVARCHAR(MAX), x.secondary_type_desc) AS secondary_type_desc ,

                        ic.column_id

               FROM     sys.xml_indexes x ( NOLOCK )

                        JOIN sys.objects o ( NOLOCK ) ON x.object_id = o.object_id

                        JOIN sys.index_columns (NOLOCK) ic ON x.object_id = ic.object_id

                                                              AND x.index_id = ic.index_id

             ),

        DuplicatesXMLTable

          AS ( SELECT   x1.SchemaName ,

                        x1.TableName ,

                        x1.name AS IndexName ,

                        x2.name AS DuplicateIndexName ,

                        x1.secondary_type_desc AS IndexType ,

                        x1.index_id ,

                        x1.object_id ,

                        ROW_NUMBER() OVER ( ORDER BY x1.SchemaName, x1.TableName, x1.name, x2.name ) AS seq1 ,

                        ROW_NUMBER() OVER ( ORDER BY x1.SchemaName DESC, x1.TableName DESC, x1.name DESC, x2.name DESC ) AS seq2 ,

                        NULL AS inc

               FROM     XMLTable x1

                        JOIN XMLTable x2 ON x1.object_id = x2.object_id

                                            AND x1.index_id < x2.index_id

                                            AND x1.using_xml_index_id = x2.using_xml_index_id

                                            AND x1.secondary_type = x2.secondary_type

             )

    SELECT  SchemaName ,

            TableName ,

            IndexName ,

            DuplicateIndexName ,

            IndexType  ,

            Index_ID ,

            [Object_ID] ,

            1 AS IsXML

    FROM    DuplicatesXMLTable dtxml

    ORDER BY 1 ,

             2 ,

             3;
--查看某个具体的表是否和其它表拥有主外键关系  

SELECT DC.OWNER           AS "PARENT_TABLE_OWNER", 

       DC.TABLE_NAME      AS "PARENT_TABLE_NAME", 

       DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME", 

       DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME", 

       DF.OWNER           AS "CHILD_TABLE_OWNER", 

       DF.TABLE_NAME      AS "CHILD_TABLE_NAME" 

FROM   DBA_CONSTRAINTS DC, 

       (SELECT C.OWNER, 

               C.CONSTRAINT_NAME, 

               C.R_CONSTRAINT_NAME, 

               C.TABLE_NAME 

        FROM   DBA_CONSTRAINTS C 

        WHERE  CONSTRAINT_TYPE = 'R') DF 

WHERE  DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME 

       AND DC.OWNER =UPPER('&OWNER')

       AND DC.TABLE_NAME=UPPER('&TABLE_NAME');

我们以AdventureWorks二〇一四数据库为例,如下所示,表[Person]检查重复索引,ORACLE中关于外键缺少索引的探讨和总结。.[Address]下有4个目录,如下所示

--脚本使用深入分析函数LISTAGG, 适用于ORACLE 11g以至上述版本,要是数据库版本是Oracle 11g及以上,就足以选拔此脚本代替上边脚本。

 

如上所示,当外键字段没有索引时,父表与子表关联时,子表会进展全表扫描,上边,作者在外键字段创立索引后,就会幸免子表表扫描了。

 

参照他事他说加以考查资料:

 

CREATE INDEX IX_Address_N1 ON [Person].[Address](StateProvinceID);

地锁定愈来愈多的行,而影响并发性

CREATE INDEX IX_Address_N2 ON [Person].[Address](StateProvinceID) INCLUDE (City);

 

 

咱俩先来拜访二个轻便的例证,看看当外键缺点和失误索引时,子表是还是不是进行全表扫描,如下所示,表EMP与DEPT存在主外键关系:

 

SQL> set linesize 1200

SQL> set autotrace traceonly

SQL> select p.id , p.name,c.child_name

  2  from test.parent_tb_test p

  3  inner join test.child_tb_test c on p.id = c.f_id 

  4  where p.id=1000;

 

1000 rows selected.

 

 

Execution Plan

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

Plan hash value: 901213199

 

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

| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |                   |  1009 | 44396 |  4706  (21)| 00:00:07 |

|   1 |  NESTED LOOPS                |                   |  1009 | 44396 |  4706  (21)| 00:00:07 |

|   2 |   TABLE ACCESS BY INDEX ROWID| PARENT_TB_TEST    |     1 |    31 |     1   (0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN         | PK_PARENT_TB_TEST |     1 |       |     1   (0)| 00:00:01 |

|*  4 |   TABLE ACCESS FULL          | CHILD_TB_TEST     |  1009 | 13117 |  4705  (21)| 00:00:07 |

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

Predicate Information (identified by operation id):

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

   3 - access("P"."ID"=1000)

   4 - filter("C"."F_ID"=1000)

 

 

Statistics

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

          0  recursive calls

          0  db block gets

      32855  consistent gets

      32772  physical reads

          0  redo size

      29668  bytes sent via SQL*Net to client

       1218  bytes received via SQL*Net from client

         68  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       1000  rows processed

 

SQL> 

图片 1

接下去,我们再来看看外键缺点和失误索引影响并发,以至变成死锁的情事,如下所示,创设表dead_lock_parent与dead_lock_foreign,两者存在主外键关系,分布插入两条测量试验数据:

图片 2

 

 

 

 

SQL> delete from dead_lock_parent where id=2;

前言感想:不常起来,陡然想写贰个关于MS SQL的巡检类别方面包车型客车随笔,因为自身以为那上边的知识分享是有价值,也是老大有意义的。一方面,比非常多种经营验不足的人,对于巡检有一点点茫然不解,不知情要从哪些方面巡检,其余一面,英特网有关MS SQL巡检察院方面面包车型客车材质好像亦不是专程多。写这么些种类只是三个分享,本身的初志是贰个知识梳理、计算提炼进度,某个知识婺剧本亦非原创,小说非常多地点也是融合了友好的生机勃勃对设法和见解的,不足和浮泛之处鲜明也不行多,一得之见,也冀望我们提意见和提议、补充,指正此中的美中不足。Stay Hungry Stay Foolish!

--查看有些具体的表是不是和任何表具备主外键关系

 

SQL> SELECT S.SID                             SID, 

         S.USERNAME                        USERNAME, 

         S.MACHINE                         MACHINE, 

         L.TYPE                            TYPE, 

         O.OBJECT_NAME                     OBJECT_NAME, 

         DECODE(L.LMODE, 0, 'None', 

                         1, 'Null', 

                         2, 'Row Share', 

                         3, 'Row Exlusive', 

                         4, 'Share', 

                         5, 'Sh/Row Exlusive', 

                         6, 'Exclusive')   lmode, 

    DECODE(L.REQUEST, 0, 'None', 

                           1, 'Null', 

                           2, 'Row Share', 

                           3, 'Row Exlusive', 

                           4, 'Share', 

                           5, 'Sh/Row Exlusive', 

                           6, 'Exclusive') request, 

         L.BLOCK                           BLOCK 

  FROM   V$LOCK L, 

         V$SESSION S, 

         DBA_OBJECTS O 

  WHERE  L.SID = S.SID 

         AND USERNAME != 'SYSTEM' 

         AND O.OBJECT_ID(+) = L.ID1 

         AND S.SID IN ( 766,789) 

  ORDER  BY S.SID; 

 

       SID USERNAME MACHINE        TY OBJECT_NAME          LMODE           REQUEST         BLOCK

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

       766 TEST     XXXXGET253194 TX                      Exclusive       None                0

       766 TEST     XXXXGET253194 TM DEAD_LOCK_FOREIGN    Row Exlusive    None                1

       766 TEST     XXXXGET253194 TM DEAD_LOCK_PARENT     Row Exlusive    None                0

       789 TEST     DB-Server.loca TX                      Exclusive       None                0

                    ldomain

 

       789 TEST     DB-Server.loca TM DEAD_LOCK_PARENT     Row Exlusive    None                0

                    ldomain

 

       789 TEST     DB-Server.loca TM DEAD_LOCK_FOREIGN    Row Exlusive    Sh/Row Exlusive     0

                    ldomain    

 

 

小心,关于重复索引(Duplicate Indexes)表示存在的目录除了名字不一致等外, 索引所在字段以致索引字段顺序都是如出意气风发辙的。An index is considered to be a duplicate if it references the same column and ordinal position as another index in the same database。 这一个脚本是搜索大同小异的目录,倘令你创设下边索引,索引字段雷同,可是有隐含列字段不平等,那么这么些脚本会将那么些目录视为不均等的目录。有意思味能够和睦试试。

3:接着在对话1(会话ID为789)里进行删除dead_lock_parent中id为1的记录:

那么我们实践上边脚本就能够找到这一个重复的目录,如下所示

 

其它关于XML索引的再度索引,能够接收上面脚本检查。

SELECT 'CREATE INDEX ' 

              || OWNER 

              || '.' 

              || REPLACE(CONSTRAINT_NAME,'FK_','IX_') 

              || ' ON ' 

              || OWNER 

              || '.' 

              || TABLE_NAME 

              || ' (' 

              || FK_COLUMNS 

              ||') TABLESPACE ' 

              || 

       ( 

              SELECT TABLESPACE_NAME 

              FROM   DBA_TABLES 

              WHERE  OWNER= CON.OWNER 

              AND    TABLE_NAME= CON.TABLE_NAME) CREATE_INDEXES_ON_FOREIGN_KEY 

FROM   ( 

                SELECT   CC.OWNER, 

                         CC.TABLE_NAME, 

                         CC.CONSTRAINT_NAME, 

                         LISTAGG(CC.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY CC.POSITION) FK_COLUMNS

                FROM     DBA_CONS_COLUMNS CC, 

                         DBA_CONSTRAINTS DC 

                WHERE    CC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME 

                AND      DC.CONSTRAINT_TYPE = 'R' 

                AND      CC.OWNER = DC.OWNER 

                AND      DC.OWNER NOT IN ( 'SYS', 

                                          'SYSTEM', 

                                          'OLAPSYS', 

                                          'SYSMAN', 

                                          'MDSYS', 

                                          'ADMIN' ) 

                GROUP BY CC.OWNER, 

                         CC.TABLE_NAME, 

                         CC.CONSTRAINT_NAME) CON 

  WHERE NOT EXISTS 

       ( 

              SELECT 1 

              FROM   ( 

                              SELECT   TABLE_OWNER, 

                                       TABLE_NAME, 

                                       INDEX_NAME,

                                       LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_POSITION) FK_COLUMNS

                              FROM     DBA_IND_COLUMNS 

                              WHERE    INDEX_OWNER NOT IN ( 'SYS', 

                                                           'SYSTEM', 

                                                           'OLAPSYS', 

                                                           'SYSMAN', 

                                                           'MDSYS', 

                                                           'ADMIN' ) 

                                                         

                              GROUP BY TABLE_OWNER, 

                                       TABLE_NAME ,INDEX_NAME) COL 

              WHERE  CON.OWNER = COL.TABLE_OWNER 

              AND    CON.TABLE_NAME = COL.TABLE_NAME 

              AND    CON.FK_COLUMNS = SUBSTR(COL.FK_COLUMNS, 1, LENGTH(CON.FK_COLUMNS)) ) 

              ORDER BY 1;

 

作者们第风姿罗曼蒂克能够由此下面脚本,找到任何数据库中那几个表有主外键关系,并列出主外键节制.

 

 

在各样库跑三次那些剧本,就能够将装有的重复的目录(Duplicate Indexes)全体找寻,不过当手头服务器、数据库比比较多时,这么些专门的工作也是多个体力活,能够将以此常规职业自动化,防止重复劳动,作者将这几个集成在MyDBA工具里面,只必要点击一下鼠标,就足以扶植本身自动管理那么些专门的职业。

 

若是有些二愣子在此个表的字段StateProvinceID上创制了上面重复索引,IX_Address_N1 与IX_Address_StateProvinceID是多个再度索引。

SQL> create index ix_child_tb_test on child_tb_test(f_id);

 

SQL> set linesize 1200

SQL> set autotrace traceonly

SQL> select p.id , p.name,c.child_name

  2  from test.parent_tb_test p

  3  inner join test.child_tb_test c on p.id = c.f_id 

  4  where p.id=1000;
;WITH    IndexColumns

          AS ( SELECT DISTINCT

                      SCHEMA_NAME(o.schema_id)     AS SchemaName    ,

                      OBJECT_NAME(o.object_id)     AS TableName     ,

                      i.name                       AS IndexName     ,

                      o.object_id                  AS [Object_ID]   ,

                      i.index_id                   AS Index_ID      ,

                      i.type_desc                 AS IndexType      ,

                      ( SELECT    CASE key_ordinal

                                    WHEN 0 THEN NULL

                                    ELSE '[' + COL_NAME(k.object_id,

                                                        column_id) + '] '

                                         + CASE WHEN is_descending_key = 1

                                                THEN 'Desc'

                                                ELSE 'Asc'

                                           END

                                  END AS [data()]

                        FROM      sys.index_columns  k WITH(NOLOCK)

                        WHERE     k.object_id = i.object_id

                                  AND k.index_id = i.index_id

                        ORDER BY  key_ordinal ,

                                  column_id

                      FOR

                        XML PATH('')

                      ) AS IndexColumns ,

                        CASE WHEN i.index_id = 1

                             THEN ( SELECT  '[' + name + ']' AS [data()]

                                    FROM    sys.columns (NOLOCK) AS c

                                    WHERE   c.object_id = i.object_id

                                            AND c.column_id NOT IN (

                                            SELECT  column_id

                                            FROM    sys.index_columns (NOLOCK)

                                                    AS kk

                                            WHERE   kk.object_id = i.object_id

                                                    AND kk.index_id = i.index_id )

                                    ORDER BY column_id

                                  FOR

                                    XML PATH('')

                                  )

                             ELSE ( SELECT  '[' + COL_NAME(k.object_id,

                                                           column_id) + ']' AS [data()]

                                    FROM    sys.index_columns k WITH(NOLOCK) 

                                    WHERE   k.object_id = i.object_id

                                            AND k.index_id = i.index_id

                                            AND is_included_column = 1

                                            AND k.column_id NOT IN (

                                            SELECT  column_id

                                            FROM    sys.index_columns kk

                                            WHERE   k.object_id = kk.object_id

                                                    AND kk.index_id = 1 )

                                    ORDER BY key_ordinal ,

                                            column_id

                                  FOR

                                    XML PATH('')

                                  )

                        END AS IndexInclude

               FROM     sys.indexes  i WITH(NOLOCK) 

                        INNER JOIN sys.objects o WITH(NOLOCK) ON i.object_id = o.object_id

                        INNER JOIN sys.index_columns ic  WITH(NOLOCK ) ON ic.object_id = i.object_id

                                                              AND ic.index_id = i.index_id

                        INNER JOIN sys.columns c WITH(NOLOCK) ON c.object_id = ic.object_id

                                                              AND c.column_id = ic.column_id

               WHERE    o.type = 'U'

                        AND i.index_id <> 0  -- 0 = 堆

                        AND i.type <> 3         -- 3 = XML  

                        AND i.type <> 5         -- 5 = 聚集列存储索引(SQL 2014~ SQL 2016)

                        AND i.type <> 6         -- 6 = 非聚集列存储索引(SQL 2014~ SQL 2016)

                        AND i.type <> 7         -- 7 = 非聚集哈希索引(SQL 2014~ SQL 2016)

               GROUP BY o.schema_id ,

                        o.object_id ,

                        i.object_id ,

                        i.name ,

                        i.index_id ,

                        i.type_desc

             ),

        DuplicatesTable

          AS ( SELECT   ic1.SchemaName    ,

                        ic1.TableName     ,

                        ic1.IndexName     ,

                        ic1.[Object_ID]   ,

                        ic2.IndexName AS DuplicateIndexName ,

                        ic1.IndexType   ,

                        CASE WHEN ic1.index_id = 1

                             THEN ic1.IndexColumns + ' (Clustered)'

                             WHEN ic1.IndexInclude = '' THEN ic1.IndexColumns

                             WHEN ic1.IndexInclude IS NULL THEN ic1.IndexColumns

                             ELSE ic1.IndexColumns + ' INCLUDE ' + ic1.IndexInclude

                        END AS IndexCols ,

                        ic1.index_id

               FROM     IndexColumns ic1

                        JOIN IndexColumns ic2 ON ic1.object_id = ic2.object_id

                                                 AND ic1.index_id < ic2.index_id

                                                 AND ic1.IndexColumns = ic2.IndexColumns

                                                 AND ( ISNULL(ic1.IndexInclude, '') = ISNULL(ic2.IndexInclude,

                                                              '')

                                                       OR ic1.index_id = 1

                                                     )

             )

    SELECT  SchemaName ,

            TableName ,

            IndexName ,

            DuplicateIndexName ,

            IndexType,

            IndexCols ,

            Index_ID ,

          Object_ID ,

          0 AS IsXML

    FROM    DuplicatesTable dt

    ORDER BY 1 , 2 ,3

 

在SQL Server数据库中,有相当大可能率存在重复的目录(Duplicate Indexes),那些不止影响属性(INSERT、UPDATE、DELETE时带给十三分的IO成本,当数据库维护,索引重新组合时也会带动万分的付出),何况占用空间。数据库存在重新索引(Duplicate Indexes)的自始至终的经过是多地点的,非常多时候、相当多事情不是你所能完全掌握控制的,除非您所管理的数据库极度标准,权限决定、脚本公布非常严格、流程化。一时不说那一个,那么怎么在数据库巡检进程找寻这个再一次的目录(Duplicate Indexes)呢? 上边分享三个自己在Premier Proactive Services中发觉贰个的剧本(做了有个别改过和调动)。

上边脚本布局了测量检验用的例证和多少, 那么大家相比看看外键有无索引的区分:

--查看整个数据库下拥有主外键关系的所有表(排除一些系统用户)   

SELECT DC.OWNER                   AS "PARENT_TABLE_OWNER", 

       DC.TABLE_NAME              AS "PARENT_TABLE_NAME", 

       DC.CONSTRAINT_NAME         AS "PRIMARY CONSTRAINT NAME", 

       DF.CONSTRAINT_NAME         AS "REFERENCED CONSTRAINT NAME", 

       DF.OWNER                   AS "CHILD_TABLE_OWNER", 

       DF.TABLE_NAME              AS "CHILD_TABLE_NAME" 

FROM   DBA_CONSTRAINTS DC, 

       (SELECT C.OWNER, 

               C.CONSTRAINT_NAME, 

               C.R_CONSTRAINT_NAME, 

               C.TABLE_NAME 

        FROM   DBA_CONSTRAINTS C 

        WHERE  CONSTRAINT_TYPE = 'R') DF 

WHERE  DC.CONSTRAINT_NAME =DF.R_CONSTRAINT_NAME 

       AND DC.OWNER NOT IN ( 'SYSTEM', 'SYS', 'DBSNMP', 'EXFSYS', 

                            'ORDDATA', 'CTXSYS', 'OLAPSYS', 'MDSYS', 

                            'SYSMAN' ); 

 

 

 

 

SQL> create table dead_lock_parent( id number primary key, name varchar2(32));

 

Table created.

 

SQL> create table dead_lock_foreign(fid  number, fname varchar2(32), foreign key(fid) references dead_lock_parent);

 

Table created.

 

SQL> insert into dead_lock_parent values( 1, 'kerry');

 

1 row created.

 

SQL> insert into dead_lock_foreign values(1, 'kerry_fk');  

 

1 row created.

 

SQL> insert into dead_lock_parent values(2, 'jimmy');

 

1 row created.

 

SQL> insert into dead_lock_foreign values(2, 'jimmy_fk');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> 

创立索引后,我们再来看看其施行安插,注意相比较创设索引前后,实践布置的出入,如下所示:

接下去大家要寻觅在具体的外键字段是不是有目录,脚本如下所示:

 

 

 

/*******************************************************************************************

--脚本功能描述:

--  对于数据库中外键缺少索引的字段,生成对应的索引(排除一些系统账号,例如sys、system),如果外键索引超过十个字段

--  那么这个脚本就不能正确的生成对应的索引,当然也很少有外键设置在超过10个字段的。另外索引表空

--  空间跟数据表空间相同,如有分开的话,建议在此处再做调整。

********************************************************************************************/

SELECT    'CREATE INDEX ' || OWNER || '.' || REPLACE(CONSTRAINT_NAME,'FK_','IX_') || 

        ' ON ' || OWNER || '.' || TABLE_NAME || ' (' || COL_LIST ||') TABLESPACE ' 

        || (SELECT TABLESPACE_NAME FROM DBA_TABLES WHERE OWNER= CON.OWNER AND TABLE_NAME= CON.TABLE_NAME) 

        AS  CREATE_INDEXES_ON_FOREIGN_KEY 

FROM

     (SELECT CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME,

           MAX(DECODE(POSITION, 1,     '"' ||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 2,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 3,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 4,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 5,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 6,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 7,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 8,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 9,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 10,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST

           FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CC

           WHERE DC.OWNER = CC.OWNER

           AND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

           AND DC.CONSTRAINT_TYPE = 'R'

           AND DC.OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS', 'ADMIN')

           GROUP BY CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME 

     ) CON

      WHERE NOT EXISTS (

        SELECT 1 FROM

                  ( SELECT TABLE_OWNER, TABLE_NAME,   

                       MAX(DECODE(COLUMN_POSITION, 1,     '"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 2,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 3,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 4,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 5,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 6,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 7,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 8,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 9,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 10,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) COL_LIST

                       FROM DBA_IND_COLUMNS 

                   WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS')

                   GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COL

    WHERE CON.OWNER = COL.TABLE_OWNER 

    AND CON.TABLE_NAME = COL.TABLE_NAME  

    AND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST) ) )  ; 

找寻未索引的外键

 

理当如此那多个表的数据量实乃太少了,质量上差距非常小,当数据量增进上去后,这些个性差别就能相比较分明了。如下例子所示,大家组织四个数据量相对很大的父表与子表:

 

SELECT CASE 

         WHEN B.TABLE_NAME IS NULL THEN 'NO INDEXED' 

         ELSE 'INDEXED' 

       END               AS STATUS, 

       A.TABLE_OWNER     AS TABLE_OWNER, 

       A.TABLE_NAME      AS TABLE_NAME, 

       A.CONSTRAINT_NAME AS FK_NAME, 

       A.FK_COLUMNS      AS FK_COLUMNS, 

       B.INDEX_NAME      AS INDEX_NAME, 

       B.INDEX_COLUMNS   AS INDEX_COLUMNS 

FROM   (SELECT A.OWNER                              AS TABLE_OWNER, 

               A.TABLE_NAME                         AS TABLE_NAME, 

               A.CONSTRAINT_NAME                    AS CONSTRAINT_NAME, 

               LISTAGG(A.COLUMN_NAME, ',') 

                 WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS 

        FROM   DBA_CONS_COLUMNS A, 

               DBA_CONSTRAINTS B 

        WHERE  A.CONSTRAINT_NAME = B.CONSTRAINT_NAME 

               AND B.CONSTRAINT_TYPE = 'R' 

               AND A.OWNER = B.OWNER 

               AND A.OWNER NOT IN ( 'SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 

                                    'MDSYS' ) 

        GROUP  BY A.OWNER, 

                  A.TABLE_NAME, 

                  A.CONSTRAINT_NAME) A, 

       (SELECT TABLE_OWNER, 

               TABLE_NAME, 

               INDEX_NAME, 

               LISTAGG(C.COLUMN_NAME, ',') 

                 WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS 

        FROM   DBA_IND_COLUMNS C 

        GROUP  BY TABLE_OWNER, 

                  TABLE_NAME, 

                  INDEX_NAME) B 

WHERE  A.TABLE_NAME = B.TABLE_NAME(+) 

       AND A.TABLE_OWNER = B.TABLE_OWNER(+) 

       AND B.INDEX_COLUMNS(+) LIKE A.FK_COLUMNS 

                                   || '%' 

ORDER  BY 1 DESC

 

 

地方音信风度翩翩旦无法令你明白,那么能够看看上面脚本,相信您能看得更详实。

 

  1. 潜移暗化属性。 假若子表外键没有开创索引,那么当父表查询关联子表时,子表将拓宽全表扫描。影响表连接方式。

  2. 潜濡默化并发。 无论是更新父表主键,恐怕去除多个父记录,都会在子表中加三个表锁(在此条语句完毕前,不一样意对子表做任何更改)。那就能够不必要

3: 不会从父表联结到子表, 也许更通俗的讲,外键列不扶持子表的叁人命关天访谈路线,何况你在谓词中并未有动用这一个外键累从子表中精选数据。

SELECT   CON.OWNER ,

         CON.TABLE_NAME,

         CON.CONSTRAINT_NAME,

         CON.COL_LIST,

         'No Indexed' AS INDEX_STATUS

FROM

     (SELECT CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME,

           MAX(DECODE(POSITION, 1,     '"' ||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 2,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 3,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 4,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 5,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 6,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 7,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 8,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 9,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) ||

           MAX(DECODE(POSITION, 10,', '||'"'||

                  SUBSTR(COLUMN_NAME,1,30) ||'"',NULL)) COL_LIST

           FROM DBA_CONSTRAINTS DC, DBA_CONS_COLUMNS CC

           WHERE DC.OWNER = CC.OWNER

           AND DC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME

           AND DC.CONSTRAINT_TYPE = 'R'

           AND DC.OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS', 'ADMIN')

           GROUP BY CC.OWNER, CC.TABLE_NAME, CC.CONSTRAINT_NAME 

     ) CON

      WHERE NOT EXISTS (

        SELECT 1 FROM

                  ( SELECT TABLE_OWNER, TABLE_NAME,   

                       MAX(DECODE(COLUMN_POSITION, 1,     '"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 2,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 3,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 4,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 5,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 6,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 7,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 8,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 9,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) ||

                       MAX(DECODE(COLUMN_POSITION, 10,', '||'"'||

                              SUBSTR(COLUMN_NAME,1,30)  ||'"',NULL)) COL_LIST

                       FROM DBA_IND_COLUMNS 

                   WHERE TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'OLAPSYS', 'SYSMAN', 'MDSYS')

                   GROUP BY TABLE_OWNER, TABLE_NAME, INDEX_NAME ) COL

    WHERE CON.OWNER = COL.TABLE_OWNER 

    AND CON.TABLE_NAME = COL.TABLE_NAME  

    AND CON.COL_LIST = SUBSTR(COL.COL_LIST, 1, LENGTH(CON.COL_LIST) ) )  ;  

2:在会话2(会话ID为766)里面奉行上面SQL语句:

比方你在外键字段上开创索引,那么这种景色下的操作就不会自不过然死锁。在此就不再赘述。有意思味能够测量试验一下.

1:在对话1(会话ID为789)里面实行下边SQL语句:

 

图片 3

随着在会话2里面推行下边SQL,删除主表中id=2的笔录

 

 

2: 无论是蓄意还是无心,不问可见不会更新父表的独步天下/主键字段值。

外键缺点和失误索引影响

CREATE INDEX SCOTT.IX_DEPTNO ON SCOTT.EMP ("DEPTNO") TABLESPACE USERS;

 

SQL> show user;

USER 为 "TEST"

SQL> select * from v$mystat where rownum=1;

 

       SID STATISTIC#      VALUE

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

       789          0          1

 

SQL> delete from dead_lock_foreign where fid=1;

 

已删除 1 行。

 

 

外键列上缺少索引会带给五个难点,限定并发性、影响属性、还应该有极大可能率招致死锁。所以对于绝大多数气象,大家应当尽大概思量在外键上边创立索引

此刻您会发觉会话被封堵了,大家能够用上边SQL查询具体的封堵音讯。

图片 4

自动生成创立外键索引的台本

您会发觉会话1就能并发Deadlock

 

 

SQL> set autotrace on;

SQL> 

SQL> SELECT  D.DEPTNO, COUNT(*)

  2  FROM SCOTT.EMP E INNER JOIN SCOTT.DEPT D ON E.DEPTNO =D.DEPTNO

  3  GROUP BY D.DEPTNO;

 

    DEPTNO   COUNT(*)

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

        30          6

        20          5

        10          3

 

 

Execution Plan

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

Plan hash value: 4067220884

 

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     3 |     9 |     4  (25)| 00:00:01 |

|   1 |  HASH GROUP BY     |      |     3 |     9 |     4  (25)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - filter("E"."DEPTNO" IS NOT NULL)

 

 

Statistics

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

          1  recursive calls

          0  db block gets

          7  consistent gets

          0  physical reads

          0  redo size

        665  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          3  rows processed

图片 5

虽说扩展索引,或然会推动一些附加的属性费用(DML操作费用增添)和磁盘空间方面包车型大巴开支,可是相比其带给的性质匡正来讲,这几个额外的支出其实完全能够忽视。若无其他特别处境,提议持有的外键字段都加上索引。在Oracle Oracle Database 9i/10g/11g编程艺术那本书中介绍了在怎么时候不必要对外键加索引. 必得满意上边多少个条件:

外键创立索引提出(Foreign Key Indexing Tips)

1: 不会删除父表中的行。

 

    在ORACLE数据库中,定义外键限定期,ORACLE是不会自动创造对应索引的,必得手动在外键节制相关的列上成立索引。那么外键字段上是否有要求创制索引呢?假使有尤为重要的话,巡检时,怎么样寻找外键字段上尚无开创索引的有关表,并转移对应的目录的本子呢?

--查看全部数据库下有所主外键关系的全部表(消亡某些系统客商)

COL MODE_HELD FOR A14;

COL LOCK_TYPE FOR A8;

COL MODE_REQUESTED FOR A10;

COL OBJECT_TYPE FOR A14;

COL OBJECT_NAME FOR A20;

SELECT LK.SID,

       DECODE(LK.TYPE,

              'TX',

              'Transaction',

              'TM',

              'DML',

              'UL',

              'PL/SQL User Lock',

              LK.TYPE) LOCK_TYPE,

       DECODE(LK.LMODE,

              0,

              'None',

              1,

              'Null',

              2,

              'Row-S (SS)',

              3,

              'Row-X (SX)',

              4,

              'Share',

              5,

              'S/Row-X (SSX)',

              6,

              'Exclusive',

              TO_CHAR(LK.LMODE)) MODE_HELD,

       DECODE(LK.REQUEST,

              0,

              'None',

              1,

              'Null',

              2,

              'Row-S (SS)',

              3,

              'Row-X (SX)',

              4,

              'Share',

              5,

              'S/Row-X (SSX)',

              6,

              'Exclusive',

              TO_CHAR(LK.REQUEST)) MODE_REQUESTED, 

       OB.OBJECT_TYPE,

       OB.OBJECT_NAME,

       LK.BLOCK,

       SE.LOCKWAIT

  FROM V$LOCK LK, DBA_OBJECTS OB, V$SESSION SE

 WHERE LK.TYPE IN ('TM', 'UL')

   AND LK.SID = SE.SID

   AND LK.ID1 = OB.OBJECT_ID(+)

 AND SE.SID IN (766,789)

 ORDER BY SID;
SQL> show user;

USER is "TEST"

SQL> select * from v$mystat where rownum=1;

 

       SID STATISTIC#      VALUE

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

       766          0          1

 

SQL> delete from dead_lock_foreign where fid=2;

 

1 row deleted.
--查看某个Schema下拥有主外键关系的所有表   

SELECT DC.OWNER           AS "PARENT_TABLE_OWNER", 

       DC.TABLE_NAME      AS "PARENT_TABLE_NAME", 

       DC.CONSTRAINT_NAME AS "PRIMARY CONSTRAINT NAME", 

       DF.CONSTRAINT_NAME AS "REFERENCED CONSTRAINT NAME", 

       DF.OWNER           AS "CHILD_TABLE_OWNER", 

       DF.TABLE_NAME      AS "CHILD_TABLE_NAME" 

FROM   DBA_CONSTRAINTS DC, 

       (SELECT C.OWNER, 

               C.CONSTRAINT_NAME, 

               C.R_CONSTRAINT_NAME, 

               C.TABLE_NAME 

        FROM   DBA_CONSTRAINTS C 

        WHERE  CONSTRAINT_TYPE = 'R') DF 

WHERE  DC.CONSTRAINT_NAME = DF.R_CONSTRAINT_NAME 

       AND DC.OWNER =UPPER('&OWNER');  

 

 

3:在特别规意况下,还也会有极大希望形成死锁。

create table parent_tb_test 

(

    id         number(10),

    name    varchar2(32),

    constraint pk_parent_tb_test primary key(id)

);

 

create table child_tb_test

(

     c_id   number(10),

     f_id        number(10),

     child_name    varchar2(32),

     constraint pk_child_tb_test primary key(c_id),

     foreign key(f_id) references parent_tb_test

);

 

 

begin

    

for index_num in 1 .. 10000 loop

    insert into parent_tb_test

    select index_num , 'kerry' || to_char(index_num) from dual;

    

    if mod(index_num,100) = 0 then

        commit;

    end if;

end loop;

 

     commit;

    

end;

/

 

declare index_num number :=1;

begin

 

    for index_parent  in 1 .. 10000 loop

        for index_child in 1 .. 1000 loop

             insert into child_tb_test

             select index_num, index_parent, 'child' || to_char(index_child) from dual;

             

             index_num := index_num +1;

             if mod(index_child,1000) = 0 then

                 commit;

             end if;

        end loop;

    end loop;

 

    commit;

end;

/

 

 

SQL> execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'PARENT_TB_TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

PL/SQL procedure successfully completed.

 

SQL> execute dbms_stats.gather_table_stats(ownname => 'TEST', tabname =>'CHILD_TB_TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

 

PL/SQL procedure successfully completed.

 

SQL>

 

 

 

设假如ORACLE 11g或上述版本,数据库有深入分析函数LISTAGG的话,能够运用下边脚本

 

图片 6

--查看有些Schema下有所主外键关系的全数表

 

SQL> delete from dead_lock_parent where id=1;

 

 

 

图片 7

 

图片 8

 

地方的那些脚本已经能搜索那七个外键字段已经确立或未创设目录,那时黄金年代旦对外键字段贫乏索引的表手工业创立索引的话,若是数额比相当多以来,那么专门的学问量也不行大,上边能够用那么些剧本自动生成缺点和失误的目录

本文由澳门威斯尼人平台登录发布于澳门威斯尼人平台登录,转载请注明出处:检查重复索引,ORACLE中关于外键缺少索引的探讨和总结

相关阅读