Re: Strange phenomenon of add/drop new index

Maybe I should elaborate on the index. The index is created on three members of the same table, that are used in the where phrase of the select statement in the trigger. One of the members is the DATE type. Because the where phrase is like such
Where a = v_a
And b = v_b
And to_char( cdate, 'YYYYMMDD') = to_char( v_cdate, 'YYYYMMDD');

So I created the index with function of to_char(cdate, 'YYYYMMDD') and activate the function index requirement in the init.ora file However, even I tried another index without the cdate member, I still got the same phenomenon.
I hope this information help to solve my confusion.

C Chang

cschang wrote:

> 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 - 21:52:20 CDT