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

Thread: index organization

  1. #1
    Join Date
    Aug 2001
    Posts
    267

    index organization

    Every day we are loading around 2gb data into a table using SQL*LOADER. There are some indexes on the table . I am looking for better way. Is droping the index before loading and creating after the load is better and faster, or just rebuilding the index after the load is faster?? . Usually rebuilding index online is better for 24*7 systems since the old one is still available for users. But we don't have any user activity during the process.

    But I have to rebuild the index since we are truncating the table every day and loading 2gb of data.( I don't have any partitions on the table)

    Need some tuning advice because some times I am passing the window and the users are getting hit by that.
    Raghu

  2. #2
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    Loading data without an index is considerably faster than with an index. Since you can afford to have the table unavailable:

    - drop the indexes
    - SQL load the data
    - recreate the indexes

    This will be the fastest method.

    P.S. The table will be available while the indexes are in the creation process. The access will be via full table scan until the index creates are complete.
    alapps

    Fast, Cheap, Reliable... Pick Two(2)

  3. #3
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    It is also advised that you put your index and data on 2 different harddisk controllers .
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  4. #4
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    I have the same sort of scenario every night with my batch. Batch overuns were frequently hitting users.

    Part of my batch actually referenced SOME indexes, but all the ones superfluous to the batch, I dumped. (Save primary key indexes, which are used to enforce the constraint.)

    Every morning, there was a server CPU hit when I recreated all the indexes, but this was only for an hour.

    I usually use:

    create index on (field) tablespace parallel(degree 10) nologging;

    One word of warning though. I found that parallel rebuilds in a non-locally managed tablespace caused interim parallel extent allocations of less than the default tablespace settings for extents.

    Thus, although the final segment extents ALL matched the settings for the tablespace (or extent sizes specified in the rebuild statement), fragmentation of the tablespace occurred through the parallel execution completely ignoring extent settings and doing whatever it wanted.

    The solution to this seems to be to create locally managed tablespace with Uniform extent sizes. This forces any parallel rebuild extent allocations to match the uniform sizing, and doesn't let them do their own thing!!

    I still have some terrible fragmentation in some index tablespaces that have been subjected to parallel rebuilds over time. I am migrating all these to locally managed as and when I can.

    Oh - you have to have system managed uniform extent local managed. You can't try forcing anything by migrating existing tablespaces to locally managed.

    What a pain!

    :-)

    - Tony.

  5. #5
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    Hmmm!

    How the hell did I manage to get ALL that strike through text in my post!

    PASS!

    Many apologies to all who try and read it!

    :-(

    T.

  6. #6
    Join Date
    Nov 2001
    Location
    Sheffield, England, UK
    Posts
    78
    I have just realised, ALL my comments or script that appeared inside 'less than' or 'greater than' symbols has been truncated, and this may have caused the alteration to 'strike-through' text somehow.

    So... in the above index creation statement, (which now looks wrong!!), it should read:

    create index 'index_name' on 'table_name'(field.s) tablespace 'tablespace_name' parallel(degree 10) nologging;

    :-)

    Hope that looks better!

    Doh!

    T.

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    Reposting tonys post

    I have the same sort of scenario every night with my batch. Batch overuns were frequently hitting users.

    Part of my batch actually referenced SOME indexes, but all the ones superfluous to the batch, I dumped. (Save primary key indexes, which are used to enforce the constraint.)

    Every morning, there was a server CPU hit when I recreated all the indexes, but this was only for an hour.

    I usually use:

    create index on (field) tablespace parallel(degree 10) nologging;

    One word of warning though. I found that parallel rebuilds in a non-locally managed tablespace caused interim parallel extent allocations of less than the default tablespace settings for extents.

    Thus, although the final segment extents ALL matched the settings for the tablespace (or extent sizes specified in the rebuild statement), fragmentation of the tablespace occurred through the parallel execution completely ignoring extent settings and doing whatever it wanted.

    The solution to this seems to be to create locally managed tablespace with Uniform extent sizes. This forces any parallel rebuild extent allocations to match the uniform sizing, and doesn't let them do their own thing!!

    I still have some terrible fragmentation in some index tablespaces that have been subjected to parallel rebuilds over time. I am migrating all these to locally managed as and when I can.

    Oh - you have to have system managed uniform extent local managed. You can't try forcing anything by migrating existing tablespaces to locally managed.

    What a pain!

    :-)

    - Tony.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    A more robust approach than dropping/loading/creating indexes might be to make the indexes nuusable, load (with skip_unusable_indexes=true), then rebuild unusable indexes. You can't accidentally lose an index, and adding, removing, or redefining indexes from the target table does not require you to modify a post-SQL*Loader script.
    Last edited by slimdave; 03-20-2003 at 10:08 AM.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  9. #9
    Join Date
    Aug 2001
    Posts
    267
    I am now doing exactly what slimdave pointed . I am using skip_unusable_indexes=true option while the load . And I am rebuilding all the indexes . But In my case not much performance. Because I am using conventional load but not direct load because I got triggers on my tables. So I can't use direct load option. Still I am passing my window in both options that I first posted. I got 12 tables like that.

    Now I am thinking nologging option to my tablespaces and rebuilding indexes with nolog option. I will try this may improve the performance. Any more advice from gurus. I don't need to recover (in case) since I always have datafiles backed up.

    Thanks for all that replied to my question.
    Raghu

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Are you rebuilding indexes all at the same time or serially? Serial would be pretty slow i would think.

    You could also include "compute statistics" in the rebuild.
    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