Home » SQL & PL/SQL » SQL & PL/SQL » Help with query rewrite using analytic function (Oracle SQL)
Help with query rewrite using analytic function [message #347103] Wed, 10 September 2008 13:07 Go to next message
shrustigreddy
Messages: 7
Registered: May 2005
Junior Member
Hi,

Can you pls help rewrite the following SQL using Oracle Analytic functions...purpose is to tune and spped up the query...

select * from tab_1 where (id,date_column)
in(
Select id, max(date_column) from tab_1
group by id
)

TIA
SGR
Re: Help with query rewrite using analytic function [message #347104 is a reply to message #347103] Wed, 10 September 2008 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Help with query rewrite using analytic function [message #347106 is a reply to message #347103] Wed, 10 September 2008 13:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.


Regards
Michel
Re: Help with query rewrite using analytic function [message #347504 is a reply to message #347106] Thu, 11 September 2008 22:27 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It is possible that a simple index on (ID, DATE_COLUMN) will produce a better result than an analytic function.

Make sure you gather statistics on the index though.

Ross Leishman
Previous Topic: Retrieving Data Based on MAX()
Next Topic: Auto Email Using DBMS_Schedular
Goto Forum:
  


Current Time: Thu Feb 13 14:14:01 CST 2025