1.ORACLE高效SQL

 

 

1. 高效SQL所需的知识

1. 有关所查询内容的物理组织的知识
2. 数据库能够做什么的知识
3. SQL的所有错综复杂的知识
4. 对目标的真实理解

 

1. 访问路径

1. 全扫描
2. 各种类型的索引访问
3. 通过散列或ROWIN方式的直接访问

 

1. 1. 全扫描

全扫描是读取ORACLE的大量数据的行之有效的方法，因为数据库将使用多块读取。

       由于ORACLE知道打算读取该段中的每一块，因此将以此性读取多个块，而非一次一块。

       对于大数据量的操作，全扫描比使用索引高效，尽管这一思路似乎与传统知识相违背。

 

       如果需要尽快的得到结果集中的第一行数据，全扫描和大数据量操作是应该避免的。

1. 1. 1. 全扫描和多块读取计算

ORACLE通过使用多块IO来完成全扫描。在ORACLE在一个IO调用中一次读取N块。通过10046跟踪时间和观察等待事件可以看到多块读取。

通过ORACLE的DB_FILE_MULTIBLOCK_READ_COUNT设置，试验不同的值，可以看到性能的影响。当然每次使用需要清洗高速缓存以加强物理IO。可以使用alter tablepsace 的表空间简单的脱机/联机。

存储过程如下：

SQL> create or replace procedure

Full_scan_big_table(p_dbmrc in number)

As

       L_cnt number;

Begin

       Execute immediate

       'alter tablespace users offline';

       Execute immediate

       'alter tablespace users online';

       Execute immediate

       'alter session set

       Db_file_multiblock_read_count='||p_dbmrc;

       Execute immediate

       'alter session set events

       ''10046 trace name context forever,level 12''';

 

       Execute immediate

       'select /*+ FULL(bt_mbrc_' || p_dbmrc || ') */ count(*)

       From big_table bt_mbrc_' || p_dbmrc

       Into l_cnt;

       Execute immediate

       'alter session set events

       ''10046 trace name context off''';

End;

/

然后使用如下代码块：

SQL>begin

For i in 0..9

Loop

       Full_scan_big_table( power(2,i));

End loop;

End;

/

运行TKPROF 后，收到关于DB_FILE_MULTIBLOCK_READ_COUNT值为16的报告。

报告所在路径：

$ORACLE_BASE/diag/rdbms/hammer/hammer/trace/

从1变为2 的时候，时间直接缩小一半了。

并非越多越好。

执行如下查看不同db_file_multiblock_read_count下的CBO计算成本

Set autotrace traceonly explain

Alter session set db_file_multiblock_read_count =1;

Select /*+ FULL(b) */ count(*) from big_table b;

Alter session set db_file_multiblock_read_count =2;

Select /*+ FULL(b) */ count(*) from big_table b;

Alter session set db_file_multiblock_read_count =4;

Select /*+ FULL(b) */ count(*) from big_table b;

Alter session set db_file_multiblock_read_count =8;

Select /*+ FULL(b) */ count(*) from big_table b;

Alter session set db_file_multiblock_read_count =16;

Select /*+ FULL(b) */ count(*) from big_table b;

Alter session set db_file_multiblock_read_count =32;

Select /*+ FULL(b) */ count(*) from big_table b;

Alter session set db_file_multiblock_read_count =64;

Select /*+ FULL(b) */ count(*) from big_table b;

Alter session set db_file_multiblock_read_count =128;

Select /*+ FULL(b) */ count(*) from big_table b;

Alter session set db_file_multiblock_read_count =256;

Select /*+ FULL(b) */ count(*) from big_table b;

Alter session set db_file_multiblock_read_count =512;

Select /*+ FULL(b) */ count(*) from big_table b;

 

DB_FILE_MULTIBLOCK_READ_COUNT	COST
1	34850
2	20396
4	13118
8	9478
16	7660
32	6750
64	6294
128	6068
256	5954
512	5896
1024	5868
4096	5846
10240	5846

 

