LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. This hint is more versatile than the
ORDERED hint. For example:
SELECT /*+ LEADING(e j) */ * FROM employees e, departments d, job_history j WHERE e.department_id = d.department_id AND e.hire_date = j.start_date;
LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting
LEADING hints, then all of them are ignored. If you specify the
ORDERED hint, it overrides all
FIRST_ROWS(n): This hint instructs the optimizer to select a plan that returns the first n rows most efficiently. 强调返回速度。
SELECT /*+ FIRST_ROWS(10) */ empno, ename FROM emp WHERE deptno = 10;
select /*+ FIRST_ROWS(100) */ * from (
select /*+ LEADING(c b) */ rownum as Idnum,b.login
where Idnum between 101 and 120
建议 between 范围要大，否则效率低。
FIRST_ROWS hint instructs Oracle to optimize an individual SQL statement for fast response, choosing the plan that returns the first
n rows most efficiently. For
integer, specify the number of rows to return.
FIRST_ROWS hint specified without an argument, which optimizes for the best plan to return the first single row, is retained for backward compatibility and plan stability only.
For example, the optimizer uses the query optimization approach to optimize the following statement for best response time:
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id FROM employees WHERE department_id = 20;
In this example each department contains many employees. The user wants the first 10 employees of department 20 to be displayed as quickly as possible.
The optimizer ignores this hint in
UPDATE statement blocks and in
SELECT statement blocks that include any blocking operations, such as sorts or groupings. Such statements cannot be optimized for best response time, because Oracle Database must retrieve all rows accessed by the statement before returning the first row. If you specify this hint in any such statement, then the database optimizes for best throughput.
ALL_ROWS hint instructs the optimizer to optimize a statement block with a goal of best throughput—that is, minimum total resource consumption. For example, the optimizer uses the query optimization approach to optimize this statement for best throughput:
SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id FROM employees WHERE employee_id = 7566;
If you specify either the
ALL_ROWS or the
FIRST_ROWS hint in a SQL statement, and if the data dictionary does not have statistics about tables accessed by the statement, then the optimizer uses default statistical values, such as allocated storage for such tables, to estimate the missing statistics and to subsequently choose an execution plan. These estimates might not be as accurate as those gathered by the
DBMS_STATS package, so you should use the
DBMS_STATS package to gather statistics.
If you specify hints for access paths or join operations along with either the
FIRST_ROWS hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.
MERGE hint lets you merge views in a query.
If a view's query block contains a
GROUP BY clause or
DISTINCT operator in the
SELECT list, then the optimizer can merge the view into the accessing statement only if complex view merging is enabled. Complex merging can also be used to merge an
IN subquery into the accessing statement if the subquery is uncorrelated.
SELECT /*+ MERGE(v) */ e1.last_name, e1.salary, v.avg_salary FROM employees e1, (SELECT department_id, avg(salary) avg_salary FROM employees e2 GROUP BY department_id) v WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;
MERGE hint is used without an argument, it should be placed in the view query block. When
MERGE is used with the view name as an argument, it should be placed in the surrounding query.