Home » SQL & PL/SQL » SQL & PL/SQL » Query regarding subqueries & execution plan (Oracle 9.0.2.8)
Query regarding subqueries & execution plan [message #430111] Sun, 08 November 2009 08:37 Go to next message
hjoshi
Messages: 7
Registered: November 2009
Location: Sydney
Junior Member
Hi,

Once again, my lack of understanding ..

What I had to start off with is this query

Query A
SELECT 
  FROM table1 a, 
       table2 b, 
       table3 c
 WHERE a.user = b.user
   AND a.id = b.id
   AND b.category = c.category
   AND a.id = c.id
   AND a.user = c.id
   AND c.user = <Some Value 1>
   AND c.id = <Some Value 2>


What I've been wanting to do is dynamically populate <Some Value 1> and <Some Value 2> fields. To illustrate, the query to dynamically retrieve the input field values is something like this ..

Query B
SELECT id, user
  FROM table4
 WHERE  ....etc..etc..


This is what I've been doing

Query C
SELECT 
  FROM table1 a, 
       table2 b, 
       table3 c,
       (SELECT id, user
          FROM table4
         WHERE  ....etc..etc..) d
 WHERE a.user = b.user
   AND a.id = b.id
   AND b.category = c.category
   AND a.id = c.id
   AND a.user = c.id
   AND c.user = d.user
   AND c.id = d.id



What I'm seeing is ..
- If I'm manually putting in the input values into Query A, the results are returned almost instantaneously.
- Query B is resulting the results almost instantaneously.
- However, Query C (which is Query B put into Query A as an inline view) runs extremely slowly.

Doing an explain plan, I could see that Query C's explain plan was altered quite differently to what I was seeing from Query A.

My Question is .. is it possible to introduce a subquery into a SELECT statement without affecting it's execution plan .. or giving the inline view some sort of precedence. ? I don't want the inline view query getting 'Joined' ..but rather it's output to get used as input values into the outer query.

Regards,

Himanshu
Re: Query regarding subqueries & execution plan [message #430119 is a reply to message #430111] Sun, 08 November 2009 19:49 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When you provide values in the SQL, Oracle knows where to start evaluating the query: it starts with the filter predicates you provided.

In the last version, Oracle sees only JOIN predicates. For all it knows, table4 may have values matching EVERY row in tables 1,2, and 3. It will probably start with Table1 and join every single row to Tables 2 and 3 before finally filtering non-matching rows in the join to table4.

You will need to TELL Oracle that it is best to start the query with Table 4.

SELECT /*+ LEADING(d) */
  FROM table1 a, 
       table2 b, 
       table3 c,
       (SELECT id, user
          FROM table4
         WHERE  ....etc..etc..) d
 WHERE a.user = b.user
   AND a.id = b.id
   AND b.category = c.category
   AND a.id = c.id
   AND a.user = c.id
   AND c.user = d.user
   AND c.id = d.id


Ross Leishman
icon7.gif  Re: Query regarding subqueries & execution plan [message #430149 is a reply to message #430119] Mon, 09 November 2009 03:59 Go to previous message
hjoshi
Messages: 7
Registered: November 2009
Location: Sydney
Junior Member
Hi Ross - Many thanks. That did the trick Smile
Previous Topic: Help In tuning but facing "Not Group By expression error"
Next Topic: stored procedure cursor and loop
Goto Forum:
  


Current Time: Sat Dec 03 18:03:28 CST 2016

Total time taken to generate the page: 0.09308 seconds