Examples of using an SQL optimization specification are given as follows:
- In a SELECT statement, specify an SQL optimization specification for a used index. For this process, use an index (IDX1) for retrieving a stock table (STOCK):
SELECT PNAME FROM STOCK WITH INDEX (IDX1)
WHERE PRICE <= 500
- In a SELECT statement, specify an SQL optimization specification for a used index. For this process, use multiple indexes (IDX1, IDX2) 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. For this process, for the definition of a stock table (STOCK), use an index that was defined by specifying the PRIMARY KEY option:
SELECT PNAME FROM STOCK WITH INDEX (PRIMARY KEY)
WHERE PRICE <= 500
- In a SELECT statement, specify an SQL optimization specification for a used index. During this process, suppress the use of an index (table scanning) for the retrieval of the stock table (STOCK):
SELECT PNAME FROM STOCK WITHOUT INDEX
WHERE PRICE <= 500
- In a SELECT statement, specify a join method SQL optimization specification. For this process, make the inner join join method into nest-loop-join or distributed nest-loop-join.
SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
FROM STOCK INNER JOIN BY NEST ORDER
ON STOCK.PCODE = ORDER.PCODE
- In a SELECT statement, specify a join method SQL optimization specification. For this process, make the outer join join method into hash-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, specify a join method SQL optimization specification. In this process, make the inner join (with INNER omitted) join method into merge-join.
SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
FROM STOCK JOIN BY MERGE ORDER
ON STOCK.PCODE = ORDER.PCODE
- In a SELECT statement, specify a subquery execution method SQL optimization specification. In this process, make the subquery execution method into hash execution.
SELECT PNAME FROM STOCK
WHERE PCODE =ANY
(HASH SELECT PCODE FROM ORDER
WHERE CCODE = '302S')
- In a SELECT statement, specify a subquery execution method SQL optimization specification. In this process, make the subquery execution method into non-hash execution (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')
- Specify a used index SQL optimization specification by enclosing it in /*>> and <<*/.
SELECT PNAME FROM STOCK /*>> WITH INDEX (IDX1) <<*/
WHERE PRICE <= 500
- Specify a join method SQL optimization specification by enclosing it in /*>> and <<*/.
SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
FROM STOCK INNER JOIN /*>> BY NEST <<*/ ORDER
ON STOCK.PCODE = ORDER.PCODE
- Specify a subquery execution method SQL optimization specification by enclosing it in /*>> and <<*/.
SELECT PNAME FROM STOCK
WHERE PCODE =ANY
(/*>> HASH <<*/ SELECT PCODE FROM ORDER
WHERE CCODE = '302S')
- In a SELECT statement, specify a join method SQL optimization specification and a used index SQL optimization specification. In this process, use an index (IDX3) for retrieving a stock table (STOCK) and make the inner join (with INNER omitted) join method into hash-join.
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, specify a join method SQL optimization specification and a used index SQL optimization specification by enclosing each in /*>> and <<*/. In this process, use an index (IDX3) for retrieving a stock table (STOCK) and make the inner join (with INNER omitted) join method into hash-join.
SELECT STOCK.PCODE,STOCK.PNAME,ORDER.CCODE
FROM STOCK /*>> WITH INDEX (IDX3) <<*/ JOIN /*>> BY HASH <<*/ ORDER
ON STOCK.PCODE = ORDER.PCODE