Forum Stats

  • 3,783,348 Users
  • 2,254,761 Discussions
  • 7,880,371 Comments

Discussions

Index Rebuilding on Huge Tables

user585870
user585870 Member Posts: 162
edited Apr 13, 2010 7:03AM in General Database Discussions
Dear All,

1. we are in process of rebuilding an index on table A

2. Insert operation on this table A is huge. 24*7 insertion is going on.

3. During the rebuild process the insertion process in very slow and gives some ORA-7445 Error. So right now we are doing the index rebuild by stopping the insert operation.

4. We want to perform the index rebuild process with out stopping the batch jobs.

if anywork around is available,kindly share.

Thanks in Advance
Tagged:

Answers

  • MsJ
    MsJ Member Posts: 936
    Online index features:

    ALTER INDEX REBUILD ONLINE;

    DMLs are allowed on the base table
    It is comparatively Slow
    Base table is referred for the new index
    Base table is locked in shared mode and DDLs are not possible
    Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later

    Offline Index Rebuild Features:

    ALTER INDEX REBUILD; (Default)

    Does not refer the base table and the base table is exclusively locked
    New index is created from the old index
    No DML and DDL possible on the base table
    Comparatively faster
  • user585870
    user585870 Member Posts: 162
    Hi,

    Thank you for the reply.

    We tried the Alter Rebuild index Online, the same issues were happening. we are forced to stop the batch job.

    Number of rows in the table is 982764476
  • MsJ
    MsJ Member Posts: 936
    Use parallel to run faster.

    Alter index scott.IDX_DTT_EMP rebuild online PARALLEL 10;
  • Pavan Kumar
    Pavan Kumar Member Posts: 11,904 Gold Crown
    Hi,

    ORA-7445 - refers to so many things, which we need to consider. Try to provide the exact error informaiton. AFAIK, usually it will be OS issues, bugs and parameters sizing Issues. As you stated that Index re-build is carried out online,did you crossed check temporary tabelspace size and sga sizing is appropriate or not. Which impacts the performance of re-building.

    HTH

    - Pavan Kumar N
    - ORACLE OCP - 9i/10g
    https://www.oracleinternals.blogspot.com
  • Rajesh Lathwal
    Rajesh Lathwal Member Posts: 2,423
    What is the Oracle version here ?

    also please paste the lines from alert file stating the ORA 7445 error..

    There is also a bug related to this... and this is for 10G database.

    Bug 6767655 Dump [kauxs_do_journal] from concurrent DML while rebuilding index ONLINE
    ORA-7445 (kauxs_do_journal) [ID 756799.1]


    Regards
    Rajesh
  • user585870
    user585870 Member Posts: 162
    Hi All,


    Version Info:
    Oracle 10.2.0.3 - 64 bit 2 node RAC system on HP-UX B.11.31 U ia64

    Even after using the parallel cluase also the same issue is happening.

    Right Now we dont have the exact error information. This happened one year back and after that we are rebuiliding after stopping the batch jobs. this will works fine.
  • Rajesh Lathwal
    Rajesh Lathwal Member Posts: 2,423
    Hi,

    Without error info, it will be difficult to troubleshoot.

    Rgds
    Rajesh
  • user585870
    user585870 Member Posts: 162
    Hi Rajesh,

    I am searching in my archives. will update you soon
This discussion has been closed.