Home » SQL & PL/SQL » SQL & PL/SQL » Restricting recordset before joining to large table?
Restricting recordset before joining to large table? [message #188438] Fri, 18 August 2006 10:11 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
I'm joining a series of tables that have many rows. tblParam has over 4 million rows and most of the other tables have about 300,000. So you can imagine that when I try to do an ORDER BY on the joined tables it takes forever (literally). But I only need the most recent 200 workorders to be ordered. So I tried this...

SELECT /*+ rule*/ tblWorkorder.workorder as workorder, nvl(tblWorkorder.priority,'') as priority 
FROM tblWorkorder 
INNER JOIN tblSample ON tblSample.workorder = 
(select tblWorkorder.workorder from tblWorkorder where tblWorkorder.workorder = tblSample.workorder and rownum < 200 ORDER BY tblWorkorder.datesubmitted)
INNER JOIN tblAnalysis ON tblSample.sampleindex = tblAnalysis.sampleindex 
INNER JOIN tblPackage ON tblAnalysis.packageid = tblPackage.packageid 
INNER JOIN tblPackageParam ON tblPackage.packageid = tblPackageParam.packageid 
INNER JOIN tblParam ON tblPackageParam.paramid = tblParam.paramid
WHERE tblWorkorder.datesubmitted Is Not null 
and tblParam.instrument = 'IC'


But it doesn't like the ORDER BY clause in my sub-select. Even without the ORDER BY clause in the sub-select it doesn't appear to restrict the resulting recordset to 200 workorders. Am I going about this wrong?

[Updated on: Fri, 18 August 2006 10:12]

Report message to a moderator

Re: Restricting recordset before joining to large table? [message #188442 is a reply to message #188438] Fri, 18 August 2006 10:24 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
You cannot:

select tblWorkorder.workorder from tblWorkorder where tblWorkorder.workorder = tblSample.workorder and rownum < 200 ORDER BY tblWorkorder.datesubmitted


You have to:

select *
  from (select tblWorkorder.workorder
          from tblWorkorder
         where tblWorkorder.workorder = tblSample.workorder
        ORDER BY tblWorkorder.datesubmitted))
and rownum < 200



to get the top-200 (i.e. 199) records.

[Updated on: Fri, 18 August 2006 10:27]

Report message to a moderator

Re: Restricting recordset before joining to large table? [message #188443 is a reply to message #188438] Fri, 18 August 2006 10:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, the RULE hint is going to be hurting you quite badly with this - The RBO is a big fan of indexed nested loops, which aren't what you want when you're dealing with a result set this big.

Loose that, and make sure the tables are analyzed would be my first hint.

Your subquery syntax is wrong.
You've got
(select workorder 
from tblWorkorder 
where tblWorkorder.workorder = tblSample.workorder 
and rownum < 200 
ORDER BY tblWorkorder.datesubmitted)
, but that will just give you 200 rows at random from the Db, ordered into date order.
You want something like
(select workorder 
from (SELECT workorder,rownum rnum
      FROM tblWorkorder  
      WHEREtblWorkorder.workorder = tblSample.workorder
      ORDER BY tblWorkorder.datesubmitted)
WHERE rnum < 200)

That will order by the date and give you the first 200 rows.

I also wonder if you'll need to convert the = to an IN, but I'm a miserable old curmudeon, and don't use that new-fangled ANSI syntax, so I can't say for certain.

{Drat - pipped to the post, and by a very similar looking solution]

[Updated on: Fri, 18 August 2006 10:34]

Report message to a moderator

Re: Restricting recordset before joining to large table? [message #188447 is a reply to message #188443] Fri, 18 August 2006 10:52 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Thanks for the replies. I converted my SQL to the old way and implemented your solution (with a couple necessary changes). It runs now but is extremely slow (still running) despite doing analyze on tables in question. See any errors?

SELECT tblWorkorder.workorder as workorder, nvl(tblWorkorder.priority,'') as priority 
FROM tblWorkorder, tblSample, tblAnalysis, tblPackage, tblPackageParam, tblParam
WHERE
tblSample.workorder = 
(select workorder from 
(SELECT tblWorkorder.workorder,rownum rnum FROM tblWorkorder,tblSample WHERE tblWorkorder.workorder = tblSample.workorder ORDER BY tblWorkorder.datesubmitted) 
WHERE rnum < 200 and workorder = tblSample.workorder)
and tblSample.sampleindex = tblAnalysis.sampleindex 
and tblAnalysis.packageid = tblPackage.packageid 
and tblPackage.packageid = tblPackageParam.packageid 
and tblPackageParam.paramid = tblParam.paramid
and tblWorkorder.datesubmitted Is Not null 
and tblParam.instrument = 'IC'
Re: Restricting recordset before joining to large table? [message #188448 is a reply to message #188447] Fri, 18 August 2006 10:55 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>SELECT tblWorkorder.workorder as workorder, nvl(tblWorkorder.priority,'') as priority
>FROM tblWorkorder, tblSample, tblAnalysis, tblPackage, tblPackageParam, tblParam
You should only have one tables in the FROM clause because you are SELECTing only data only FROM TBLWORKORDER
The remaining tables shold be subordinated into the WHERE clause using EXISTS

[Updated on: Fri, 18 August 2006 10:56] by Moderator

Report message to a moderator

Re: Restricting recordset before joining to large table? [message #188449 is a reply to message #188448] Fri, 18 August 2006 10:59 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
anacedent wrote on Fri, 18 August 2006 10:55


The remaining tables shold be subordinated into the WHERE clause using EXISTS


Could I trouble you to provide sample code (I guess they need an "extremely newbie" forum Wink)

Will this improve performance?
Re: Restricting recordset before joining to large table? [message #188450 is a reply to message #188438] Fri, 18 August 2006 11:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Will this improve performance?
I have done this with numerous SQL statements.
The amount of improvement varies, but I remember 1 case where the query time went from 200 seconds to 10 seconds.
YMMV

P.S.
With advice you can have it good, fast, or free. Pick any two.
Re: Restricting recordset before joining to large table? [message #188493 is a reply to message #188450] Fri, 18 August 2006 14:20 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Incidentally, this is the code I ended up using. It makes use of "IN" which executes first thereby limiting the recordset right away...

SELECT distinct tblWorkorder.workorder as workorder, nvl(tblWorkorder.priority,'') as priority, datesubmitted
FROM tblWorkorder, tblSample, tblAnalysis, tblPackage, tblPackageParam, tblParam
WHERE
tblWorkorder.workorder in (select wo from (select tblWorkorder.workorder as wo from tblWorkorder ORDER BY priority,tblWorkorder.datesubmitted) WHERE rownum < 200)
and tblWorkorder.workorder = tblSample.workorder
and tblSample.sampleindex = tblAnalysis.sampleindex 
and tblAnalysis.packageid = tblPackage.packageid 
and tblPackage.packageid = tblPackageParam.packageid 
and tblPackageParam.paramid = tblParam.paramid
and tblWorkorder.datesubmitted Is Not null 
and tblParam.instrument = 'ION CHROMATOGRAPH'
order by priority, datesubmitted


I would like to understand how to use EXISTS to eliminate tables from FROM clause but I don't have money or time so I'm out of luck I guess.

Thanks for everyone's help!
Re: Restricting recordset before joining to large table? [message #188504 is a reply to message #188493] Fri, 18 August 2006 15:49 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You'd use a syntax like:
SELECT tblWorkorder.workorder as workorder, nvl(tblWorkorder.priority,'') as priority, datesubmitted
FROM tblWorkorder
WHERE
tblWorkorder.workorder in (select wo from (select tblWorkorder.workorder as wo from tblWorkorder ORDER BY priority,tblWorkorder.datesubmitted) WHERE rownum < 200)
and exists (SELECT null 
            FROM tblSample, tblAnalysis, tblPackage, tblPackageParam, tblParam 
            WHERE tblWorkorder.workorder = tblSample.workorder
            AND   tblSAmple.sampleindex = tblAnalysis.sampleindex 
            and tblAnalysis.packageid = tblPackage.packageid 
            and tblPackage.packageid = tblPackageParam.packageid 
            and tblPackageParam.paramid = tblParam.paramid
            and tblParam.instrument = 'ION CHROMATOGRAPH')
and tblWorkorder.datesubmitted Is Not null 
order by priority, datesubmitted

The two advantages of this approach are
1) The EXISTS subquery will stop executing as soon as it finds the first row that matched the criteria - it won't have to go and retrieve the whole result set
2) By removing all the multiple rows returned from the many table joins, you can get rid of the DISTINCT in the SELECT
Re: Restricting recordset before joining to large table? [message #188505 is a reply to message #188504] Fri, 18 August 2006 15:55 Go to previous message
ferrethouse
Messages: 43
Registered: August 2006
Member
WOW!

That is awesome. From 20 seconds down to 2.5. I can definitely live with 2.5. I had created a materialized view but now I don't need it.

Thank you so much!
Previous Topic: Assign select value to variable?
Next Topic: PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
Goto Forum:
  


Current Time: Thu Dec 08 04:32:29 CST 2016

Total time taken to generate the page: 0.12246 seconds