03 访问和联结方法(未完成, 先暂停)

优化器必须确定如何来访问你的SQL语句所需要的数据, 你构造SQL语句, 而优化器则在硬解析的过程中算出哪些运算能够尽可能高效地提供数据. 主要是根据统计信息. 你对优化器可能会考虑的不同访问和联结方法了解的越多, 就越能够构造出恰当的SQL语句来帮助优化器做出最好的选择. 并且当优化器所选择的运算不能满足你的性能需求时, 你可以更准确的来确定哪些运算更合适提供你想要的响应时间.

在SQL语句表达式和条件均评估完毕, 以及所有可能有助于更准确地生成执行计划的查询转换都进行完以后, 生成执行计划的下一个步骤就是利用优化器来确定访问数据的最好方法. 一般来说, 总共只有两种基本数据访问途径: 全扫描或索引扫描, 全扫描(可能是全表扫描, 也可能是全索引扫描)过程中, 多个块被读入到一个IO运算中, 索引扫描首先扫描索引叶子块以取得特定的行id (rowid), 然后利用这些id来访问附表取得实际数据. 这些访问都是通过单块读取来完成的. (oracle 操作的最小单位是 1 block, 所以物理上1 block 中的数据会全部被提取到内存中) 如果在数据表访问的步骤之后还需要进一步的数据筛选, 这些行将会被包含在最终结果集之前通过筛选.

与典型的统计信息一起, 优化器将利用你的查询(你写的sql语句) 来算出你需要多少数据(选择比)以及哪种访问方法能够尽可能快的提供数据.

全扫描访问方法

当对一个对象进行全扫描时, 与该对象相关的所有数据库都必须取出并进行处理, 以确定块中所包含的数据行是否是你的查询所需要的. (oracle 必须一整个block, 一整个block的读取磁盘到内存中, 这里的block是指 oracle 的逻辑的 block) 因此, 当全扫描发生时, 优化器(还有你)需要考虑两件事:

1. 必须读取多少个数据块, 每个数据块中有多少数据将被舍弃.

    当一个查询需要返回表中绝大多数的数据行时, 选择使用全扫描的可能性当然也是很高的. 另外, 即使查询仅需要返回很小比例的数据行时, 使用全扫描也是恰当的, 同时, 即便是查询绝大多数行, 也未必一定使用全扫描. 例如:

   1:  create table t1 as

   2:  select trunc((rownum - 1) / 100) id,

   3:          rpad(rownum, 100) t_pad

   4:    from dba_source

   5:   where rownum <= 10000;

   6:   

   7:   create index t1_idx1 on t1(id);

   8:   

   9:   -- 更新统计信息

  10:   exec dbms_stats.gather_table_stats(user, 't1', method_opt=>'FOR ALL COLUMNS SIZE 1', cascade=>TRUE);

  11:   

  12:  create table t2 as

  13:  select mod(rownum, 100) id,

  14:          rpad(rownum, 100) t_pad

  15:    from dba_source

  16:   where rownum <= 10000;

  17:   

  18:   create index t2_idx1 on t2(id);   

  19:   

  20:   exec dbms_stats.gather_table_stats(user, 't2', method_opt=>'FOR ALL COLUMNS SIZE 1', cascade=>TRUE);

每个表都有10000 行数据, 从内容上看, 这两个表 一样, 但是 t1 的 id 列是通过表达式 trunc((rownum-1)/100), 而 t2的 id 使用 mod(rownum, 100), 假设表存储如下图:

首先预估一下你要查询的行数, 本例是 100 行

   1:   select count(*) ct from t1 where id = 1;

   2:   select count(*) ct from t2 where id = 1;

假设我们要找 id = 1 的值, 我们知道是100行, 而全表有10000行, 选择比是 100/10000 = 1%, 我们知道, 这个选择比很小, 优化器应该使用索引来得到数据, 因为我们已经在id列创建了索引, 但是此时, 我们应该了解一下数据是如何存储的, 如果数据时顺序存储的, 大多数的 id=1的行在物理上仅存储于几个数据块中, 如本例中t1那样, 那么这个结论是对的.(使用索引)

为什么优化器没有为这两个查询选择同样的解释计划呢 ? 这是因为在这两张表中数据的存储方式不同, 对于表 t1 的查询 oracle 仅需要访问很少的几个数据块来取得查询所需的 100行数据, 因此, 索引就是最具吸引力的选择, 但是, 由于表 t2 的数据行在物理上零散存储在该表的所有数据块中, 对于它的查询就需要读取几乎所有数据块来获取同样的 100 行数据. 优化器计算出使用索引来读取表中的每一个数据块的时间可能比直接使用全表扫描读取所有数据块要长.

SLA(service level agreement): 服务水平协定

