Examples of using an SQL optimization specification are given as follows:
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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')
- 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')
- A used index SQL optimization specification is specified by enclosing it in /*>> and <<*/.
SELECT PNAME FROM STOCK /*>> WITH INDEX (IDX1) <<*/
WHERE PRICE <= 500
- 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
- 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')
- 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
- 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