Optimizing Storage Structures - Oracle Database 11gR2 Performance Tuning - page 108

SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_YEAR_OF_BIRTH,
CUST_EMAIL FROM CUSTOMERS
WHERE UPPER(CUST_LAST_NAME) = 'WADE';
SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_YEAR_OF_BIRTH,
CUST_EMAIL FROM CUSTOMERS WHERE CUST_FIRST_NAME = 'DARBY';
SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_YEAR_OF_BIRTH,
CUST_EMAIL FROM CUSTOMERS WHERE CUST_FIRST_NAME = 'Darby';
SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_YEAR_OF_BIRTH,
CUST_EMAIL FROM CUSTOMERS
WHERE UPPER(CUST_FIRST_NAME) = 'DARBY';

1. Connect to SH schema:

CONNECT sh@TESTDB/sh

2. Execute the TEST CASE:
3. In the following screenshot, we can see only the results of the first query in the test
case; however, the results are also the same for other queries in the test case:

4. Create the index IX1_CUSTOMERS :

CREATE INDEX IX1_CUSTOMERS ON
CUSTOMERS (CUST_LAST_NAME, CUST_FIRST_NAME);