全扫描要扫描到高水位, 即便是后面的块没有存储数据, 也要扫描.

   1:  -- List totla blocks

   2:   select blocks from user_segments where segment_name = 'T2';  -- 注意这里T2一定要大写

   3:   

   4:   -- list how many blocks contain data

   5:   select count(distinct(dbms_rowid.rowid_block_number(rowid))) block_ct from t2;

   6:   

   7:   -- List the lowest and highest block numbers for this table

   8:   select min(dbms_rowid.rowid_block_number(rowid)) min_blk,

   9:          max(dbms_rowid.rowid_block_number(rowid)) max_blk from t2;

  10:     

  11:   -- check the space usage in the table, space_usage.sql

  12:   declare

  13:      l_tabname        varchar2(30) := '&1';

  14:      l_fs1_bytes        number;

  15:      l_fs2_bytes        number;

  16:      l_fs3_bytes        number;

  17:      l_fs4_bytes        number;

  18:      

  19:      l_fs1_blocks    number;

  20:      l_fs2_blocks    number;

  21:      l_fs3_blocks    number;

  22:      l_fs4_blocks    number;

  23:      

  24:      l_full_bytes    number;

  25:      l_full_blocks    number;

  26:      

  27:      l_unformatted_bytes        number;

  28:      l_unformatted_blocks    number;

  29:  begin

  30:      dbms_space.space_usage(

  31:          segment_owner        => user,

  32:          segment_name        => l_tablename,

  33:          segment_type        => 'TABLE'，

  34:          fs1_bytes            => l_fs1_bytes,

  35:          fs1_blocks            => l_fs1_blocks,

  36:          fs2_bytes            => l_fs2_bytes,

  37:          fs2_blocks            => l_fs2_blocks,

  38:          fs3_bytes            => l_fs3_bytes,

  39:          fs3_blocks            => l_fs3_blocks,

  40:          fs4_bytes            => l_fs4_bytes,

  41:          fs4_blocks            => l_fs4_blocks,

  42:          full_bytes            => l_full_bytes,

  43:          full_blocks            => l_full_blocks,

  44:          unformatted_blocks    => l_unformatted_blocks,

  45:          unformatted_bytes    => l_unformatted_bytes

  46:      );

  47:      dbms_output.put_line('0-25% Free     = ' || l_fs1_blocks || ' Bytes = ' || l_fs1_bytes);

  48:      dbms_output.put_line('25-50% Free     = ' || l_fs2_blocks || ' Bytes = ' || l_fs2_bytes);

  49:      dbms_output.put_line('50-75% Free     = ' || l_fs3_blocks || ' Bytes = ' || l_fs3_bytes);

  50:      dbms_output.put_line('75-100% Free     = ' || l_fs4_blocks || ' Bytes = ' || l_fs4_bytes);

  51:  end;

  52:  /

  53:      

注意, 书中例子执行后, 得到结果和实验不同, 这个实验没有做完, 主要想证明, 当你 delete from t2 时, 高水位保持不变, 同时你进行全扫描时, 也非常耗时, 因为全扫描是要扫描的高水位的, truncate table t2, 这个操作可以降低高水位, 从而降低全扫描的时间.

 

索引扫描访问方法

默认的索引类型是 B- 树索引. 索引建立在表中的一个活多个列或者列的表达式上, 将列值和行编号(rowid) 一起存储. rowid 物理表中行数据的内部地址, 包含两个地址, 其一为指向数据表中包含该行的块所存放数据文件的地址, 另一个是可以直接定位到数据行自身的这一行在数据块中的地址.

   1:  column filen format a50 head 'File Name'

   2:   

   3:  select e.rowid,

   4:         (select file_name

   5:            from dba_data_files

   6:           where file_id = dbms_rowid.rowid_to_absolute_fno(e.rowid, user, 'EMPLOYEES')) filen,

   7:         dbms_rowid.rowid_block_number(e.rowid) block_no,

   8:         dbms_rowid.rowid_row_number(e.rowid) row_no

   9:    from hr.employees e

  10:   where e.last_name = 'Jones';

可以看到 block No. 和 row No.  这样就能一下找到块和块内的行, 通过索引扫描来进行的数据块访问时通过单块读取来完成的. 一旦读取了索引条目, 就只会取出行编号所指定的那一个块数据, 它被取出后(因为oracle每次I/O操作的最小单位是1个block), 然后就仅有行编号所指定的行被访问.

这也就是说对于通过索引扫描所获取的每一行, 都至少需要访问两个数据块, 至少一个索引块和一个数据块. 如果你最终的结果集有100行, 而这100行数据时通过索引扫描取得的, 则至少需要访问200块数据块. 之所以说”至少”是因为根据索引的大小, oracle有可能首先必须访问多个索引块才能获得所需要的第一个符合条件的列值.

索引结构

首先索引是树型结构, 包含一个或多个层次的分支块和一个层次的叶子块, 分支块保存着下一层分支所包含的值的范围信息, 它被用来在索引结构中进行搜索, 以获得所需的叶子块. 叶子分支包含排序后的索引对象的值和行编号.

如果你创建一张空表, 并在其上创建索引, 索引将会包含一个空块. 在这种情况下, 这个唯一的块即是根数据块又是叶子数据块. 索引高度:1

