FAQ de Base de Datos

[mauriciolangea]

Pregunta : ¿Como puedo saber si tengo indices redundantes en mi base de datos?

Respuesta :

Simple, con el script que está a continuación ...


select u1.name || '.' || o1.name || ' redundante con ' || u2.name || '.' || o2.name "INDICES REDUNDANTES"
from sys.user$ u1,sys.obj$ o1,sys.ind$ i1,sys.icol$ ic1,sys.user$ u2,
sys.obj$ o2,sys.ind$ i2,sys.icol$ ic2
where
o1.obj# = i1.obj#
and o1.owner# != 0
and o1.owner# = u1.user#
and o2.obj# = i2.obj# and o2.owner# != 0
and o2.owner# = u2.user#
and i1.bo# = i2.bo#
and i1.cols = 1
and i2.cols > 1
and ic1.obj# = i1.obj#
and ic2.obj# = i2.obj#
and ic1.bo# = i1.bo#
and ic2.bo# = i2.bo#
and ic1.col# = ic2.col#
and ic1.pos# = ic2.pos#