Optimizer hits query in oracle database

 optinmizor hits query chapter 7 performance book please check it any issue

===============================================================================

1. Connect the database to the SH schema:

CONNECT sh@TESTDB/sh

2. Set the auto-trace functionality in SQL*Plus to see only the execution plan without executing the queries:

SET AUTOT TRACE EXP

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