Re: Rebuilding indexes

"emmet ryan" <emmet_at_bitsys.ie> wrote in message news:e0d93a1e.0212080541.4fc1d98a_at_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;

Several suggestions here
1 - please always provide your version. No one here is clairvoyant and no one keeps track of what everyone is using. 2 Do *NOT* rely on physical dictionary objects: they are not guaranteed to be portable from release to release. Hence the query and information here you provide is meaningless as similar results might be obtained by the published index_stats view
3 Please define 'can't seem to get to work' You're not running any analyze, so why would the statistics change?
4 Also your compatible parameter might be set to lower than 8.1.0.0 (coalesce) or 7.3.3.0 (rebuild), in which case these features aren't available.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address