Re: Why this query is sooo slow?!

From: Noel <tomekb_at_softman.pl>
Date: Fri, 21 May 2004 11:07:07 +0200
Message-ID: <c8kgqv$sbo$1@inews.gazeta.pl>

> 'high' table: primary key is 'eventid' column. Index 'ind_h' has
> 'APPTIMESTAMP', 'DEVICEID', 'SOURCEINT' and 'NFSEVERITY' columns.
> 'geocountry_m' table has index (geo_country_pk) on 'COUNTRYCODE'
> column
> 'geoipaddress_m' table has primary key (geo_ip_pk) on 'STARTIP' and
> 'ENDIP' columns
> 'geoinformation_m' table has primary key (GEO_INFO_PK) on 'BLOCKID'
> column, and index on 'COUNTRYCODE' column.
> Related constraints among the tables in the query:
> 'BLOCKID' column of 'geoipaddress_m' table is the foreign key of the
> same column in 'GEOINFORMATION_M' table
> 'COUNTRYCODE' column of 'GEOINFORMATION_M' is the foreign key of the
> same column in 'GEOCOUNTRY_M' table.
> And I do collect stats from 'dbms_stats'.

select
TO_CHAR(n.apptimestamp,'') period,
c.countryname,
sum(n.eventcount)
from
high n

,geocountry_m c
,geoipaddress_m ip
,geoinformation_m info

where
n.apptimestamp[I] >= '05/02/2004 05:00:00'

and  n.apptimestamp[I] < '05/02/2004 06:00:00'
and  n.nfseverity[I] in (4,5)
and  n.deviceid[I] 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) and ip.blockid[FK] = info.blockid[PK]

and c.countrycode [PK] = info.countrycode[I]
and n.sourceint[I] >= ip.startip[PK]
and n.sourceint[I] <= ip.endip[PK]

group by
TO_CHAR(n.apptimestamp,'')
,c.countryname;

Its your query with all your info applied. I wonder if ip.block is indexed.
Try to create index on that column.

--
TomekB

Received on Fri May 21 2004 - 04:07:07 CDT