Re: Query running extremely slow after create/drop index

From: Christian Svensson <chse30_at_hotmail.com>
Date: 28 May 2003 07:09:20 -0700
Message-ID: <ccc2a7eb.0305280609.3f3f8e2a@posting.google.com>

> 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.

The explain plans are the same on Pre and Prod:

OPERATION OPTIONS OBJECT_NAME SELECT STATEMENT

---

UNION-ALL      		
HASH JOIN      		
INDEX          	FAST FULL SCAN 	RKEM_LOAD_VALFLAG_TYPE_INDX
PARTITION RANGE	ALL            	
INDEX          	FAST FULL SCAN 	RKEM_EQPNO_DCDATE_INDX
HASH JOIN      		
TABLE ACCESS   	BY INDEX ROWID 	RKEM_LOAD
INDEX          	RANGE SCAN     	RKEM_LOAD_VALFLAG_TYPE_INDX
INDEX          	FAST FULL SCAN 	PK_RKEM

and the query looks like this:

select /*+use_hash(l r)*/ r.rowid
from rkem_load l,

       rkem r

where  l.eqpno between nvl(gvFromEq,'!') and nvl(gvToEq,'z')
and    l.validated_flag = '!'
and    l.rkem_type = 'M'
and    l.eqpno = r.eqpno
and    l.dcdate = r.dcdate

union all
select /*+use_hash(l r)*/ r.rowid
from rkem_load l,

       rkem r

where  l.eqpno between nvl(gvFromEq,'!') and nvl(gvToEq,'z')
and    l.validated_flag = '!'
and    l.rkem_type = 'M'
and    l.eqpno = r.eqpno
and    l.actdate=r.actdate;

The query retreives about 150.000-220.000 rows

And new statistics are gathered since my create/drop index operation.

The database has also been restarted (you never know).

I am very very confused about this. So if anyone have any smart ideas I would be very grateful for these.

Cheers all !

/Christian Received on Wed May 28 2003 - 09:09:20 CDT