Home » SQL & PL/SQL » SQL & PL/SQL » performance tuning of the query (oracle 11g)
performance tuning of the query [message #571596] Tue, 27 November 2012 16:22 Go to next message
Messages: 122
Registered: April 2005
Senior Member

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.
Re: performance tuning of the query [message #571600 is a reply to message #571596] Tue, 27 November 2012 17:14 Go to previous messageGo to next message
Messages: 25295
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
3) output from SQL_TRACE & tkprof
Re: performance tuning of the query [message #571605 is a reply to message #571596] Wed, 28 November 2012 01:18 Go to previous message
Michel Cadot
Messages: 64610
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Previous Topic: nested tables in pl/sql
Next Topic: email notification using oracle advance queue
Goto Forum:

Current Time: Sun Mar 26 04:28:33 CDT 2017

Total time taken to generate the page: 0.19805 seconds