When I tried to add a new index to a table, I experienced strange turn
out that I have no clue. Hope some guru can explain this. What happed as
following:
I found out a trigger that includes select and insert statements for a
table was slow. So I added a new index to the table based on the where
clause of select statement. As I tried the first time to run the
procedure, it was very fast comparing that of before indexing.
In order to confirm this, then I dropped the same index and delete the
same set of records, which I just inserted. Run Again. It was slow, of
course.
So I deleted the same set records again and recreated the same index for
the table before I rerun the same trigger to insert the records (The
third time). Guessed what! It was slower than without the index and much
worse than that of first inserting when I added the index.
I kept repeating the drop index/delete records/insert records/delete
records/? add index/insert records/ for two more times, and the insert
with the index became worse and worse. What was going on? Did my
add/drop the index cause the disruption something because of the same
data set? Or there was some temp records stuck. Because I noticed that
the insert became very fast again only after I rebooted the server. I
committed after each step of add/drop index and delete the records. My
system is 8.1.7.1.4 on NT 4 with single CPU. The size of the table has
500K rows records. What the trigger does is to check on the existence of
the records then called upon an insert procedure for the new record. I
used the sqlldr80 from 8.0.5 to load the records.
C Chang
Received on Thu May 06 2004 - 16:06:28 CDT
