[ASE]Recherche des index redondants

[danmicka]

Bonjour,

Connaissez-vous une requête utilisant les tables système qui permet de retrouver les index redondants d'une base.

merci,

michael

[mpeppler]

Par indexe redondant je suppose que vous entendez des indexes dont les colonnes initiales sont une sous-ensemble de l'autre (et dans le même ordre).

Dans ce cas une variation de la procédure que j'ai postée il y a quelque temps pour recréer les indexes pourrait être utilisée:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
 
CREATE TABLE #ixdata(tabname varchar(60), name varchar(60), cols varchar(255))
go
 
declare ixcsr cursor
FOR SELECT name, id, indid FROM sysindexes
   WHERE id IN (SELECT id FROM sysobjects WHERE type = 'U')
go
 
SET nocount ON
 
declare @name varchar(32)
      , @id int
      , @indid int
      , @colid int
 
declare @cmd varchar(300)
      , @tab varchar(32)
      , @colname varchar(32)
      , @colorder varchar(32)
 
open ixcsr
 
fetch ixcsr INTO @name, @id, @indid
 
while @@sqlstatus = 0
begin
    SELECT @tab = object_name(@id)
 
    SELECT @colid = 1
 
    SELECT @cmd = ""
    while @colid <= 31
    begin
        SELECT @colname = index_col(@tab, @indid, @colid)
             , @colorder = index_colorder(@tab, @indid, @colid)
 
        IF @colname IS NULL
            goto LAST_COLUMN
 
        IF @colid > 1
            SELECT @cmd = @cmd + ", "
 
        SELECT @cmd =  @cmd + @colname + " " + @colorder
 
	SELECT @colid = @colid + 1
 
    end
 
    LAST_COLUMN:
 
    INSERT #ixdata values(@tab, @name, @cmd)
 
    fetch ixcsr INTO @name, @id, @indid
end
 
close ixcsr
 
deallocate cursor ixcsr
go
 
SELECT tabname, name, cols FROM #ixdata i1
WHERE EXISTS (SELECT * FROM #ixdata i2 
           WHERE i1.tabname = i2.tabname 
              AND i2.cols LIKE i1.cols + "%" 
              AND i1.name != i2.name)
AND cols != ' '
go
 
DROP TABLE #ixdata
go

Donc pour chaque indexe on extrait la liste des colonnes et on insert cette information dans une table temporaire. Ensuite on liste les entrées dans cette table temporaire dont les colonnes se trouvent comme sous-ensemble dans un autre indexe.

Attention - ce code n'a été testé que minimalement, donc je suggère de le valider correctement avant de lui faire aveuglément confiance!

Michael

[danmicka]

Merci bcp michael,

je viens de tester ton code dans un procédure et ça fonctionne très bien.

michael

[dbafranck]

Voici une petite évolution à cette procédure pour prendre en compte le cas des index qui gèrent des contraintes.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
CREATE proc sp_index_redondant AS
 
CREATE TABLE #ixdata(tabname varchar(30),
                     name varchar(30),
                     pk char(2), -- Index de cle primaire
                     uk char(2), -- Index unique
                     fk char(2), -- Index servant a la gestion qu’au moins une contrainte d’integrite referentielle
                     cols varchar(255))
 
declare ixcsr cursor
FOR
    SELECT
        name,
        id,
        pk= CASE
            WHEN (STATUS & 2048) = 2048 THEN "PK"
            ELSE "--"
            END,
        uk= CASE
            WHEN (STATUS & 2) = 2 THEN "UK"
            ELSE "--"
            END,
        fk= CASE
            WHEN (status2 & 1) = 1 THEN "FK"
            ELSE "--"
            END,
        indid
        FROM sysindexes
        WHERE id IN (SELECT id FROM sysobjects WHERE type = 'U') AND indid NOT IN (0,255)
 
SET nocount ON
 
declare @name varchar(32)
      , @id int
      , @pk char(2)
      , @uk char(2)
      , @fk char(2)
      , @indid int
      , @colid int
 
declare @cmd varchar(300)
      , @tab varchar(32)
      , @colname varchar(32)
      , @colorder varchar(32)
 
open ixcsr
 
fetch ixcsr INTO @name, @id, @pk, @uk, @fk, @indid
 
while @@sqlstatus = 0
begin
    SELECT @tab = object_name(@id)
 
    SELECT @colid = 1
 
    SELECT @cmd = ""
    while @colid <= 31
    begin
        SELECT @colname = index_col(@tab, @indid, @colid)
             , @colorder = index_colorder(@tab, @indid, @colid)
 
        IF @colname IS NULL
            goto LAST_COLUMN
 
        IF @colid > 1
            SELECT @cmd = @cmd + ", "
 
        SELECT @cmd =  @cmd + @colname + " " + @colorder
 
                 SELECT @colid = @colid + 1
 
    end
 
    LAST_COLUMN:
 
    INSERT #ixdata values(@tab, @name, @pk, @uk, @fk, @cmd)
 
    fetch ixcsr INTO @name, @id, @pk, @uk, @fk, @indid
end
 
close ixcsr
 
deallocate cursor ixcsr
 
SELECT tabname, name, pk, uk, fk, cols INTO #ix from #ixdata i1
WHERE EXISTS (SELECT * FROM #ixdata i2
              WHERE i1.tabname = i2.tabname
              AND i2.cols LIKE i1.cols + "%"
              AND i1.name != i2.name)
 
IF (SELECT count(*) FROM #ix) > 0
begin
 
    declare @len char(12) -- Formattage affichage de cols
    SELECT @len = str(max(len(cols))) FROM #ix
 
    print " Index redondants. Verifiez notamment les elements suivants avant de proceder a toute suppression :"
    print "     1. Si cols est identique pour plusieurs lignes d'une meme table, il est possible qu'un des index correspondant ne soit pas a supprimer."
    print "     2. Pour les index unique (uk), verifiez que vous n'allez pas supprimer un index utile dans le cadre de la verification d'une contrainte d'unicite."
    print " Pour realiser ces verifications, vous devez regardez la liste de tous les index de la table concernee (sp_helpindex <table>)."
    print ""
    -- On retire du resultat les index de cle primaire et les index servant a la gestion de contraintes d'integrites referentielles car ces index ne sont pas senses etre inutiles.
    execute("select tabname, name, uk, cols = substring(cols, 1,"+@len+") from #ix where pk != 'PK' and fk != 'FK' order by tabname, cols")
end
 
else
    print "Pas d’index redondant detecte. "
 
DROP TABLE #ixdata
DROP TABLE #ix