| Efficient Way - Outer join Vs Not Exists [message #593727] |
Tue, 20 August 2013 06:39  |
 |
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 #593786 is a reply to message #593770] |
Tue, 20 August 2013 23:01  |
 |
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
