Query running extremely slow after create/drop index

Greeetings !

For a batchjob with a select query joining two large tables (~220.000 and ~160 millions of rows), this query that normally took ~50 min to finish now takes 6-8 longer time to finish.

This after I have created an index on one of the tables and later dropped the same index (to try tune another query).

I have flushed the sga so it does not reuse the plan as when the index was created, I have also changed the pl/sql code for the statement (added a space mark) to also eliminate the reuse.

This is on a Preproduction environment so we have opportunity to restart the database to see if it helps (you never know). After comparing against the production database the table/index settings is the same, there are also same amount of data on both pre and prod, the explain plan when running this query is strangely almost the same on pre and prod. So I am very confused why the query takes so much longer time, it smells like a bug...

Sun Solaris 6 (I know...) / Oracle 8.1.7.3.0

But if anyone have encountered this same situation and have some kind of hints, please send a reply on this group.

Thanks.

Cheers !

/Christian Svensson Received on Fri May 23 2003 - 09:04:03 CDT