-
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
-
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)
-
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
-
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.
-
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.
-
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.
-
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.
-
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.
-
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
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|