1. 1. 1. 全扫描和高水位标记

全表扫描要注意，全表扫描始终读取一个段的所有 低于高水位标记 的块。包含数据的任何块都将进行全扫描。

小例子：创建表，扫描表，观察。删除此表中的大多数行并再次运行之。然后，重建此表并观察各结果。使用scott用户。

create table t as select * from all_objects;

set autotrace on

select count(*) from t;

SQL>set autotrace off;

delete from t where owner <> ‘SCOTT’;

Set autotrace on

Select count(*) from t;

       在高水位标记下的每个块（在某一点上包含数据的全部块）都必须检查。块中也许有数据，也许没有数据，但在检查之后就不会了。

执行如下：

SQL>set autotrace off

Alter table t move;

Set autotrace on

Select count(*) from t;

该程序删除了没有数据的块，只剩下低于高水位标记的块。

看consistent gets 只有10 了。

定期删除全扫描的表中的大量信息，可以使用其他的方法而非简单的DELETE（该命令不降低高水位标记）来完成清除：

       使用分区。可以仅截取或删除带有数据的分区来做清楚。这些操作都降低高水位标记或删除它。

       使用CREATE TABLE TAMP AS SELECT <data to keep > ,后跟 drop old_trable  和 rename temp ；复制要保留的数据而不是删除被清除的数据。

 

1. 1. ROWID

ORACLE中ROWID是数据的物理地址。一个ROWID包含关于文件、块和该块中的行的一行信息。

ROWID对于获取一个特定的行来说可能是最快的方法，但是使用ROWID获取成千上万行并不是最好的方法。

       ROWID用于访问经过某种排序索引扫描后的表。

例如：

Select * from table where indexed_column=value;

将扫描基于索引列的索引、检索到行所指向的索引，然后通过ROWID访问此表，执行特定文件块的读取。

1. 使用ROWID提高检索速度
2. ROWID范围

ROWID访问的另一个用途是ROWID范围。

可以把一个表的单一全扫描分解成了许多ROWID范围扫描。

 

1. 1. 索引扫描

例如：

SQL>select * from emp where empno=1234;

将键值1234转换成了一个ROWID，然后用ROWID直接访问表中的行。

1. 1. 1. B*树结构

B* 树索引是数据库中最常见的一种索引结构。ORACLE 花费的数据搜索时间最小化。

最低级的块（叶结点）包含了每个索引键值和一个指向正在索引的行的ROWID。叶节点上的内部块称为枝块，驾驭着该结构。

       B * 树的特性之一是所有叶块都应该在树的同级上。该级也称为索引的高度。

意思是叶节点上面的所有节点都唯一指向低级的、更为具体的节点。叶节点中的条目指向具体的ROWID。

大多数B*树索引将有2或3的高度，平均对应上百万个记录。意味着将花费2或3个IO来查找索引的主键。

       B*树是一个优秀的、通用的索引机制，该索引对于大、小表都很适用，并且几乎不会因基本表尺寸的增长而削弱性能。

       有两个经常发生的主索引扫描：索引唯一扫描和索引范围扫描。还有另一些类型的索引访问计划：索引跳跃扫描、索引全扫描和快速全扫描，以及索引连接。

1. 1. 1. 索引唯一扫描

索引唯一扫描（Index-unique scans）.优化程序知道在索引中索引列是唯一的，从索引查找中至多返回一行。

       实际上，在B*树中没有非唯一索引这样的事情。在非唯一索引中，ORACLE仅仅将该行的ROWID添加到索引键中使其非唯一。在非唯一索引中，将会发现数据是按索引键值然后按ROWID进行存储，而在唯一索引中，数据只按索引键值存储。

1. 1. 1. 索引范围扫描

对于索引范围扫描，不能保证索引查找只返回一行。

       索引范围扫描可按两个搜索方向之一执行。

       此外，ORACLE不仅能够按照键查找数据或检索存储的数据，也可以查找最小和最大值。

      

1. 1. 1. 索引跳跃扫描

