Professional Web Applications Themes

Rebuilding indexes - Oracle Server

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.avgc ln)+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 ...

  1. #1

    Default 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.avgc ln)+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;
    emmet ryan Guest

  2. #2

    Default Re: Rebuilding indexes


    "emmet ryan" <emmetbitsys.ie> wrote in message
    news:e0d93a1e.0212080541.4fc1d98aposting.google.c om...
    > 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.avgc ln)+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 yze,
    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


    Sybrand Bakker Guest

Similar Threads

  1. indexes in cs
    By Albert_Constantineau@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 1
    Last Post: August 14th, 09:41 PM
  2. using indexes
    By tragik in forum Coldfusion Database Access
    Replies: 0
    Last Post: January 9th, 07:03 PM
  3. rebuilding database through DR fails
    By Hoyte Swager in forum Informix
    Replies: 3
    Last Post: October 27th, 12:20 PM
  4. Rebuilding a logo
    By catxnc webforumsuser@macromedia.com in forum Macromedia Fireworks
    Replies: 8
    Last Post: September 28th, 02:35 AM
  5. Rebuilding the kernel - establishing the default settings
    By Sean in forum Linux Setup, Configuration & Administration
    Replies: 1
    Last Post: July 4th, 10:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139