Home » RDBMS Server » Performance Tuning » How to Optimise this query (Oracle 11G)
How to Optimise this query [message #655131] Mon, 22 August 2016 17:48 Go to next message
tarundas2016
Messages: 2
Registered: August 2016
Junior Member
Hi,

In my project i have this query ,can some one suggest me how to optimize this below query

select * from asset_event ae2 where CREATED_DATE > (select ae1.CREATED_DATE from asset_event ae1 where ae1. id =(select max(ae.ID) from asset_event ae where ae.ASSET_ID=287))-1 ;

Many Thanks and Regards
Tarun
Re: How to Optimise this query [message #655133 is a reply to message #655131] Mon, 22 August 2016 18:18 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ORAFAQ tuning below -
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) ddl for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: How to Optimise this query [message #655138 is a reply to message #655133] Tue, 23 August 2016 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and also tell us, with words, what the query intends to get.

Re: How to Optimise this query [message #655527 is a reply to message #655138] Thu, 01 September 2016 04:10 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
If I had to guess I would say this is part of some inventory management system.

select max(ae.ID) from asset_event ae where ae.ASSET_ID=287

looks like it is searching recent events for some particular asset.

select ae1.CREATED_DATE from asset_event ae1 where ae1. id =(select max(ae.ID) from asset_event ae where ae.ASSET_ID=287)

would provide the date when this "most recent event" occurred.

select * from asset_event ae2 where CREATED_DATE > (select ae1.CREATED_DATE from asset_event ae1 where ae1. id =(select max(ae.ID) from asset_event ae where ae.ASSET_ID=287))-1 ;

then this would show us all events that occurred one day prior and after, the "special event".

so it looks like we are doing some kind of history checking to see what has been going in a system, around a particular date related to some special thing. This would provide some idea of what else changed with other assets around the same time as a specific change to one particular asset of interest. OK that all sounds generic enough but you get the idea.

Not sure why you can't just do this.

select * from asset_event ae2 where CREATED_DATE > (select max(ae.CREATED_DATE) from asset_event ae where ae.ASSET_ID=287)-1 ;

Which could be tuned with the indexes asset_event(asset_id,created_date) and asset_event(created_date). Though one needs to test these to be sure.

Kevin

Previous Topic: SQL Query - Just too big
Next Topic: Commit interval
Goto Forum:
  


Current Time: Thu Mar 28 13:52:34 CDT 2024