索引的前面列很少有不同的值，会选择跳跃索引。

1. 1. 1. 索引全扫描

索引全扫描（Index Full Scans） 不读取索引结构中的每一块。与全扫描的知识相背。

处理索引的所有叶块，但是查找到第一个叶块需要处理足够多的分支块。

       索引全扫描使用单块IO按顺序读取索引。

       索引全扫描的特性之一是，由于处于索引结构中，将从索引中按排序顺序读取数据。索引全扫描可以避免排序。

 

1. 1. 1. 索引快速全扫描

索引快速全扫描（Index Fast-Full Scans） 与索引全扫描有明显不同。

索引快速全扫描有以下特征：

1. 1. 读取索引结构中的每个块，包括所有内部分支块
   2. 采用多块读取，就像表的全扫描一样
   3. 不按排序顺序检索数据

索引快速全扫描将索引等同于表的一个缩小版本。一次读取索引数据N块，处理叶块的数据，并忽略分支块。能够比索引全扫描更快地读取索引结构，因为它使用了多块IO。

       在查询只引用索引列并且表的全扫描可以由快速全扫描该索引来避免时，将使用这种方法。索引快速全扫描不能替代排序，因为数据肯定不会从这种索引扫描中排序返回。

 

1. 1. 1. 索引链接

索引链接（Index Join）是在表中存在多个索引时针对某个查询所选中的索引路径，该索引中应该包含了查询中选中的全部列（不需要对表进行访问）。

 

1. 1. 群扫描

ORACLE有两种群对象

1. B*树群：

有一个单群键索引，用来将群键抓换成一个数据库块的地址。从该块开始并通过包含该键值数据的块列表，将查找到群中的带有该键值的所有行。

1. 散列群

没有辅索引，而是散列键本身被散列排序，指向一个数据库块的地址。从该块开始并通过包含键值的数据的块列表，将查找到该群中散列到该键值上的所有行。

 

1. 链接概念
   1. 嵌套循环

嵌套循环联结是数据库的主连接方法，是最常见的连接技术。

例如：

SQL>select ename,dname from emp,dept where emp.deptno = dept.deptno;

此连接技术擅长于从结果中迅速获得第一行。

嵌套循环连接技术通常不适用于需快速获得结果集的最后一个记录的情况。

       由于数据的纯卷积，使用嵌套循环连接是极为低效的。如果目标将要运行此查询且只获取前10行，则嵌套循环方法很有用。如果想要读取后10行数据，则将花费大量的时间和逻辑IO。

 

1. 1. 散列链接
      1. 散列固有连接

在最佳条件下，ORACLE将取两个表中较小的一个。将此散列表在私有内存中，访问此数据结构不会招致一般逻辑IO的栓锁活动。

1. 1. 1. 散列外部连接

外部表用于建立散列表，内部表用来建立查找散列表。

       散列链接擅长用于连接两个大的结果集或一大一小两个结果集。读取第一行比较慢，因为在返回第一行之前，其中一个数据源必须散列到内存中，或者在内存和磁盘中。但是，读取其他数据行的性能，尤其是散列表在内存中时，极好。

 

1. 1. 排序合并联结

排序合并连接（Sort-Merge Join） 与嵌套循环连接和散列连接不同。没有驱动表的概念。

       通常不如散列连接高效。通常连个输入集都需要扫描和排序。与散列连接不同，在散列连接中数据输出前只有一个输入集被处理。通常，排序-合并连接在非相等连接操作中有用。

 

1. 1. 笛卡尔连接

笛卡尔连接也称为笛卡尔积。笛卡尔连接名声不好，但是对于某些查询，则是解决问题的唯一方法。

 

1. 1. 反连接

反连接而用于从一个表中返回不在另一个数据源中的数据行。

许多人认为应该避免NOT IN 查询，因为存在实际的性能问题。但是 反连接和CBO，NOT IN查询可能很搞笑。

 

1. 1. 全外部连接

全外部连接返回TABLE_A的每一行以及TABLE_B中的每一行。