5 Replies Latest reply: Nov 11, 2007 11:46 AM by 311441 RSS

    Script: Lists All Indexes that Benefit from a Rebuild

    Santosh Kumar
      Dear All,

      I was going thru the metalink note 122008.1. It has a script which determines whether an index is a good candidate for a rebuild or for a bitmap index. All indexes for a given schema or for a subset of schema's are analyzed (except indexes under SYS and SYSTEM).

      I tried this script several times, but it gives blank results. Has anybody used it?
        • 1. Re: Script: Lists All Indexes that Benefit from a Rebuild
          Jonah H. Harris
          Hmm, I seem to recall a script like that from 8+ years ago. Is it the same one? As I recall, it did work at that time.
          • 2. Re: Script: Lists All Indexes that Benefit from a Rebuild
            3096
            How could you possibly know this without knowing how the index is used? The basis for these scripts is:

            -- Index is considered as candidate for rebuild when :
            -- - when deleted entries represent 20% or more of the current entries
            -- - when the index depth is more then 4 levels.(height starts counting from 1 so > 5)
            -- Index is (possible) candidate for a bitmap index when :
            -- - distinctiveness is more than 99%

            What if you're going to reinsert those 20% of deleted entries? What if 5 levels is the natural size of the index, and it's just going to grow (painfully) back to that level? What if you modify some of the values you've just bitmap indexed?

            I would focues efforts on tuning problems that you actually have, not on this.
            • 3. Re: Script: Lists All Indexes that Benefit from a Rebuild
              Santosh Kumar
              Thanks David,

              But the script should list all the indexes in a schema with all other values.i.e. it should return some value as it is shown in the script.
              • 4. Re: Script: Lists All Indexes that Benefit from a Rebuild
                Jonathan Lewis
                If you read the script, you will see that it uses dbms_output() to print out the details of an index only if it meets certain criteria. Presumably there are no indexes in your schema that meet the criteria.

                Frankly I find it hard to believe how stupid someone at Oracle has been to "update" and re-publish this script with a 10g reference.

                There is no mention of the locking problems that the validate command produces, no hint that the script might list details from one index with the name from another index because of that locking problem, and no warning that turning a B-tree index into a bitmap index may cripple an OLTP system.

                Regards
                Jonathan Lewis
                http://jonathanlewis.wordpress.com
                http://www.jlcomp.demon.co.uk
                • 5. Re: Script: Lists All Indexes that Benefit from a Rebuild
                  311441
                  Hi Jonathan

                  I feel like crying ...

                  Seriously, this script and so many like it is such a ridiculous thing to run, that it defies belief that it's still actively being promoted.

                  Sniff

                  Richard