Re: Query running extremely slow after create/drop index

chse30_at_hotmail.com (Christian Svensson) wrote in message news:<ccc2a7eb.0305230604.291e3149_at_posting.google.com>...
> 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

how many records are you trying to return? post the explain plan. Im willing to bet you didnt gather statistics again and are doing a nested loops join instead of a hash join. or vice versa.

indices and nested loops joins are much slower is used on big tables where you are returning alot of records.

post the explain plans along with the query. Its probably going to be your join. Received on Fri May 23 2003 - 11:50:25 CDT