oracle database query Optimizer 查询优化
博客专区 > Oscarfff 的博客 > 博客详情
oracle database query Optimizer 查询优化
Oscarfff 发表于2年前
oracle database query Optimizer 查询优化
  • 发表于 2年前
  • 阅读 36
  • 收藏 0
  • 点赞 0
  • 评论 0

新睿云服务器60天免费使用,快来体验!>>>   

多表连接查询优化:

LEADING Hint

Description of leading_hint.gif follows
Description of the illustration leading_hint.gif
 

(See "Specifying a Query Block in a Hint"tablespec::=)

The 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;

The 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 LEADING hints.

其它参考参考博文

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

from a 


 )
 where Idnum between 101 and 120

建议 between 范围要大,否则效率低。

 

The 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.

Note:

The 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 DELETE and 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.

first_rows 用在有排序或者分组的查询语句中,不是最优的选择。

 

ALL_ROWS Hint

Description of all_rows_hint.gif follows
Description of the illustration all_rows_hint.gif
 

The 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 theDBMS_STATS package to gather statistics.

If you specify hints for access paths or join operations along with either the ALL_ROWS or FIRST_ROWS hint, then the optimizer gives precedence to the access paths and join operations specified by the hints.

 

MERGE Hint

强调同时进行,如果有子查询的时候用。

Description of merge_hint.gif follows
Description of the illustration merge_hint.gif
 

(See "Specifying a Query Block in a Hint"tablespec::=)

The 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.

For example:

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;

When the 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.

  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 70
博文 802
码字总数 96913
×
Oscarfff
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: