In this recipe, we will investigate performance-related issues when using set operations, such as UNION, INTERSECT, and MINUS.
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;
The following steps will demonstrate how to avoid sorting:
Connect to the
SHschema and enable tracing:CONNECT sh@TESTDB/sh SET AUTOT TRACE EXP STAT
Execute a query using the
UNIONoperator 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;
Execute the same...