DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Index question

  1. #1
    Join Date
    Mar 2001
    Posts
    149

    Index question

    Hi all, im wondering what's the difference between drop/create index compared to alter index index_name rebuild? can i use rebuild command to fix index logical block corruption? Please help. I'm using Oracle 8.1.6

  2. #2
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    Alter index rebuild is based on the index-date stored int he current index.
    If there are some corruption ind the index, i think rebuild will fail.
    Orca

  3. #3
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    When you rebuild an index, you use an existing index as the data source. Create an index in this manner enables you to change storage characteristics or move to a new tablespace. Rebuilding an index based on an existing data source removes intra-block fragmentation. Compared to droping the index and using the CREATE INDEX statement, re-creating an existing index offers better performance.

    It need double space to rebuild an index, using COALESCE index to reduce fregmentation and free up disk space when you have no more disk space.

    As Orca777 said, you need to Drop/Create Index if the original index got corrupted.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by Calvin_Qiu
    It need double space to rebuild an index, using COALESCE index to reduce fregmentation and free up disk space when you have no more disk space.
    COALESCE Index ?? Never heard about this!
    You meant COALESCE tablespace, isn't it?

    Sanjay

  5. #5
    Join Date
    Jul 2000
    Location
    Amsterdam
    Posts
    234
    Coalesce index is a 9i thingy.

    Tycho

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    coaslece index is available since 8i

    what it does is coalescing the space between leaves although I dont think it frees physical space, it just coalesce free blocks like extents so they can be reused more efficiently

    check

    http://download-west.oracle.com/docs...dexes.htm#3103
    Last edited by pando; 10-28-2002 at 07:20 AM.

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Thanks for the url Pando.
    I wasn't aware of this 8i feature.

    Sanjay

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