SQL Reference - OPTION

OPTION

OPTION is a keyword in SQL used to optimize the behavior of a query. It allows for specific instructions to the Query Optimizer about how to implement a query, therefore influencing the choice of the query execution plan. OPTION is written after the execution of main SQL statement. Common uses of OPTION includes limiting the query cost, specifying a join strategy, or controlling the resources used in query execution.

Example

SELECT ProductID, Name
FROM Production.Product
ORDER BY ListPrice DESC
OPTION (MAXDOP 1);

Output

ProductID | Name
--------- | -------------
748       | Road-150 Red, 62
749       | Road-150 Red, 44
750       | Road-150 Red, 48
751       | Road-150 Red, 52
752       | Road-150 Red, 56
753       | Road-150 Red, 60

Explanation

The OPTION clause is used to optimize the performance of your SQL Server query. In this example, the MAXDOP 1 option ensures that the query uses only one processor for its execution, potentially improving performance for a system with multiple processors. The query selects ProductID and Name from the Production.Product table, ordered by ListPrice in descending order.

Example

SELECT * FROM employees
WHERE department_id = 10
OPTION (FULL JOIN)

Output

EMPLOYEE_ID FIRST_NAME LAST_NAME    DEPARTMENT_ID
------------------------------------------------
110        John        Doe            10
111        Jane        Smith          10

Explanation

The OPTION(FULL JOIN) hint is Oracle’s mechanism for influencing the execution plan of a statement. In the above example, the Oracle SQL engine will perform a FULL JOIN on the tables mentioned in the query. The output shows all employees who are in department 10.