Find out less used index?

Posted in 2005

Sean Lu — Fri, 20 May 2005 05:33:35 — source: IIUG Forums & Mailing Lists

dear all, how to find out the less used indexes in a table? how many indexes are there for a table in a normal performance?

↪ replying to Sean Lu

Andreas.KUTSCHE@spar.at — Fri, 20 May 2005 07:05:08 — source: IIUG Forums & Mailing Lists

Hi, if you have detached indices (indices in their own tablespace fragments, e.g. with their own names) you can activate TBLSPACE_STATS and afterwards you can do a the following SQL on sysmaster: select dbsname,tabname,sum(bufreads),sum(pagreads),sum(isreads) from sysptprof where tabname like .... But check your SQL statements, too. If the optimizer takes the wrong indices the correct indices could have zero reads. Doesn't work for attached indices, because TBLSPACE_STATS can't distinguish the different index reads or the data reads. Regards, Andreas Kutsche > -----Ursprüngliche Nachricht----- > Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im > Auftrag von Sean Lu > Gesendet: Freitag, 20. Mai 2005 07:34 > An: ids@iiug.org > Betreff: Find out less used index? [5010] > > > dear all, how to find out the less used indexes in a table? > how many indexes are there for a table in a normal performance? > >