Skip to Content
avatar image
Former Member

Rebuilding indexes with Bad Storage Quality

Hi All,

I need to Know how you can find out indexes with Bad storage quality and then i have to prepare a List of all these indexes and go for an Online Reorginzation, can some one help me in getting this data and the procedure as to how you do it.. I know rebulding Particular index using Brtools, but not Pretty sure about the Online Reorganization

Regards,

Ershad Ahmed.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

5 Answers

  • avatar image
    Former Member
    May 14, 2007 at 03:53 AM

    Hi Ershad,

    It is a good practise to rebuild all indexes as they improve performance and release unused storage (due to delete operations). It will be time consuming activity to identify which indexes are currupt, just run rebuild for all the indexes in SAP schema.

    Run a SQL script to generate list of all the indexes and its rebuild script . The index rebuild operation that I run is:

    alter index SAPR3E."<object name>" rebuild parallel 20 online nologging;

    I leave this running during the weekend (part of cron) and this does wonders to the database performance. The rebuild is fast as there is no logging done and it uses parallel servers to build your indexes.

    Cheers,

    Nisch

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Scott,

      no there is no such switch.

      The reason for this is, that indexes that have been created with NOLOGGING will lead to corrupt blocks after a recovery.

      Since we strive to minimize downtime even in case of a recovery it's not wanted to have the need for index rebuilds after a recovery just before the system can go online again.

      Especially for the UNIQUE indexes that are used to make sure the primary keys are unique this is a rather dangerous situation when the data is changed while the index is not usable.

      BTW: Why do you perform a ALTER INDEX LOGGING after you've rebuild the index? Be aware that the NOLOGGING flag applies for the index (re)build ONLY! All other changes afterwards are logged.

      There is the Option to use the NOLOGGING flag for indexes in BW systems - but this is also not recommended for HA BW systems. It's a performance feature with a heavy drawback once you've to recover. In some cases dbv will still report corrupt blocks even after all indexes have been rebuild.

      So this should not be some to be used in your productive OLTP system.

      KR Lars

  • avatar image
    Former Member
    May 11, 2007 at 09:32 AM

    Index can be checked using: ANALYZE INDEX <ix> VALIDATE STRUCTURE;

    But also check whether the index is used:

    alter index <index_name> monitoring usage;

    and check view v$object_usage

    e.g.

    desc index_stats

    set linesize 120

    SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space

    FROM index_stats

    WHERE name = 'IX_TEST';

    ANALYZE INDEX ix_test VALIDATE STRUCTURE;

    SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space

    FROM index_stats

    WHERE name = 'IX_TEST';

    SELECT opt_cmpr_count, opt_cmpr_pctsave

    FROM index_stats;

    DROP INDEX ix_test;

    CREATE INDEX ix_test

    ON test (object_name, object_type)

    PCTFREE 0

    COMPRESS 1;

    ANALYZE INDEX ix_test VALIDATE STRUCTURE;

    SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space

    FROM index_stats

    WHERE name = 'IX_TEST';

    SELECT opt_cmpr_count, opt_cmpr_pctsave

    FROM index_stats;

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 11, 2007 at 07:53 PM

    Refer to SAP note 332677

    Regards

    Manohar

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 15, 2008 at 12:21 PM

    Hi Ahmed,

    please run report RSORAISQ or RSORAISQN --> check notes 712098 and 1026796

    kind regards

    Davy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 10, 2016 at 04:09 AM

    This message was moderated.

    Add comment
    10|10000 characters needed characters exceeded