August 26, 2004 at 2:37 pm
I use Query Analyzer, one statement at a time
PLEASE: Verify before drop any index
The output is:
tab, idx, "contained index", "index that contains"
USE ....
-- step 1: get tab,idx,col,order
create view listaidxcols as
select SO.name as tabname,
SI.name as idxname,
IK.keyno as keyno,
SC.name as colname
from sysindexkeys IK,
syscolumns SC,
sysindexes SI,
sysobjects SO
where -- Liga syscolumns
IK.id=SC.id
and IK.colid=SC.colid
-- Liga sysindexes
and IK.id=SI.id
and IK.indid=SI.indid
-- Liga a sysObjects (tablas)
and IK.id=SO.id
and SO.xtype='U'
and SI.name not like '_WA_Sys_%'
and SI.name not like 'hind_%'
--step 2 get # of cols x index
create view cantcolsidx
as select tabname,
idxname,
count(*) as numllaves
from listaidxcols
group by tabname,idxname
--step 3 get redundant index list
select A.tabname as tabla,A.idxname as Aidx, B.idxname as Bidx
from cantcolsidx A, cantcolsidx B
where A.tabname = B.tabname
and A.numllaves < B.numllaves
and A.idxname <> B.idxname
and A.numllaves in (
select count(*)
from listaidxcols C, listaidxcols D
where C.tabname=A.tabname
and C.idxname=A.idxname
and D.tabname=B.tabname
and D.idxname=B.idxname
and C.idxname<>D.idxname
and C.colname=D.colname
and C.keyno =D.keyno
)
-- step 4 clean up
drop view listaidxcols;
drop view cantcolsidx;
August 30, 2004 at 8:00 am
This was removed by the editor as SPAM
September 2, 2004 at 10:22 pm
You are detecting indexes where the column list of one is a subset of the other (and same sequence).
Will you drop the longer one or the shorter one ?
What about identical indexes (A.numllaves = B.numllaves) ?
You cannot drop indexes needed for primary or foreign keys, so ideally you should report that too.
I personally wouldn't automate too much. I would do two left-joins to keyno=1 and key=2, to get a list sorted by the first two columns, because further columns are usually redundant for performance, and decide what to do about each case of similar indexes.
September 3, 2004 at 8:54 am
generally you drop the shorter index, cause the larger one, must be more selective.
But in the case the shorter one is a key, there is a catch, sql server, could evite a trip to the table if it uses the larger secuence, at a penalty at update time, if its not the case, you must drop the larger one
you have 2 real good points, it doesnt consider equal sized keys, and you must use your judgement, in each case
thanks for the correction, the idea its to get consciuos of changes that the index wizard does.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply