Home » RDBMS Server » Performance Tuning » Subquery Transformation (11gR2, win-xp)
Subquery Transformation [message #541112] Fri, 27 January 2012 12:16 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
John Watson
Messages: 8930
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
John Watson
Messages: 8930
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 23 11:39:46 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.