| Performance Issue with the query [message #571595] |
Tue, 27 November 2012 16:21 |
gentleman777us
Messages: 122
Registered: April 2005
|
Senior Member |
|
|
Hi,
Iam querying the table which has over 8 million rows with the following query, It takes lot of time to query. Any suggestions to improve the query performance is greatly appreciated.
select out.* from (select r.category, r.subcat, r.lat, r.lon sdo_geom.sdo_distance(r.geom,latlon2geom(38.93486,-77.26026), 0.5, 'UNIT=MILE')dist,id, nvl(source, 'SHIP2010') as source
from (select * from shippers where sdo_within_distance(geom, latlon2geom(38.93486,-77.26026), 'DISTANCE=1 UNIT=MILE') = 'TRUE' and upper(category) = 'FARMING' AND upper(subcat) = 'CATTLE') R order by dist) out
where rownum <= 1;
I have indexes on geom column, function based index on upper(category), upper(subcat) columns.
latlon2geom is a function we have all it does it it converts the input to point geometry type
ex: latlon2geom(38.93486,-77.26026) = (2001, 8307, (-77.26026, 38.93486,),,)
Here is the explain plan for the above query
5 count stopkey
4 view TOM
cost: 766 bytes: 5250 cardinality:14
3 sort order by stopkey
cost: 766 bytes: 2,478 cardinality:14
2 Table access by index rowid TABLE shippers
cost: 765 bytes 2,478 cardinality: 14
1 Index range scan index shippers_subcat_fnct_idx
cost:119 cardinality: 33,832
Note: Unfortunately I can not create a test case as the database is on a different network and my email access is on a different network. The email access network doesn't have any oracle installed.
Thank you very much for your prompt reply.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
