OraFAQ Forum: Performance Tuning » How to Optimise this query

Home » RDBMS Server » Performance Tuning » How to Optimise this query (Oracle 11G)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

How to Optimise this query [message #655131]

Mon, 22 August 2016 17:48

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

Report message to a moderator

Re: How to Optimise this query [message #655133 is a reply to message #655131]

Mon, 22 August 2016 18:18

BlackSwan
Messages: 26208
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

Report message to a moderator

Re: How to Optimise this query [message #655138 is a reply to message #655133]

Tue, 23 August 2016 00:53

Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: How to Optimise this query [message #655527 is a reply to message #655138]

Thu, 01 September 2016 04:10

Kevin Meade
Messages: 2102
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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Oct 23 13:20:12 CDT 2018