随着新行加入表中, 新的索引条目也会增加到块中, 直到新的条目再也加不进去为止. 此时 oracle 就会分配两个新的索引块并将所有索引条目加入这两个新的叶子块中. 之前被填满的那个单独的根数据块现在就替代为指向两个新数据块的指针. 这时, 这个根就会记录两个块的最低索引值, 并记录这两个块的物理地址.索引高度:2

随着时间的推进, 更多的行插入到表中, 索引条目被加入刚才创建的两个叶子数据块中, 当这两个叶子块也被填满之后, oracle将会增加一个新的叶子块(这与根节点不一样, 只分配了一个新的叶子块)并为它分配介于已填满的块与新叶子块之间的索引条目. 每次一个叶子数据块填满并分裂之后, 就会为这个心的叶子块增加一个新指针到根数据块中.

最后, 跟数据块也被填满了, 然后再重复将数据块分裂为两个新的分支块(注意, 这里生成的是两个新的分支块), 索引高度:3
现在继续加入新的索引条目, 叶子数据块将会被填满并分裂, 但不是添加到一个新指针到根数据块中, 而是将指针加入到响应的分支数据块中, 最终, 分支数据块也将被填满并分裂, 这时候又将有一个新的索引条目加入到根数据块中. 随着这些过程不断积蓄, 最后根数据块有被填满并分裂, 再一次增加索引高度.

请记住唯一引起索引高度增加的就是当根数据块分裂的时候.

由于上边的特点, 所有叶子数据块到根数据块的距离都是一样的. 这也就是为什么你会听到用”平衡”来形容 oracle 的 B-树索引. 

特点, 每次数据来, 都是找到正确的位置, 必须放到叶子节点中.

B- 树生成步骤总结:

0) 空表, 空数据块

1) 最开始, 根节点, 最开始的根节点是个叶子节点, 放数据, 当这个根节点放满了. 这个根节点就会生成两个新的块来存放数据, 同时这个根节点将变成 branch 节点, 存储这两个新增节点的指针和每个块最低索引值. (只有根节点分裂时, 索引树的高度才增加), 新来的数据插入这两个新的叶子节点中

2) 随着数据增加, 叶子节点装满了, 那么分配了 1 个新的块来继续装数据, 并将这个块的信息保存在根节点中. 反复执行….

3) 当根节点都被装满了, 这时候根节点分裂, 分裂出两个branch, 根节点保存这两个 branch 信息. (注意这以后, 根节点每次分裂, 都会生成的是branch的块, 而不是装实际数据的块), 这两个branch 记录了叶子节点的块的指针及最小索引值区域.

4) 数据继续增加, 装满, 又新增加叶子节点, 注意此时是针对某个branch的叶子节点, 所以某个branch要保存新增加块的指针和最小索引.

5) 随着数据增加, 某个branch 满了, 此时就会再分配一个新的branch, 并且在根节点中增加这个新的branch的信息

6) 继续增加, 根节点满了, 根节点分裂, 分裂出两个 branch, 同时索引树的高度增加1.

7) 如此反复

索引扫描类型

很多扫描类型, 但是它们有个共同点就是它们都必须遍历索引结构以访问匹配所搜索值的叶子索引快. 首先, 索引的根块将通过一次单块读取来访问, 接下来读取分支, 一层一层, 根据索引树的高度不同, 可能需要读多层分支, 但是每次读取都是针对一个独立的数据块, 最后, 读取到了包含所需的起始索引条目的第一个叶子索引块. 如果索引高度是4, 为了得到所需叶子块, 将会对 4 个单独的块进行读取. 此时就会读取叶子块中第一个匹配的索引值的行编号(rowid), 并利用这个行编号来进行一次独立的块读取以获取整个行数据所储存的表数据块.因此如果读取一行数据, 最少要 5 个块(刚说的索引树高度是4), 4个索引块和1个数据块(通过rowid 读取)

补充: 重要的索引统计信息-聚簇因子(clustering factor). 这个因子帮助优化器生成使用索引的成本信息, 并且是表中建立了索引的数据排序优良度的一个度量值.

回忆一下: 索引条目是按顺序存储的而表中的数据时随机存储的. 除非特意去指定特定的顺序将数据载入到表中(例如索引表), 否则将不能保证某一行将会被放在什么地方.

索引的聚簇因子向优化器表明了具有同样索引值的数据行是不是存放在同一个或连续的一系列的数据块中, 或者数据行是否被分散存放在表的多个数据块中.

在表示T1表的图中, 你可以看到包含值 2 的数据行是如何被载入到同一个数据块中的. 但是对于表T2, 值为 2 的行并不是载入到连续的数据块中, 在这个例子中, 建立在T1表中这一列上的索引将会具有较低的聚簇因子, 而表T2的聚簇因子更高.

   1:  select t.table_name || '.' || i.index_name idx_name,

   2:         i.clustering_factor, t.blocks, t.num_rows

   3:    from user_indexes i, user_tables t

   4:   where i.table_name = t.table_name

   5:     and t.table_name in ('T1', 'T2')

   6:   order by t.table_name, i.index_name;

优化器将会在查询表T1的时候选择索引扫描而在查询表T2的时候选用全表扫描. 聚簇因子是帮助优化器做出决定的关键信息.