OraFAQ Forum: Performance Tuning » Joins and in-line views

Home » RDBMS Server » Performance Tuning » Joins and in-line views (Oracle 11.2)

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

Joins and in-line views [message #571855]

Mon, 03 December 2012 00:47

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

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

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

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

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

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

Report message to a moderator

Re: Joins and in-line views [message #571868 is a reply to message #571864]

Mon, 03 December 2012 03:30

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.

Report message to a moderator

Re: Joins and in-line views [message #571870 is a reply to message #571868]

Mon, 03 December 2012 04:32

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

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

Report message to a moderator

Re: Joins and in-line views [message #571871 is a reply to message #571870]

Mon, 03 December 2012 04:58

lakshmis
Messages: 102
Registered: November 2008
Location: India

Senior Member

Hi Michel,

Thanks for your response and valuable suggestion.

Regards,
Lakshmi.

Report message to a moderator

Re: Joins and in-line views [message #572437 is a reply to message #571871]

Wed, 12 December 2012 01:46

smith586
Messages: 1
Registered: December 2012

Junior Member

learned,
thanks for u all

Report message to a moderator

Re: Joins and in-line views [message #577523 is a reply to message #572437]

Mon, 18 February 2013 03:18

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 29 15:21:24 CST 2025