Avoiding sorting in set operations: union, minus, and intersect

In this recipe, we will investigate performance-related issues when using set operations, such as UNION, INTERSECT, and MINUS.

Getting ready

We will use the SH schema and a copy of the EMPLOYEES table from the HR schema to do our test. To create the MY_EMPLOYEES table in the SH schema, we will use the following script:

CONNECT / AS SYSDBA
CREATE TABLE sh.MY_EMPLOYEES AS SELECT * FROM hr.EMPLOYEES;

How to do it...

The following steps will demonstrate how to avoid sorting:

1. Connect to the SH schema and enable tracing:

   CONNECT sh@TESTDB/sh
   SET AUTOT TRACE EXP STAT
   

2. Execute a query using the UNION operator to show the customers with a credit limit higher than 13000 and the employees with a salary greater than 10000:

   SELECT
     CUST_LAST_NAME AS LastName, CUST_FIRST_NAME AS FirstName
    FROM sh.CUSTOMERS
    WHERE CUST_CREDIT_LIMIT > 13000
   UNION
   SELECT
     LAST_NAME, FIRST_NAME
    FROM sh.MY_EMPLOYEES
    WHERE SALARY > 10000;
   

3. Execute the same...