2.24.4 Examples of SQL optimization specification

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