- 爱易网页
-
数据库教程
- select前往记载的顺序
日期:2013-11-27 浏览次数:20604 次
select前往记录的顺序
中兴通讯重庆研讨所 游波 吴育红
关键词:select,顺序,优化,备份,扫描,索引
文章摘要:
当我们执行了select语句,select前往的记录的顺序对我们编程方式有较大影响,对数据库记录备份清除以及sql功用优化都有很大的关系。因此有必要明确select前往记录的顺序。本文按数据库分类讨论oracle/sybase/sql server前往记录的顺序,从原理探讨三种数据库各自的特点,并着重探讨了这些差异对数据查询及记录备份的影响。
缩略语:
IAM:index allocation map
PFS:page free space
1.简介
当我们执行了select语句,select前往的记录的顺序对我们编程方式有较大影响,对数据库记录备份清除以及sql功用优化都有很大的关系。因此有必要明确select前往记录的顺序。
select前往记录的顺序与数据库类型有很大关系,因此以下按数据库类型分别讨论。本文次要讨论了oracle/sybase/sql server前往记录的顺序,从原理探讨三种数据库各自的特点,并着重探讨了这些差异对数据查询及记录备份的影响。
2. oracle
以下假设数据库查询优化方式均为基于rule的方式,ORACLE 采用两种访问表中记录的方式:
a. 全表扫描 (Full Table Scan)
全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。
b. 通过ROWID访问表
你可以采用基于ROWID的访问方式情况,提高访问表的效率,ROWID包含了表中记录的物理位置信息。ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系。通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到功用上的提高。通常表现为按索引扫描。(Index Scan)
2.1全表扫描
如果select语句不能使用索引,则Oracle按全表扫描方式读取数据块,对于前往的结果集,oracle按rowid的大小顺序来前往记录。因此 select * from mytable 与 select * from mytable order by rowid效果是一样的
可以通过select rowid from table得到rowid伪列,数据类型为ROWID类型。使用查询语句前往的是ROWID的扩展格式(Extended Rowid)。扩展格式的ROWID由18个字符组成。这18个字符可以按照OOOOOO.FFF.BBBBBB.SSS的格式分为4组。分别代表数据对象编号(Data Object Number),数据文件编号(Datafile Number),数据块编号(Data Block Number),记录或记录片断的块内行号。
必须说明的是,并不是后插入记录的rowid就越大,有可能后插入的记录rowid还要小。下面给出两个论点加以证明:
1.后插入的记录块内行号可能大,也可能小
依据我们的试验,假设如今表中有三条记录假设文件号相反,按块号,行号陈列如下:
108 0
108 1
108 2
删除两头一条记录后,得到
108 0
108 2
再添加一条记录,可能会得到
108 0
108 1 <---新添加的记录
108 2
也可能是
108 0
108 2
108 3 <---新添加的记录
两种情况均有可能出现,取决于oracle块内的分配算法。关于该情况的更深入的分析可以参见文献2。
2.后插入的记录的块号有可能大,有可能小
插入记录的块号并不是线性添加的,而是受FreeList控制。有关FreeList的理论和算法可以参见文献1。
因此对于全表扫描可以得出以下结论:
1. 在oracle中 select * from mytable不能保证前往的记录顺序是按插入的先后顺序,而是按rowid顺序。
rowid的顺序与记录行存储的“物理序”分歧。在没有索引情况下,select作全表扫描,是按“物理序”,此时select 前往记录按“物理序”最快。
2. 对于曾经插入的记录其ROWID不会发生变化。
如果全表扫描方式下,直接使用rownum作为选择条件,依据结论1,两次得到的记录可能是不一样的。如果sql有时间条件或其他条件作为sql语句辅助的筛选(排出当前插入的值),那么再用rownum作为选择条件,则前往的记录及记录的顺序均是一样的。
结论2的特性可用于某些日志表的清除-备份机制中。对于某些日志表为了提高insert功用,可能没有索引,并且在存储过程中对这些日志表进行清除和备份。利用insert into select 先将部分记录选入到备份表中,再用delete语句删除日志表中的记录。通过rownum来控制操作的行数,避免回滚段问题,通过时间条件来实施结论2,保证记录分歧。
2.2按索引扫描
对于一段范围的按索引选择,在oracle内部表现为索引叶节点的扫描,索引叶节点通常曾经排序并且叶节点之间存在指针,便于扫描。由于此时select按索引扫描表,因此前往的记录就按“索引序”陈列。
利用上述特征,对于按索引扫描可以有以下的使用方式:
1.通过索引可以使前往记录事先排序。
在oracle中使用索引就可以使前往的记录得到排序,而无需再使用order by。对于不同的排序方式可以用不同的索引完成,通过hint/*+*/指示可以控制索引按不同的扫描方式任务,从而达到不同的效果。如/*+INDEX(TABLE INDEX_NAME)*/或/*+INDEX_DESC(TABLE INDEX_NAME)*/指示按索引升序扫描或按索引降序扫描,从而实现前往的记录按字段的升序陈列或按字段的降序陈列。
例如对于表T(a int,b int)在a上有索引index_a,b上有索引b
则select * from t得到的记录
A
B
19
43
21
1
3
10
5
8
11
2
select /*+INDEX(T INDEX_A)*/* from t where a>0 或者
select * from t where a>0 order by a
A
B
3
10
5
8
11
2
19
43
21
1
从执行计划来看,按索引扫描和按索引ROWID方式访问。
select /*+INDEX_DESC(T INDEX_B)*/* from t where b>0 或者
select * from t where b>0 order by b
A
B
21
1
11
2
5
8
3
10
19
43