Databases Reference
In-Depth Information
3.
Select some records from the
CUSTOMERS
table (say, all customers born in 1949):
SELECT
C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM sh.CUSTOMERS C
WHERE C.CUST_YEAR_OF_BIRTH = 1949;
4.
Execute the same query suggesting that it's better to full-scan the
CUSTOMERS
table
in order to answer the same query we just saw:
SELECT /*+ FULL(C) */
C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM sh.CUSTOMERS C
WHERE C.CUST_YEAR_OF_BIRTH = 1949;
5. Inform the optimizer that we are only interested in the first 10 rows of the result:
SELECT /*+ FIRST_ROWS(10) */
C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM sh.CUSTOMERS C
WHERE C.CUST_YEAR_OF_BIRTH = 1949;
6.
Tell the optimizer to use indexes on the
CUSTOMERS
table:
SELECT /*+ INDEX(C) */
C.CUST_FIRST_NAME, C.CUST_LAST_NAME
FROM sh.CUSTOMERS C
WHERE C.CUST_YEAR_OF_BIRTH = 1949;
7.
Disable the auto-trace functionality:
SET AUTOT OFF
How it works...
In this recipe, there is a query to extract the customers who were born in 1949; we want to
retrieve their first and last names.
