Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Rebuilding indexes
I've been experimenting with so called 'Browned-Out Indexes' those
that are inefficent space wise and I can't seem to get the Oracle
Rebuild or Coalesce features to work? I've tried running both on a
non-PK index, I then restarted the database and looked at the
following query (which tells me which indexes are browned out)
However thier stats never change? If I drop and recreate an index it seems to work though. Anybody have any insight on this?
Ta
Emmet
query follows...
select u.name index_owner, o.name
index_name,substr(to_char(100*i.rowcnt*(sum(h.avgcln)+11)/
(i.leafcnt*(p.value-66-i.initrans*24)),'999.00'),2) || '%' density,
floor((1-i.pctfree$/100)*i.leafcnt-i.rowcnt*(sum(h.avgcln)+11)/(p.value
- 66 - i.initrans*24)) extra_blocks
,di.leaf_blocks, di.blevel
from sys.ind$ i, sys.icol$ ic, sys.hist_head$ h, (select kvisval value
from sys.x$kvis where kvistag = 'kcbbkl') p,
sys.obj$ o, sys.user$ u, dba_indexes di
where i.leafcnt > 1 and i.type# in (1,4,6) and ic.obj# = i.obj# and
h.obj# = i.bo# and h.intcol# = ic.intcol# and o.obj# = i.obj# and o.owner# != 0 and u.user# = o.owner#