SQL Optimizer Hints

Reading Time: 1 minutes

SQL Optimizer Hints - Quick tips and description on frequently used SQL Hints:

/*+ ALL_ROWS */: The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
/*+ HASH(table_name) */: The HASH hint explicitly chooses a hash scan to access the specified table. It applies only to tables stored in a cluster.
/*+ PARALLEL(table_name degree) */: The PARALLEL hint lets you specify the desired number of concurrent servers that can be used for a parallel operation. The hint applies to the INSERT, UPDATE, and DELETE portions of a statement as well as to the table scan portion.
/*+ PARALLEL_INDEX(table_name, index_name, degree, RAC degree) */: The PARALLEL_INDEX hint specifies the desired number of concurrent servers that can be used to parallelize index range scans for partitioned indexes.
/*+ USE_HASH(table_name) */: The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join.
/*+ LEADING(table_name) */: The LEADING hint causes Oracle to use the specified table as the first table in the join order.
/*+ FIRST_ROWS(integer) */: The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).
/*+ ORDERED */: The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause.