Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Why this query is sooo slow?!

Re: Why this query is sooo slow?!

From: JZ <ibm_97_at_yahoo.com>
Date: 20 May 2004 08:36:37 -0700
Message-ID: <10bc841c.0405200736.58ef3723@posting.google.com>


Here are the index info:

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

Explain plan:

Execution Plan


   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5012 Card=1213 Bytes
          =73993)

   1    0   SORT (GROUP BY) (Cost=5012 Card=1213 Bytes=73993)
   2    1     NESTED LOOPS (Cost=1093 Card=648896 Bytes=39582656)
   3    2       NESTED LOOPS (Cost=1092 Card=648896 Bytes=30498112)
   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)

   8    3         INDEX (UNIQUE SCAN) OF 'GEO_INFO_PK' (UNIQUE)
   9    2       INDEX (UNIQUE SCAN) OF 'GEO_COUNTRY_PK' (UNIQUE)


Thanks a lot! Received on Thu May 20 2004 - 10:36:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US