Complex Boolean Logic (UNION Operator)

The techniques presented in this lesson will improve the performance of both cost-based and rule-based SQL queries. It illustrates the need to carefully check queries to avoid full-table scans. Consider the following query for an online system:

SELECT
 student_name
FROM 
 STUDENT
WHERE
 (student_nbr = :hostvar_student 
 OR 
 :hostvar_student IS NULL)
 AND
 (social_security_nbr = :hostvar_social 
 OR 
 :hostvar_social IS NULL);

This query accepts host variables which are prefixed with a colon (:).

Host variables are very common in online systems where the SQL must be executed to query based on the input from the end user. In this case, we query by student_nbr or by social_security_nbr, depending upon which of the values is supplied by the end user. This is very common in online systems, where a screen may allow the end user to enter either the social security number or the student number. Because of that, the query has an index on both social security number and student number.

From a programming perspective, this SQL is very elegant. Unfortunately, this query does not run at optimum efficiency.
So what do we expect to see from the EXPLAIN PLAN? Since we have an index on both columns, we would expect to see an index range scan. Instead, there is a full-table scan.

TABLE ACCESS FULL STUDENT

So, why the full-table scan? Here we see two Boolean conditions that are joined by the AND command. Within each Boolean, we test for two conditions, joined by an OR operator. As it turns out, the Oracle optimizer cannot resolve this complex Boolean expression.
Whenever the optimizer gets confused, it invokes a full-table scan.
Note: This is a very important point about SQL tuning. The “elegance” of an SQL statement often has an adverse impact on the performance of the query. Many SQL developers are taught to write Boolean expressions in the most compact form, and not for optimal performance.
The solution

SELECT
   student_name
FROM 
   STUDENT
WHERE
   student_nbr = :hostvar_student 
   AND 
   :hostvar_student IS NOT NULL
   AND
   :hostvar_social  IS     NULL
UNION
SELECT
   student_name
FROM 
   STUDENT
WHERE
   social_security_nbr = :hostvar_social
   AND
   :hostvar_social  IS NOT NULL 
   AND 
   :hostvar_student IS NULL
UNION
SELECT
   student_name
FROM 
   STUDENT
WHERE
   student_nbr = :hostvar_student 
   AND
   social_security_nbr = :hostvar_social
   AND
   :hostvar_social  IS NOT NULL 
   AND 
   :hostvar_student IS NOT NULL
UNION
SELECT
   student_name
FROM 
   STUDENT
WHERE
   :hostvar_social  IS NULL 
   AND 
   :hostvar_student IS NULL;                             

Re-write SQL as four Separate Queries

PROJECTION
   SORT                        UNIQUE
      UNION-ALL
         FILTER
           TABLE ACCESS        BY ROWID      STUDENT
           INDEX               RANGE SCAN    STU_NBR_SOC_SEC_IDX
         FILTER
           INDEX               RANGE SCAN    SOC_SEC_STU_NBR_IDX
         TABLE ACCESS          BY ROWID      STUDENT
           INDEX               RANGE SCAN    STU_NBR_SOC_SEC_IDX
         FILTER
           TABLE ACCESS        FULL          STUDENT               

Re-writing Query