OraFAQ Forum: Performance Tuning » Subquery Transformation

Home » RDBMS Server » Performance Tuning » Subquery Transformation (11gR2, win-xp)

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

Subquery Transformation [message #541112]

Fri, 27 January 2012 12:16

Rahm
Messages: 11
Registered: July 2011

Junior Member

I've issued the following SQL statement :

 
SELECT last_name , salary , department_id
FROM employees
WHERE department_id IN (SELECT department_id
                        FROM departments
                        WHERE department_name IN ('Executive' , 'Finance' , 'Sales'))

And that's the execution plan :

 
Execution Plan
----------------------------------------------------------
Plan hash value: 1737988890

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |    29 |   870 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN                    |               |    29 |   870 |     6  (17)| 00:00:01 |
|   2 |   INLIST ITERATOR             |               |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS   |     3 |    48 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | DEP_DNAME_IDX |     3 |       |     1   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL           | EMPLOYEES     |   107 |  1498 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Is it possible to use hints (such as USE_NL) in order to instruct the optimizer to use different transformation ?

Thank you.

Report message to a moderator

Re: Subquery Transformation [message #541114 is a reply to message #541112]

Fri, 27 January 2012 12:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

  1  SELECT last_name , salary , department_id
  2  FROM employees
  3  WHERE department_id IN (SELECT department_id
  4			     FROM departments
  5*			     WHERE department_name IN ('Executive' , 'Finance' , 'Sales'))
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 1021246405

--------------------------------------------------------------------------------------------------
| Id  | Operation		     | Name		 | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |			 |    29 |   899 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS		     |			 |	 |	 |	      | 	 |
|   2 |   NESTED LOOPS		     |			 |    29 |   899 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL	     | DEPARTMENTS	 |     3 |    48 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN	     | EMP_DEPARTMENT_IX |    10 |	 |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES	 |    10 |   150 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPARTMENT_NAME"='Executive' OR "DEPARTMENT_NAME"='Finance' OR
	      "DEPARTMENT_NAME"='Sales')
   4 - access("DEPARTMENT_ID"="DEPARTMENT_ID")

SQL>

Report message to a moderator

Re: Subquery Transformation [message #541115 is a reply to message #541114]

Fri, 27 January 2012 12:39

Rahm
Messages: 11
Registered: July 2011

Junior Member

Hi BlackSwan, thanks for your reply. How did you manage to achieve this ? I don't see any explicit hint usage

Report message to a moderator

Re: Subquery Transformation [message #541117 is a reply to message #541115]

Fri, 27 January 2012 12:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I did exactly as below

SQL> CONNECT HR/HR
SQL> set autotrace traceonly explain

& then PASTEd in your SQL

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Report message to a moderator

Re: Subquery Transformation [message #541118 is a reply to message #541112]

Fri, 27 January 2012 12:51

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Oracle determines the best plan depending on the information it has. If you give it bad or no information then it may not choose the best plan.

Why do you think it chooses another plan?
Why do you think it does not choose the best plan?

Regards
Michel

Report message to a moderator

Re: Subquery Transformation [message #541119 is a reply to message #541117]

Fri, 27 January 2012 12:56

Rahm
Messages: 11
Registered: July 2011

Junior Member

Ok, so lets say my Subquery transforms into a Nested Loop, now I want to explicitly change the execution plan so it will choose Hash Join. Is it possible ?

Report message to a moderator

Re: Subquery Transformation [message #541120 is a reply to message #541119]

Fri, 27 January 2012 12:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.lmgtfy.com/?q=oracle+stored+outlines

you presume to be smarter than the CBO; which I doubt reflects reality.

[Updated on: Fri, 27 January 2012 12:59]

Report message to a moderator

Re: Subquery Transformation [message #541122 is a reply to message #541119]

Fri, 27 January 2012 13:15

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

Quote:

Ok, so lets say my Subquery transforms into a Nested Loop, now I want to explicitly change the execution plan so it will choose Hash Join. Is it possible ?

Of course it is. Use hints, as you said in your first post.

Report message to a moderator

Re: Subquery Transformation [message #541124 is a reply to message #541122]

Fri, 27 January 2012 13:23

Rahm
Messages: 11
Registered: July 2011

Junior Member

Hey John, that's the first thing I tried to do

 
  1  SELECT /*+ USE_NL (e d) */ e.last_name , e.salary , e.department_id
  2  FROM employees e
  3  WHERE department_id IN (SELECT d.department_id
  4                          FROM departments d
  5*                         WHERE d.department_name IN ('Executive' , 'Finance' , 'Sales'))
HR> /

Execution Plan
----------------------------------------------------------
Plan hash value: 2413492578

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |    29 |   870 |     6  (17)| 00:00:01 |
|*  1 |  HASH JOIN                    |               |    29 |   870 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS FULL           | EMPLOYEES     |   107 |  1498 |     3   (0)| 00:00:01 |
|   3 |   INLIST ITERATOR             |               |       |       |            |          |
|   4 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS   |     3 |    48 |     2   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | DEP_DNAME_IDX |     3 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   5 - access("D"."DEPARTMENT_NAME"='Executive' OR "D"."DEPARTMENT_NAME"='Finance' OR
              "D"."DEPARTMENT_NAME"='Sales')

Report message to a moderator

Re: Subquery Transformation [message #541125 is a reply to message #541124]

Fri, 27 January 2012 13:26

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The question is still: why do you want to change it?
Let me say it in another way: even if you can change it using a hint in a version and environment, it does not mean it will work in another one.
Hints are ONLY for experts, you are not, so forget them.

Regards
Michel

Report message to a moderator

Re: Subquery Transformation [message #541126 is a reply to message #541124]

Fri, 27 January 2012 13:31

John Watson
Messages: 8962
Registered: January 2010
Location: Global Village

Senior Member

It works for me:

orcl> set autot traceonly exp
orcl> SELECT /*+ USE_HASH (e d) */ e.last_name , e.salary , e.department_id
  2      FROM employees e
  3      WHERE department_id IN (SELECT d.department_id
  4                              FROM departments d
  5                             WHERE d.department_name IN ('Executive' , 'Finance' , 'Sales'));

Execution Plan
----------------------------------------------------------
Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    29 |   899 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |             |    29 |   899 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENTS |     3 |    48 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
   2 - filter("D"."DEPARTMENT_NAME"='Executive' OR
              "D"."DEPARTMENT_NAME"='Finance' OR "D"."DEPARTMENT_NAME"='Sales')

orcl> SELECT /*+ USE_NL (e d) */ e.last_name , e.salary , e.department_id
  2      FROM employees e
  3      WHERE department_id IN (SELECT d.department_id
  4                              FROM departments d
  5                             WHERE d.department_name IN ('Executive' , 'Finance' , 'Sales'));

Execution Plan
----------------------------------------------------------
Plan hash value: 1021246405

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |    29 |   899 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   |       |       |            |          |
|   2 |   NESTED LOOPS               |                   |    29 |   899 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | DEPARTMENTS       |     3 |    48 |     3   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |   150 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("D"."DEPARTMENT_NAME"='Executive' OR "D"."DEPARTMENT_NAME"='Finance' OR
              "D"."DEPARTMENT_NAME"='Sales')
   4 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")

orcl>

Report message to a moderator

Re: Subquery Transformation [message #541127 is a reply to message #541126]

Fri, 27 January 2012 13:41

Rahm
Messages: 11
Registered: July 2011

Junior Member

Thanks John, that's all I needed to know.

Report message to a moderator

Re: Subquery Transformation [message #541129 is a reply to message #541127]

Fri, 27 January 2012 14:08

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And what will do with this?
It does not work for you!

Regards
Michel

Report message to a moderator

Re: Subquery Transformation [message #541130 is a reply to message #541129]

Fri, 27 January 2012 14:17

Rahm
Messages: 11
Registered: July 2011

Junior Member

Well now I'm investigating why. anyway, I was asking a very simple Yes or No question ... I appreciate your help but "what will do with this ... Hints are ONLY for experts, you are not" is just irrelevant

Report message to a moderator

Re: Subquery Transformation [message #541148 is a reply to message #541130]

Fri, 27 January 2012 23:53

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Yes/no is irrelevant in REAL word but you can live in your thought world if you want.
I just want no one follow your psychotic way and warn future readers.

Regards
Michel

Report message to a moderator

Previous Topic:	Is it possible to display alternate execution plans ?
Next Topic:	scripts on tablespaces,Indexes and DBJobs

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 16:15:24 CST 2025