Home » RDBMS Server » Performance Tuning » Efficient Way - Outer join Vs Not Exists (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Efficient Way - Outer join Vs Not Exists [message #593727] Tue, 20 August 2013 06:39 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi all,

Please let me know which one efficient way to retrieve the info which is there one table and not there in another table ?

Ex :

The following two queries gives the same results. As these tables contains less data I didn't find any difference in their EXPLAIN PLAN also .

SQL> 
SQL> select d.*
  2    from scott.emp e, scott.dept d
  3   where e.deptno(+) = d.deptno
  4     and e.deptno is null;
 
DEPTNO DNAME          LOC
------ -------------- -------------
    40 OPERATIONS     BOSTON
 
SQL> 
SQL> select *
  2    from scott.dept d
  3   where not exists (select 1 from scott.emp e where d.deptno = e.deptno);
 
DEPTNO DNAME          LOC
------ -------------- -------------
    40 OPERATIONS     BOSTON
 
SQL> 




But I have same kind requirement in my project which is having very large data.
Are there any factors to decide which one to use

Thanks
SaiPradyumn

Re: Efficient Way - Outer join Vs Not Exists [message #593729 is a reply to message #593727] Tue, 20 August 2013 07:00 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Have a look at this
Re: Efficient Way - Outer join Vs Not Exists [message #593770 is a reply to message #593727] Tue, 20 August 2013 14:39 Go to previous messageGo to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

You want to read about efficient SQL, it's all here

http://www.orafaq.com/papers/sqltiptr.pdf

Regards
Manu
Re: Efficient Way - Outer join Vs Not Exists [message #593786 is a reply to message #593770] Tue, 20 August 2013 23:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
not sure what version of the database you are using.

On 11gR2 most of these kinds of questions are moot. The optimizer is able to transform queries as it sees fit in order to get better performance. If the outer join version is faster in one case Oracle will figure that out and rewrite your query to that form. If the correlated subquery is faster for a different case, Oracle will figure that out and rewrite your query to that form. So you no longer need to worry about it.

The trick to making this work is THE BASICS. If you get the basics right, Oracle can do all its fancy stuff.

Quote:

Basics means understand your SQL WORKLOAD and use the right data modeling paradigm to support it (OLTP / DSS (batch reporting) / ANALYTIC).

Basics means define constraints (PK / UK / FK / NOT NULL / CHECK)(RELY constraints are OK for ANALYTIC models).

Basics means use proper data types: DATE = DATE, NUMBER = NUMBER, STRING = STRING and no trying to be smarter about it.

Basics means understand your SQL WORKLOAD (hey I said that before didn't I!) and index accordingly.

OLTP=index your keys.
DSS=create SQL STATEMENT SPECIFIC PERFORMANCE INDEXES to give your reports the right start.
ANALYTIC=create BITMAP indexes on all your FACT table foreign keys (these point back to your dimensions).

Basics means define a stats collection strategy that works with your data loading processes and then monitor it for things like STALENESS, OUT-OF-BOUNDS, SKEW, DEPENDENCE.

Basics means learning how to avoid simple mistakes when writing SQL like implicit data type conversion and functions on indexed columns and the top-10 SQL Mistakes (whatever they are).

NOTICE that Basics does NOT mean parallel query or partitioning or advanced index types (function based/IOT) or other advanced features AND ESPECIALLY NOT HINTS.


You do the basics right and Oracle will do all sorts of query rewrite magic and plan generation magic and runtime optimization magic for you without you needing to worry about "which is the best way to write my query" cause whatever you write, Oracle is going to change it anyway.

But if you and the people around you skimp on the basics then you will eventually need someone like me... A guy who gets paid lots of money to do a job that is essentially a waste. I recently concluded that my existence in my company is really a waste of money. Over the last three years I have been doing almost exclusively SQL Tuning (with great success if I may brag a bit) and I have realized that 9 out of 10 things I fix exist only because someone skimped on one of the basics. They did not take care of the basics so my job is all about fixing stuff that should never have been broken in the first place. What a waste. Oh well. I like my company a lot and intend to keep on giving them my best. But maybe if I say it enough times someone will listen.

Quote:
- 99% of all performance problems are caused by a lack of respect for the basics.


Good luck. Kevin

[Updated on: Tue, 20 August 2013 23:14]

Report message to a moderator

Re: Efficient Way - Outer join Vs Not Exists [message #593793 is a reply to message #593786] Wed, 21 August 2013 01:33 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member




Thanks you very much for your valuable suggestions. Sure Kevin ,I will check once again about all basics information.

Thanks
SaiPradyumn
Previous Topic: identifying query problem
Next Topic: Stats on a partitioned table
Goto Forum:
  


Current Time: Fri Mar 29 00:57:36 CDT 2024