Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Rebuilding indexes

Rebuilding indexes

From: emmet ryan <emmet_at_bitsys.ie>
Date: 8 Dec 2002 05:41:57 -0800
Message-ID: <e0d93a1e.0212080541.4fc1d98a@posting.google.com>


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#

and di.owner = u.name and di.index_name = o.name group by u.name, o.name,i.rowcnt,i.leafcnt,i.initrans,i.pctfree$,p.value,di.leaf_blocks, di.blevel
having 50 * i.rowcnt * (sum(h.avgcln) + 11) < (i.leafcnt * (p.value - 66 - i.initrans * 24 )) *
(50 - i.pctfree$) and floor((1-i.pctfree$/100) * i.leafcnt - i.rowcnt
* (sum(h.avgcln)+ 11)
/ (p.value - 66 - i.initrans *24) )>0
order by 3 desc, 2; Received on Sun Dec 08 2002 - 07:41:57 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US