Home » SQL & PL/SQL » SQL & PL/SQL » How to replace Row_number and generate the same result set (Oracle 10G)
How to replace Row_number and generate the same result set [message #422723] Fri, 18 September 2009 09:13 Go to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Hi,

I have a query like....

select decode(a.doc_type_id, null, row_number() over (partition by a.nbk_id||b.juke_id||c.sun.id||d.hak_id order by b.ingested_dttm NULLS LAST),1)
from
ta a,
tab b,
tabc c,
tabcd d
where a.nbk_id = c.sun_id(+);

Now i want to replace the row_number...partition clause and generate the same result set by other options.
Thanks in advance.
Re: How to replace Row_number and generate the same result set [message #422725 is a reply to message #422723] Fri, 18 September 2009 09:34 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Why?
Re: How to replace Row_number and generate the same result set [message #422726 is a reply to message #422723] Fri, 18 September 2009 09:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You have a query that works, using builtin Oracle functionality to perform a fairly complex process.

Why do you want to change this query?

There are other ways of doing this, and they are all
1) Slower
2) More complex


Re: How to replace Row_number and generate the same result set [message #422729 is a reply to message #422723] Fri, 18 September 2009 09:52 Go to previous messageGo to next message
bholeuday
Messages: 28
Registered: April 2009
Location: Talavali
Junior Member

Actually this is the task given to mi, i wrote sql using analytical function but that too is running very slow, so my seniors told to do without using analytical function (they think it may be slow because of analytical function). Whereas the things is Analytic function is itself performance booster, but still need to do this..
Re: How to replace Row_number and generate the same result set [message #422737 is a reply to message #422723] Fri, 18 September 2009 10:06 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
bholeuday wrote on Fri, 18 September 2009 16:13
Hi,

I have a query like....

select <something>
from 
ta    a,
tab   b,
tabc  c,
tabcd d
where a.nbk_id = c.sun_id(+);

Just for clarification: this statement queries 4 tables, but only two of then are joined. That means, that the resultset will contain all row combinations of TAB, TABCD with left join of TA and TABC. If TAB, TABCD are large tables, that resultset may be very large. Reducing it e.g. by adding proper join conditions would surely improve query performance.
Do you really want this resultset? How many rows are present in all involved tables?
Re: How to replace Row_number and generate the same result set [message #422758 is a reply to message #422729] Fri, 18 September 2009 15:43 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
bholeuday wrote on Fri, 18 September 2009 07:52
they think it may be slow because of analytical function
They think? It's very easy to prove one way or the other if it is or isn't a problem. Prove it isn't the problem and then tell your seniors to fix it themselves.
Previous Topic: How to convert simple SQL into Stored Procedures?
Next Topic: Excel report not formatting correctly in SQL*Plus (multi-merge)
Goto Forum:
  


Current Time: Wed Sep 28 10:52:27 CDT 2016

Total time taken to generate the page: 0.09142 seconds