DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: urgent

  1. #1
    Join Date
    Aug 2000
    Posts
    32

    urgent

    Hi Gurus,
    What is the prerequiste for Rebuilding indexes and what is the procedure .I need command also.help me.

    RGds
    Babu

  2. #2
    Join Date
    Dec 2002
    Location
    India & Australia
    Posts
    68

    urgent

    How about using Index Organized Tables (IOT )?

    VAST

  3. #3
    Join Date
    Dec 2002
    Location
    India & Australia
    Posts
    68

    urgent

    Create table

    columns....
    .........


    )
    Reorganize index Tblspc_name < TBl Name>
    ........
    ;

  4. #4
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    the syntax for rebuilding indexes is:
    ALTER INDEX indexname REBUILD;

    If you want to rebuild all the indexes in your schema then do this:
    sql>SPOOL 'REBUILD.SQL'
    sql>SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM USER_INDEXES;
    sql>SPOOL OFF
    sql>@REBUILD
    Agasimani
    OCP(10g/9i/8i/8)

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Just out of nterest, why do you want to rebuild your indexes?
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447

    Re: urgent

    Originally posted by venkattr
    Hi Gurus,
    What is the prerequiste for Rebuilding indexes and what is the procedure .I need command also.help me.

    RGds
    Babu
    if someone needs to be a guru to know the command to rebuild an index we might as well all retire!

    RTFM

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Re: urgent

    Originally posted by vastdba
    How about using Index Organized Tables (IOT )?

    VAST
    How about not using them :-)
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Jan 2003
    Location
    London, UK
    Posts
    4

    rebuilding indexes

    Why rebuild and not recreate? what are you trying to achieve or fix?
    What optimiser you using? (optimiser_mode in the init.ora)

    You need to be careful playing with indexes using the RB0 (Rule based) as you effect the database performance!

    m00n

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hmm rebuild is faster you know? and it achieve the same goal as drop and recreate, also if we use online clause we can rebuild the index online without affecting performance (and index is still valid for users)

  10. #10
    Join Date
    Jan 2003
    Location
    London, UK
    Posts
    4

    index rebuild

    Yes you are correct the rebuild can be faster, but will not fix
    corruptions.

    The performance issue is related to the Rule Based Optimser, and not the performance hit to rebuild the index.

    e.g. if multiple indexes can be applied to the WHERE clause, and they all have an equal number of columns specified, only the index
    created last will be used.

    I have experience performance problems when indexes have been recreated, and the performance has taken a dive. (just one to
    be cautious about)

    rgds,
    m00n

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