文档章节

oracle database query Optimizer 查询优化

Oscarfff
 Oscarfff
发布于 2016/06/13 17:54
字数 761
阅读 36
收藏 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
粉丝 72
博文 815
码字总数 96913
作品 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
使用智能优化器提高Oracle的性能极限

消耗在准备新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分。但是通过理解Oracle内部产生执行计划的机制,你能够控制Oracle花费在评估连接顺序的时间数量,并且能在大体上提高...

晨曦之光
2012/03/09
0
0
Oracle 常用命令笔记(1)

命令 说明 crsctl start has 启动数据库HAS服务。 crsctl stat res -t 检查ASM中的各服务(DG、监听、ASM实例等)状态。 srvctl status asm 查看ASM实例状态,正常情况下主备机的ASM实例都应...

abcwayne
2017/12/22
0
0
ORACLE优化器Optimizer_mode参数

Oracle使用Optimizermode参数来控制优化器的偏好,9i常用的几个参数有:firstrows,allrows,firstrows_N,rule,choose等。 而10g少了rule和choose。 optimizer_mode =choose 这个是Oracle的默...

娲城小将
2014/11/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Java Web--增删改查之二界面后台java代码(转载参考)

/** *  *//** * @author Administrator * */package dao; import java.sql.*;public class DBConn {/** * 链接数据库 * @return */  ...

小橙子的曼曼
11分钟前
0
0
Redis源码阅读笔记-对象及其类型和编码

总结之《Redis设计与实现》 对象 Redis中是使用对象来便是数据库中的键和值。 结构 // server.h...#define LRU_BITS 24...typedef struct redisObject { unsigned type:4; ...

Jian_Ming
24分钟前
0
0
laravel框架常用目录路径

laravel框架常用目录路径 app_path()app_path函数返回app目录的绝对路径:$path = app_path();你还可以使用app_path函数为相对于app目录的给定文件生成绝对路径:$path = app_p...

高处胜寒
25分钟前
0
0
记一次winserver2003系统,https无法访问,内存占用持续增加,解决办法

先交代一下环境: win server2003系统,系统装在hyper-v虚拟机里 大概2016年底的镜像,距离今天两年左右 病症:大概9月10号左右用这个镜像还可以访问https,但是今天用这个镜像新装的系统,就...

阳阳露
41分钟前
3
0
Vue学习资料

一直以为Vue是依赖nodejs的。 作为前端也可以耦合性就很低了。 //npm包管理器 进行管理npm install vue//初始化一个项目vue init//本地调试npm run dev//编译完成 ...

大灰狼wow
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部