Rebuilding indexes.....
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Rebuilding indexes.....

Hybrid View

  1. #1
    Join Date
    Mar 2001
    Posts
    42

    Rebuilding indexes.....

    Hi Gurus,

    I am going to Rebuild some of the indexes in our database. I have given the space available in the system,temp and the tablespaces where the indexes are there. Pls guide me whether the tablespaces having enough space to rebuild or not? What r the steps i need to do before rebuild? Thanks in advance.

    system tablespace having 155MB freespace.
    Temp tablespace having 2475MB freespace.

    ds_default_ts has 7 datafiles , which is having freespace of totally 769MB.
    ds_index_ts has 5 datafiles, which is having freespace of totally 7477MB.
    ds_txn_ts has 7 datafiles, which is having freepspace of totally 1473MB.
    ds_par_ts has 4 datafiles, which is having freespace of totally 248MB.


    I have given the existing size of the indexes below.

    Tablespace Indexes size
    ---------- ------- -----

    ds_default_ts - IDX_PAYTXNPERF 405MB
    IDX_PAYSTATPERF 223MB
    PK_DS_PAY_STATUSES 111MB
    IDX_OATCUSTIDACCTNO 456MB
    IDX_OABCUSTIDACCTNO 350MB
    SYS_C0061007 833MB
    ------
    TOTAL 2378MB
    ------

    ds_index_ts - IDX_DS_PAY_TXNS 143MB
    IDX_PERF_DS_OA_TXNS 503MB
    SYS_C0058752 668MB
    DS_OA_BALANCES_IDX4 207MB
    IDX_CUSTACCT 225MB
    SYS_C0058758 1512MB
    XIF18DS_OA_BALANCES 750MB
    XIF19DS_OA_BALANCES 309MB
    XIF17DS_PAY_INVOICE_DTLS 169MB
    XIF22DS_PAY_EXCEPTIONS 2MB
    ------
    4488MB
    ------

    ds_txn_ts - SYS_C0058757 77MB
    XIF16DS_PAY_STATUSES 80MB
    SYS_C0058753 191MB
    SYS_C0030685 0.3 (311296 bytes)
    SYS_C0030690 0.73 (761856 bytes)
    SYS_C0030681 1.5MB
    SYS_C0031608 494MB
    ------
    844.53MB
    ------

    ds_par_ts - XIF19DS_PAY_FXCONTRACT_DTLS 0.58 (606208 bytes)
    XIF18DS_PAY_DOC_CHECKLISTS 1.31MB
    ------
    1.89MB
    ------

    Rgds,

    Saravanan.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    First question you have to ask yourself is why are you doing it?

    Secondly that first tablespace will blow when you try and rebuild
    SYS_C0061007

    Thirdly, why do you have an index of 0.5M on that last, a FTS maybe faster than that index

  3. #3
    Join Date
    Mar 2001
    Posts
    42

    Rebuild index...

    Hi Dave,

    Y we r rebuilding is, we r currently doing purging in that tables (7 million records). Bcs of avoiding fragmentation, and performance we plan to rebuild.

    If i run other indexes one by one there won't be a problem right ? How can i run SYS_C0061007 ? Do i need to create one more tablespace and move this SYS_C0061007 to new tablespace when rebuild or anyother way ? Pls suggest.

    Thanks & Regards,

    Saravanan.

  4. #4
    Join Date
    Jul 2003
    Location
    Sofia, Bulgaria
    Posts
    91
    Hi shravansam
    I think that you will not have problem with the rebuild, maybe for the SYS_C0061007 index in ds_default_ts tablespace, you can rebuild it in other tablespace, which has more free space than it curent size, and after that to rebuild it again in the first tablespace.
    Also may advice is to consider using COMPRESS on the indexes, it can save space and leads to better performance (that wont work well on PK/unique indexes with one column or if the first column in the index is unique). And you can alter the session in which you will rebuild the indexes with bigger SORT_AREA_SIZE.
    Good luck

  5. #5
    Join Date
    Jan 2001
    Posts
    3,131
    Well, I do hope I am worng but this is starting to sound like you need some auditing ASAP.

    First of all, do you have ANYTHING in the SYSTEM tablespace that is not owned by SYS or SYSTEM?

    Try this for starters.

    select owner, segment_name, segment_type
    from dba_segments
    where tablespace_name='SYSTEM'
    and owner not in ('SYS','SYSTEM')

    And this one for indexes.

    SELECT OWNER,INDEX_NAME
    FROM DBA_INDEXES
    WHERE TABLESPACE_NAME='SYSTEM'
    AND OWNER NOT IN ('SYS','SYSTEM')

    The only other user that should have objects in there is OUTLN.
    If you have tables and indexes in the system tablespace you should re-build them to another tablespace ASAP.

    Will you create a new tablespace for the re-build?
    Will they be online?


    Personally I like to spool a script for the rebuild and include these parameters in that re-build script...

    Spool d:\Pxxx_index_rebuild.log
    set echo on;

    Blah blah blah

    spool off;

    This way you have a log of all the re-builds, if there are any errors you can always refer to the log and make adjustments.

    MH
    I remember when this place was cool.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Also, why are you separating tables and indexes into different TS's?

    COMPRESS may be counter productive on an OLTP system, as it can increase block contention for index modifications. It's not always a bad thing that an index is spread out. Just something you should be aware of.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width