Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Joins Oracle Tips by Burleson

A join occurs when a SELECT statement references more than one table, view, or snapshot in the FROM clause. Joins do take some processing power to handle, but a well-written join typically doesn’t drag down the performance of the database. There are three types of joins that can be used: Cartesian products, simple joins, and outer joins.

Cartesian Products

A Cartesian product occurs when you fail to specify a join condition for the tables in the WHERE clause of your SELECT statement. Listing 2.7 displays a query that causes a Cartesian product.

Listing 2.7 A query that causes a Cartesian product.

SELECT S.last_name, S.first_name, S.gpa,
       DP.degree_plan_description,
       SFA.total_financial_aid
FROM   STUDENTS S,
       DEGREE_PLANS DP,
       STUDENT_FINANCIAL_AID SFA;

If the STUDENTS table contains 10,000 rows, DEGREE_PLANS contains 120 rows, and STUDENT_FINANCIAL_AID contains 9,000 rows, the Cartesian product generated by the query will contain 10,000 × 9,000 × 120 (10,800,000,000) rows! A result set this large is rarely useful and extremely detrimental to the performance of a database.

Simple Joins

A simple join returns all rows that match one or more specified conditions between tables. The columns compared in the join conditions do not have to have the same name, but should have similar datatypes. Listing 2.8 shows a query using a simple join.

Listing 2.8 A query using a simple join.

SELECT S.last_name, S.first_name, S.gpa,
       DP.degree_plan_description,
       SFA.total_financial_aid
FROM   STUDENTS S,
       DEGREE_PLANS DP,
       STUDENT_FINANCIAL_AID SFA
WHERE  S.financing_num = SFA.financing_num
AND    S.degree_plan = DP.degree_plan
AND    SFA.total_aid >= 1000.00
AND    S.gpa > 3.0;

This query will return a result set containing all students who have declared degree plans, have earned at least $1,000 in financial aid, and have a grade point average greater than 3.0. If a student has not declared a degree plan, no row will be returned for that student.

Outer Joins

An outer join instructs the database to return a row from one table, even if no corresponding row is found in another table. Listing 2.9 uses an outer join to get the names and grade point averages of students who have not declared a degree plan.

Listing 2.9 A query using an outer join.

SELECT S.last_name, S.first_name, S.gpa,
       DP.degree_plan_description,
       SFA.total_financial_aid
FROM   STUDENTS S,
       DEGREE_PLANS DP,
       STUDENT_FINANCIAL_AID SFA
WHERE  S.financing_num = SFA.financing_num
AND    S.degree_plan = DP.degree_plan (+)
AND    S.gpa > 3.0;

This query will return the names of students who have not declared a degree plan as well as those students who have declared a degree plan.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.