Examples of using an SQL optimization specification are given as follows:

1. In a SELECT statement, an SQL optimization specification is specified for a used index. In this case, an index (IDX1) is used for retrieving a stock table (STOCK):
   

   SELECT PNAME FROM STOCK WITH INDEX (IDX1)
     WHERE PRICE <= 500

2. In a SELECT statement, an SQL optimization specification is specified for a used index. In this case, multiple indexes (IDX1, IDX2) are used for retrieving a stock table (STOCK):
   

   SELECT PNAME FROM STOCK WITH INDEX (IDX1,IDX2)
     WHERE PRICE <= 500 OR SQUANTITY > 100

3. In a SELECT statement, specify an SQL optimization specification for a used index. In this case, for the definition of a stock table (STOCK), an index that was defined by specifying the PRIMARY KEY option is used:
   

   SELECT PNAME FROM STOCK WITH INDEX (PRIMARY KEY)
     WHERE PRICE <= 500

4. In a SELECT statement, specify an SQL optimization specification for a used index. In this case, use of an index (table scanning) for the retrieval of the stock table (STOCK) is suppressed:
   

   SELECT PNAME FROM STOCK WITHOUT INDEX
     WHERE PRICE <= 500

5. In a SELECT statement, a join method SQL optimization specification is specified. In this case, a nested-loop join is used as the join method for the inner join.
   

   SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
     FROM STOCK INNER JOIN BY NEST ORDER
       ON STOCK.PCODE = ORDER.PCODE

6. In a SELECT statement, a join method SQL optimization specification is specified. In this case, a hash-join is used as the join method for the outer join.
   

   SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
     FROM STOCK LEFT OUTER JOIN BY HASH ORDER
       ON STOCK.PCODE = ORDER.PCODE

7. In a SELECT statement, a join method SQL optimization specification is specified. In this case, a merge-join is used as the join method for the inner join (with INNER omitted).
   

   SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
     FROM STOCK JOIN BY MERGE ORDER
       ON STOCK.PCODE = ORDER.PCODE

8. In a SELECT statement, a subquery execution method SQL optimization specification is specified. In this case, hash execution is used as the subquery execution method.
   

   SELECT PNAME FROM STOCK
     WHERE PCODE =ANY
       (HASH SELECT PCODE FROM ORDER
          WHERE CCODE = '302S')

9. In a SELECT statement, a subquery execution method SQL optimization specification is specified. In this case, non-hash execution is used as the subquery execution method (in this example, the option is either work table execution or work table ATS execution).
   

   SELECT PNAME FROM STOCK
     WHERE PCODE =ANY
       (NO HASH SELECT PCODE FROM ORDER
         WHERE CCODE = '302S')

10. A used index SQL optimization specification is specified by enclosing it in /*>> and <<*/.
    

    SELECT PNAME FROM STOCK /*>> WITH INDEX (IDX1) <<*/
      WHERE PRICE <= 500

11. A join method SQL optimization specification is specified by enclosing it in /*>> and <<*/.
    

    SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
      FROM STOCK INNER JOIN /*>> BY NEST <<*/ ORDER
        ON STOCK.PCODE = ORDER.PCODE

12. A subquery execution method SQL optimization specification is specified by enclosing it in /*>> and <<*/.
    

    SELECT PNAME FROM STOCK
      WHERE PCODE =ANY
        (/*>> HASH <<*/ SELECT PCODE FROM ORDER
          WHERE CCODE = '302S')

13. In a SELECT statement, a join method SQL optimization specification and a used index SQL optimization specification are specified. In this case, an index (IDX3) is used for retrieving a stock table (STOCK) and a hash-join is used as the join method for the inner join (with INNER omitted).
    

    SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
      FROM STOCK WITH INDEX (IDX3) JOIN BY HASH ORDER
        ON STOCK.PCODE = ORDER.PCODE

14. In a SELECT statement, a join method SQL optimization specification and a used index SQL optimization specification are specified by enclosing each in /*>> and <<*/. In this case, an index (IDX3) is used for retrieving a stock table (STOCK) and a hash-join is used as the join method for the inner join (with INNER omitted).
    

    SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
      FROM STOCK /*>> WITH INDEX (IDX3) <<*/ JOIN /*>> BY HASH <<*/ ORDER
        ON STOCK.PCODE = ORDER.PCODE