Help with indexes
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.
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.
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??
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.
Click Here to Expand Forum to Full Width