Re: Why this query is sooo slow?!

"JZ" <ibm_97_at_yahoo.com> wrote in message news:10bc841c.0405240646.106ee14_at_posting.google.com...
> Cannot use 'use_hash', 'cause we have '>' and '<' for 'HIGH' and
> 'GEOIPADDRESS_M' table join.
>
> When I mean 10500 rows from 'HIGH' table will be retrived, this
> happens before applying the filters with 'GEOIPADDRESS_M' table:
>
> apptimestamp >= '05/02/2004 05:00:00'
> and apptimestamp < '05/02/2004 06:00:00'
> and n.nfseverity in (4,5)
> and n.deviceid in

(24,3,13,22,7,5,21,20,26,12,27,23,9,1,15,18,6,2,11,19,17,10,16,14,25,4,8)

srivenu reads your explain the same way as me (and indeed in his suggestions)

Your explain says (in relevant part)

 4    3         NESTED LOOPS (Cost=1091 Card=648896 Bytes=26604736)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'HIGH'
                               (Cost=33 Card=184 Bytes=4600)

   6    5             INDEX (RANGE SCAN) OF 'IND_H' (NON-UNIQUE) (Cost
          =3 Card=184)

   7    4           INDEX (RANGE SCAN) OF 'GEO_IP_PK' (UNIQUE) (Cost=2
          3 Card=3527 Bytes=56432)

which I read as saying pull records back from HIGH (btw I expect 184 of them), then perform a nested loops join to the results of the scan of GEO_IP_PK (there should be about 3527 rows for each row in HIGH here) and that will get me 650k rows back all together. You seem to be suggesting that these estimated cardinalities are way off.

what does
select num_rows from user_tables where table_name='HIGH' look like compared with
select count(*) from HIGH;

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com