Oracle Database 11gR2 Performance Tuning Cookbook

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 ...