Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why this query is sooo slow?!
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