文档章节

oracle database query Optimizer 查询优化

Oscarfff
 Oscarfff
发布于 2016/06/13 17:54
字数 761
阅读 38
收藏 0

多表连接查询优化:

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.

本文转载自:http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50705

共有 人打赏支持
Oscarfff
粉丝 73
博文 815
码字总数 96913
作品 0
崇明
后端工程师
私信 提问
资料整理——Oracle基本概念、术语(Glossary from Oracle Concepts)——第一部分

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/hpdlzu80100/article/details/84839113 资料来源: https://docs.oracle.com/database/121/CNCPT/toc.htm 中文...

预见未来to50
12/05
0
0
资料整理——Oracle基本概念、术语(Glossary from Oracle Concepts)——第十一部分

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/hpdlzu80100/article/details/84874328 “吾生也有涯,而知也无涯”,我觉得一个理想的知行合一模型是:理论学...

预见未来to50
12/07
0
0
资料整理——Oracle基本概念、术语(Glossary from Oracle Concepts)——第十四部分

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/hpdlzu80100/article/details/84879743 “区(extent)又叫盘区,是数据文件中一个连续的分配空间,它比块要大...

预见未来to50
12/07
0
0
SQL Server 2016新特性:数据库级别配置

新的 ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) 用来配置数据库级别配置。 这个语句可以配置每个数据库的配置: 清理过程cache 设置MAXDOP参数,可以配置primary和secondary 设置...

技术小胖子
2017/11/08
0
0
RBO和CBO的基本概念

Oracle数据库中的优化器又叫查询优化器(Query Optimizer)。它是SQL分析和执行的优化工具,它负责生成、制定SQL的执行计划。Oracle的优化器有两种,基于规则的优化器(RBO)与基于代价的优化...

mrliuze
2016/01/26
16
0

没有更多内容

加载失败,请刷新页面

加载更多

老男孩 - python函数编程day2

mark

以谁为师
14分钟前
0
0
【58沈剑 架构师之路】缓存,究竟是淘汰,还是修改?

允许cache miss的场景,不管是memcache还是redis,当被缓存的内容变化时,是修改缓存,还是淘汰缓存?这是今天将要讨论的话题。 问:KV缓存都缓存了一些什么数据? 答: (1)朴素类型的数据...

张锦飞
16分钟前
0
0
Spring异常之Druid – unregister mbean error set JAVA_OPTS="-Ddruid.registerToSysProperty=true"

Spring异常之Druid – unregister mbean error 2017年04月19日 12:13:42 Dr.Zhu 阅读数:6688 版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zt_fucker/arti...

linjin200
17分钟前
0
0
数据结构-图-知识点总结

一、基本术语 图(graph):图是由顶点的有穷非空集合和顶点之间边的集合组成,通常表示为:G(V,E),其中,G表示一个图,V是图G中的顶点的集合,E是图G中边的集合。 顶点(Vertex):图中的数据...

hblt-j
20分钟前
0
0
SAP订单编排和流程增强概述

SAP产品里的订单处理,无论是On-Premises解决方案还是云产品,我认为归根到底可以概括成四个字:订单编排,包含两个层次的内容: 1. 单个订单通过业务流程或者工作流驱动的状态迁移; 2. 多种...

JerryWang_SAP
27分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部