Indexes and speed

[THoey]

I have a large table (about 6 million rows) that had three indexes created for it. When I would load a file using SQL*Loader, it was a fairly quick process to load 300K of rows. Recently, I created a 4th index. Now when I run the load, it takes forever. Should creating that final index slow it down that drastically or do you think there is something else? I am leaning to the something else, but am not sure... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

As a side note, I had two files to load. The first was 323,160 rows. With the indexes created, it took over 70 mins to load it. The second had 500+K rows. I dropped the indexes and loaded it in less than four minutes.

I guess it is the number of indexes, but it doesn't seem like it should be that way. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[uparna]

Hi Terry ,
It is a fact that indexes enhance the performance of the database , but the flop side is that when there is data loading taking place , the indexes decrease the performance. The reason is this : while data is being added/appended , the concerned indexes also have to be updated with the new data ..... and the more number of indexes you have , the longer it takes it complete the updation. simple eh? I guess you have one option left here : write a script which creates the indexes you want .Then , everytime before you load the data , drop the indexes and then load the data. After data has been loaded (this should be compleated in a jiffy now!), run the script which shall create the indexes for you. voila! problem solved . Only drawback is that the database performance may be slow incase there are users accessing it during the creation of the indexes .

Best of luck !
Jayaram.

 

[sem]

Try to increase sort_area_size. Your 4th index may cause using temporary tablespase for sorting if your sort_area_size is not large enough.