Home » RDBMS Server » Performance Tuning » Joins and in-line views (Oracle 11.2)
Joins and in-line views [message #571855] Mon, 03 December 2012 00:47 Go to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi All,

Please help me in understanding how joins work with in-line views.

I have a query and its explain plan as below:
SELECT e.ename,e.deptno,d.dname FROM 
dept d,
emp e 
WHERE e.deptno=d.deptno
AND e.deptno=20 
 
 
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   210 |    11 |
|   1 |  HASH JOIN         |      |     5 |   210 |    11 |
|   2 |   TABLE ACCESS FULL| DEPT |     1 |    22 |     5 |
|   3 |   TABLE ACCESS FULL| EMP  |     5 |   100 |     5 |
-----------------------------------------------------------
 
Note
-----
   - 'PLAN_TABLE' is old version   


I read the docuemnt at: http://www.dba-oracle.com/oracle_tips_rittman_inlineviews.htm,
section "Using outer joins with in-line views", under which he demonstartes the use of in-line views.

I changed the above query to use in-line view.
The new query and its explain plan are as below:
SELECT e.ename,e.deptno,d.dname FROM 
dept d,
(SELECT * FROM emp WHERE deptno=20) e 
WHERE e.deptno=d.deptno
 
 
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |   210 |    11 |
|   1 |  HASH JOIN         |      |     5 |   210 |    11 |
|   2 |   TABLE ACCESS FULL| DEPT |     1 |    22 |     5 |
|   3 |   TABLE ACCESS FULL| EMP  |     5 |   100 |     5 |
-----------------------------------------------------------
 
Note
-----
   - 'PLAN_TABLE' is old version


I do not find any difference in both the explain plans. Both are same.

In my second query, the filtered rows will be joined to dept table. And hence the baggage will reduce.
But how can I verify that in-line view has worked better?


Regards,
Lakshmi.

[Updated on: Mon, 03 December 2012 00:55]

Report message to a moderator

Re: Joins and in-line views [message #571858 is a reply to message #571855] Mon, 03 December 2012 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ "'PLAN_TABLE' is old version", use the current version of the plan table if you want accurate information.

2/ It does not matter how you write it (in this case) as Oracle optimizer knows what is SQL and rewrites the query in the most efficient way.

3/ NEVER relies on information from this site, it is b...

Regards
Michel

[Updated on: Mon, 03 December 2012 01:12]

Report message to a moderator

Re: Joins and in-line views [message #571859 is a reply to message #571858] Mon, 03 December 2012 01:17 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi Michel,
Thanks for your response.
You said,
Quote:

2/ It does not matter how you write it (in this case) as Oracle optimizer knows what is SQL and rewrites the query in the most efficient way.

Is there any way, I can find what oracle decides the efficient way and how it internally re-wrote the query?
Regards,
Lakshmi.

[Updated on: Mon, 03 December 2012 01:18]

Report message to a moderator

Re: Joins and in-line views [message #571864 is a reply to message #571859] Mon, 03 December 2012 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can find what oracle decides the efficient way


Activate a 10053 trace and execute the query.

Quote:
how it internally re-wrote the query?


No; in addition, some ways it rewrites a query cannot be expressed in SQL (there is no syntax for it).

Regards
Michel
Re: Joins and in-line views [message #571868 is a reply to message #571864] Mon, 03 December 2012 03:30 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi,

Please suggest:
1. Is changing the joins in long-running queries to in-line views is advisable or not?
If I can change, how can I verify the improvement in performance statistically?

Also I face another situation where in:
1. I start a sql*plus session,
2. Run the long running query with timing ON. Lets assume it takes 10mins.
3. I make some changes to the queries (Tuning changes)
4. And then re-run the query. This time, the query completes in less than 2 secs.
The reason I suspect is that the data has come from SGA and not from disk.

I can not flush the memory because there are many jobs which run in this environment.
Please suggest how can I get the actual time taken by the query every time I run the query.

Regards,
Lakshmi.
Re: Joins and in-line views [message #571870 is a reply to message #571868] Mon, 03 December 2012 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1) It is always advisable to write a query to minimize the work (and the source of errors) of the optimizer. As a general rule to write a query, do the maximum restrictions at the earliest steps possible.

2) This could be a reason.
Run the query several times for each version, ignore the first one, do not make performances test on an environment that you don't master all parameters.

Regards
Michel
Re: Joins and in-line views [message #571871 is a reply to message #571870] Mon, 03 December 2012 04:58 Go to previous messageGo to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi Michel,

Thanks for your response and valuable suggestion.

Regards,
Lakshmi.
Re: Joins and in-line views [message #572437 is a reply to message #571871] Wed, 12 December 2012 01:46 Go to previous messageGo to next message
smith586
Messages: 1
Registered: December 2012
Junior Member
learned,
thanks for u all
Re: Joins and in-line views [message #577523 is a reply to message #572437] Mon, 18 February 2013 03:18 Go to previous message
TuneMyQuery
Messages: 9
Registered: February 2013
Junior Member

Hello,

In order for Oracle to choose the best execution plan :

1) Make sure your database statistics are up to date.

2) Write your SQL statement in the simpliest possible way. In your case, a regular inner join between emp and dept is perfectly OK.

Best regards,

Emmanuel
Previous Topic: Improve DDL operation performance (2 Merged)
Next Topic: Query Sql Server faster than Oracle
Goto Forum:
  


Current Time: Thu Mar 28 12:14:44 CDT 2024