Databases Reference
In-Depth Information
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;
