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

Thread: Help with indexes

  1. #1
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74

    Help with indexes

    Hi all

    I have a table which gets populated every 3 months with about 400,000 records. This insertion happens only once every 3 months and is used for reporting purposes. My question is how often should I re-build the indexes on this table?? Can anyone advice me of the pro's and con's of re-building the indexes on this table every 3 months.

    Thanks in advance.

    Regards

    Rajani
    rajorcl

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If it's a complete refresh of the table performed as one insert, then make the indexes unusable, then ALTER SESSION SET SKIP_UNUSABLE_INDEXES= TRUE and perform the insert, then rebuild the indexes.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  3. #3
    Join Date
    Jun 2003
    Location
    australia
    Posts
    74
    Thanks Dave.

    Here is the exact situation, I've got a table which is populated 4 times every 3 months. And everytime about 100,000 records are inserted. The previous data remains there for a year and later archived and then deleted (Not a refresh). This table is used only for reporting once every 3 months. I've been told to disable the index while insertion and rebuild it later if its a refresh, but it is not. So what do I do in this case??

    Please advice.

    Regards
    rajorcl

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What you seem to have here is a load-report cycle similar to a little decision support system, which means that you can take the opportunity during the load to do all sorts of things that will speed up reporting and reduce it's burden on the system later on.

    This means, for example, that when you insert your 100,000 records you can physically cluster the values -- if the most common columns on which people will query are col1, col2, and col3, then when you insert into the table make sure that you add "ORDER BY COL1, COL2, COL3" to the query. This will improve performance on all reports that use those columns as predicates.

    If you are on 9i you can compress the data as well. ALTER TABLE MY_TABLE COMPRESS and then either use a direct path "INSERT /*+ APPEND */ INTO" to get compressed data, or "ALTER TABLE MY_TABLE MOVE COMPRESS" to compress the whole lot after it has been populated.

    If you have the Partitioning Option, then consider using a partitioned table, to make it easier to drop that old data.

    Now having said that, if your current process works, and does not take too long to run reports or load the data, then forget about all that and stick with what you're doing now. Rebuild indexes if you like ... you might like to measure performance before and after just to see if it's worthwhile, but it probably would not do too much harm at any rate.
    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