BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

The Semi-Join

Oracle Tips by Burleson Consulting

A semi-join is an operation where the EXISTS clause is used with a subquery. It is called a semi-join because even if duplicate rows are returned in the subquery, only one set of matching values in the outer query is returned. In the case of the EXISTS clause, the subquery is executed, but even if the subquery returned multiple rows, the semi-join will not duplicate the value referenced in the outer query, as show in Figure 16-5.

Figure 5: A semi-join

This is an example of a correlated subquery because the EXISTS references the outer table in the where clause. Here we display the names of all departments who have employees who earned more than a $5,000 commission. This is a semi-join because even though the subquery may return many rows for each employee with more than $5,000 in commissions, only one department name will be displayed.

select /*+ first_rows */
   dname
from
   dept
where
   exists
   (select
      *
    from
      emp
    where
      dept.deptno = emp.deptno
    and
      emp.comm > 5000
   )
;

When you examine this query, it is clear that even though there may be many employees in each department with $5,000 commissions, only one match is required to display the department name. Of course, if we have an index on the comm column, then a semi-join would not be necessary, because the query could filter all emp rows where comm > 5000 and then probe into the dept table for the names of employees in those departments. If there is no index on the comm column in emp, then a semi-join can be used to improve query performance.

As you may remember from previous chapters, the CBO always invokes a NESTED LOOPS table access method for all queries with EXISTS clauses when an index is available on both columns (comm and deptno in this example). However, since we have no index on comm, we see the following semi-join plan.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     1
  FILTER
                                                                     1
    TABLE ACCESS
FULL                           DEPT                                  1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
      INDEX
RANGE SCAN                     EMP_DEPTNO                            1

This makes sense for a semi-join because the query only has the deptno index available, and the entire join set must be filtered for those rows where comm is greater than $5,000. Next, let’s see how the inefficient nature of semi-joins makes them candidates for replacement with equi-joins using the select distinct clause.

Alternative Representation for Semi-Joins

If you examined the query carefully, you may notice that it could be rewritten as a standard equi-join. The trick is to eliminate the duplicate rows in the department name, and we can do this in a standard join by specifying the select distinct clause. As you may remember from previous chapters, the distinct clause invokes a sort to eliminate the duplicate rows from the result set. Here is the rewritten query with an equi-join:

select distinct /*+ first_rows */
   dname
from
   dept,
   emp
where
   dept.deptno = emp.deptno
and
   emp.comm > 5000
;

Now, look carefully at the new execution plan where there is no index on the comm column. Here wee see a hash join, with full-table scans being executed against both tables. The presence of the full-table scans indicates that this may not be the fastest execution plan if these are large tables.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     6
  SORT
UNIQUE                                                               1
    HASH JOIN
                                                                     1
      TABLE ACCESS
FULL                           EMP                                   1
      TABLE ACCESS
FULL                           DEPT                                  2

This execution plan should raise immediate suspicion because the query is serviced by performing a full-table scan against the emp table, and then performing another full-table scan on the dept table.

With an index on the comm column, we see a very different execution plan because full-table scans are no longer required.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     6
  SORT
UNIQUE                                                               1
    HASH JOIN
                                                                     1
      TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
        INDEX
RANGE SCAN                     EMP_COMM                              1
      TABLE ACCESS
FULL                           DEPT                                  2

Here you see the HASH JOIN table access method, with the department table acting as the driving table.

Tip: You can usually avoid a semi-join by ensuring that a column index exists on all relevant predicates in the where clauses of both the outer and inner queries. Also, you can often replace a subquery with the exists clause, replacing it with a standard equi-join using the select distinct clause.

Next, let’s move away from the theoretical join types and take a look at Oracle implementation of table joining.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter