图片 1

Oracle中的伪列<三>

前言

[TOC]

ROWID: 表中的每一行在数据文件中都有一个物理地址,ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行,ROWID值可以唯一的标识表中的一行,由于ROWID返回的是该行的物理地址,因此使用ROWID可以显示行是如何存储的。

通常,当我们将SQL语句提交给Oracle数据库时,Oracle会选择一种最优方式来执行,这是通过查询优化器Query
Optimizer来实现的。CBO是Oracle默认使用的查询优化器模式。在CBO中,SQL执行计划的生成,是以一种寻找成本最优为目标导向的执行计划探索过程。所谓成本就是将CPU和IO消耗整合起来的量化指标,每一个执行计划的成本就是经过优化器内部公式估算出的数字值。

在看《基于Oracle的SQL优化一书》知道了很多专业名称,做了记录,CBO、优化器、查询转换、执行计划、Hint、并行、游标、绑定变量、统计信息、直方图、索引等等。这篇博客可以说是读书笔记

SELECT ROWID, ename FROM emp WHERE sal >
2000;

我们在写SQL语句的时候,经常会碰到where子句后面有多个条件的情况,也就是根据多列的条件筛选得到数据。默认情况下,oracle会把多列的选择率相乘从而得到where语句的选择率,这样有可能造成选择率不准确,从而导致优化器做出错误的判断。为了能够让优化器做出准确的判断,从而生成准确的执行计划,oracle在11g数据库中引入了收集多列统计信息。本文通过对测试表的多条件查询,介绍收集多列统计信息的重要性。

1.1 优化器简介

优化器(Optimizer):优化器是Oracle数据库内置的一个核心子系统,负责解析SQL,Oracle优化器是Oracle系统的一个核心组件,其目的是按照一定的原则来获取目标SQL在当前情形下执行的最高效执行路径,也可以说是执行计划。

 

一、环境准备

1.2 SQL执行过程

Oracle SQL的执行过程:

图片 1在这里插入图片描述

ROWNUM: 在查询的结果集中,ROWNUM为结果集中每一行标识一个行号,第一行返回1,第二行返回2,以此类推。通过ROWNUM伪列可以限制查询结果集中返回的行数。

我们在Oracle 11g中进行试验。

2.1 优化器的优化方式

Oracle优化器按照优化方式分为两种

  • 基于规则的优化器(Rule-Based Optimizer),简称RBO
  • 基于成本的优化器(Cost-Based Optimizer),简称CBO

SELECT ROWNUM, ename, job, sal FROM emp WHERE
ROWNUM <= 5; // 取前5名员工的信息

SQL> SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE 11.2.0.3.0 ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL>

2.2 基于规则的优化器

基于规则的优化器(Rule-Based
Optimizer):所谓基于规则的优化器是指Oracle按照硬编码在数据库的一系列规则来决定SQL的执行计划,简称是RBO

RBO在oracle10后官方就不建议用,因为RBO并不支持oracle一些性能比较好的功能特性,也不会根据表的数据量等等获取执行计划,而且RBO定的那些规则也不是很容易修改。不过代码在oracle10后版本还是保存的,所以要学习测试也可以用SQL开启RBO模式

alter session set optimizer_mode='RULE';

RBO的执行过程:对于一条sql,oracle会事先给sql各种情况的执行计划定一个等级,一共有15个等级,从等级1到等级15,规则是等级越低执行效率越高,也就是等级1的执行计划执行效率是最高的。然后oracle自然就选出等级1的执行路径作为执行计划。

  • 对于执行路径一样的情况:假如出现执行路径一样的情况,这时候就要根据数据字典缓存来确定最低的等级了,意思就是获取缓存中的先后顺序确定哪条作为执行计划

上面说了可以通过SQL开启CBO模式,这是针对普通情况的,假如出现下面情况,那就是强制使用CBO

  • SQL涉及对象有IOT(Index Organized Table)
  • SQL涉及的对象分区表
  • 使用了并行查询或者并行DML
  • 使用了星型连接
  • 使用了哈希连接
  • 使用了索引快速全扫描
  • 使用了函数索引
  • ….

这些情况总结来自《基于Oracle的SQL优化一书》

虽然Oracle针对上述情况都开启了强制CBO,但是我们还是可以手动解决的,方法也是来自《基于Oracle的SQL优化一书》,作者提供了改写等价sql的方法,比如在sql的where条件中对number或者date类型的列加0,

select * from 表格 where a+0>参数

如果是varchar2类型的,加可以加个空字符串

select * from 表格 where a || '' = 参数

SELECT ROWNUM, T.* FROM

在hr用户下创建测试表hoegh,重复插入数据,数据量相当于16个employees表。

2.3 基于成本的优化器

介绍一下基于成本的优化器(Cost-Based
Optimization):基于成本的优化器简称是CBO,在SQL执行过程,会缓存执行的一些信息到Oracle的数据字典里,这里的信息就有sql执行路径的I/O、网络资源、CPU的使用情况,其实这个就是SQL的执行成本,也是按照这个成本来确定执行计划。所以CBO概念就是根据I/O、网络资源、CPU的使用情况来确定SQL执行路径也可以说是执行计划的优化器。

集的势(Cardinality)是CBO特有的概念,集的势指结果集的行数。引入这个概念是为了表示SQL执行成本值,Cardinality越大,也就是说sql执行返回的结果集所包含的行数就越多,也说明成本越大。

可选择率(Selectivity):指施加指定谓语条件后返回结果集的记录数占未施加任何谓语条件的原始结果集的记录数的比率。可选择率的范围是0~1,它的值越小,说明可选择性越好,值越大说明可选择性越差,也就是成本值越大。可选择率为1时性能是最差的。

可选择率 =
施加指定谓语条件后返回结果集的记录数/未施加任何谓语条件的原始结果集的记录数。

(SELECT ename, job, sal FROM emp ORDER BY sal
DESC) T

SQL> SQL> conn hr/hrConnected.SQL> SQL> create table hoegh as select * from employees;Table created.SQL> select count from hoegh; COUNT---------- 107SQL> SQL> insert into hoegh select * from hoegh;107 rows created.SQL> /214 rows created.SQL> /428 rows created.SQL> /856 rows created.SQL> commit;Commit complete.SQL> select count from hoegh; COUNT---------- 1712SQL>

3.1 优化器优化模式分类

优化器优化模式分为Rule、Choose、First rows、All rows

  • Rule:就是基于规则Rule的方式
  • Choose:当一个表或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。这是Oracle的默认方式
  • First
    rows:与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。
  • All rows:其实就是基于Cost方式

WHERE ROWNUM <= 5); // 取工资最高的前5名

二、按照常规方法收集统计量信息;

3.2 优化模式使用方法

要修改优化模式可以使用类似SQL

alter session set optimizer_mode='RULE';

SELECT * FROM