Avoiding Outer Joins

Solution 1:

Oracle “Normal” joins are used to retrieve records from both the tables which satisfy join conditions. But sometimes we need records from the table which satisfy the join condition and also those records which are not present in another table. In this situation we go for Outer Joins.

If the tables being joined are having millions of records then using outer joins on such tables takes lots of time for execution. Even though the query is returning 10 or 100 records, the execution time taken by a query will be huge.

Please have a look at a simple query mentioned below. The query provides department names for, employees with ID between 1 and 100 and also those employees which are not allocated to any department.

select e.ID,e.NAME, d.NAME

from dept d, emp e

where d.id (+) = e.DEPT_ID

and e.id between 1 AND 100

In the above example we need only one column, department name, from DEPT table. But for this we need to perform a costly outer join on DEPT table. In such situations it is possible to avoid outer joins. Let’s have a look at the example given below,

select e.ID,e.NAME,(select NAME from dept d where d.id = e.DEPT_ID)

from emp e

where e.id between 1 AND 100

In this solution we are removing the DEPT table from the FROM clause of main query. The DEPT table is used as a sub query to retrieve values for department name column. This sub query will be executed only 100 times. If the DEPT (ID) column is indexed then the query on DEPT table will be faster.

Above solution gives positive results when,

1. Only one column is being selected from the huge INNER table
2. The query is joining tables with millions of records
3. Query return very few records as an output

Solution 2:

In case of normalized database we need to join multiple tables and some times we need to use outer joins. These queries take lots of time for retrieving 10 or 100 records.

Please see the example mentioned below. The query is trying to retrieve all the transaction between dates 01-Jan-2009 and 10-Jan-2009. For each demat transaction the query provides details for Beneficiary account, Depository participant account and settlement account involved in the transaction. The demat transaction details are stored in the DEMAT_TRANSACTION table and demat account details are stored in DEMAT_ACCOUNT table. DEMAT_ACCOUNT represents multiple accounts involved in a transaction and stores one record per account.

SELECT demat_trn.transaction_id,

benacct.party_name "Beneficiary Account",

dpacct.party_name "Depository Participant Party",

settleacct.part_name "Settlement Account"

FROM demat_transaction demat_trn,

demat_account benacct,

demat_account dpacct,

demat_account settleacct

WHERE demat_trn.transaction_id = benacct.transaction_id(+)

AND demat_trn.transaction_id = dpacct.transaction_id(+)

AND demat_trn.transaction_id = settleacct.transaction_id(+)

AND demat_transaction.transaction_date BETWEEN

TO_DATE ('01012009', 'DDMMYYYY')

AND TO_DATE ('10012009', 'DDMMYYYY')

In this example we can avoid outer joins by denormalizing the DEMAT_TRANSACTION and DEMAT_ACCOUNT tables. If there are maximum 3 accounts involved in the demat transaction, then the DEMAT_TRANSACTION table should be altered to accommodate details for all the three accounts for each transaction. After denormalization the final query will look like as mentioned below,

SELECT demat_trn.transaction_id,

benacct.party_name "Beneficiary Account",

dpacct.party_name "Depository Participant Party",

settleacct.part_name "Settlement Account"

FROM demat_transaction demat_trn

WHERE demat_trn.transaction_date BETWEEN

TO_DATE ('01012009', 'DDMMYYYY')

AND TO_DATE ('10012009', 'DDMMYYYY')

Since the denormalized query is fetching records from only one table, hence this query will definitely be executed faster than the normalized query.

Conclusion:

Outer joins are very costly when the tables being joined are very big and having millions of records. Depending on queries output it is possible to easily avoid the outer joins using Sub queries or denormalizing the tables involved in the Join.