select返回记录的顺序对我们编程方式有较大影响,因此有必要明确select返回记录的顺序。
select返回记录的顺序与数据库类型有很大关系,与索引情况也有很大关系,以下分类讨论。
1.在oracle中
(1)如果select 后面的where条件中不含索引,oracle按rowid的大小顺序来返回记录。
因此 select * from mytable 与 select * from mytable order by rowid效果是一样的
必须说明的是,并不是后插入记录的rowid就越大。极有可能后插入的记录rowid还要小。
因为在oracle中rowid由对象号,文件号,块,行号(块内的行号)构成,
假设现在表中有三条记录假设文件号相同,按块号,行号排列如下:
108 0
108 1
108 2
删除中间一条记录后,得到
108 0
108 2
再增加一条记录,可能会得到
108 0
108 1 <—新增加的记录
108 2
也可能是
108 0
108 2
108 3 <—新增加的记录
两种情况均有可能出现,取决于oracle当时分配算法。
因此可以得出结论,在oracle中 select * from mytable不能保证返回的记录顺序是按插入的先后顺序。
是按rowid顺序返回的,rowid的顺序与记录行存储的"物理序"一致,核心就是"物理序"。
在没有索引情况下,select作全表扫描,是按"物理序",此时select 返回记录按"物理序"最快。
(2)对于where后面有索引条件的时候
由于此时select按索引扫描表,因此返回记录就按"索引序",所以在oracle中使用索引就可以
使返回的记录得到排序,而无需再使用order by。对于不同的排序方式可以用不同的索引完成。
即时where后面没有索引条件,也可按/+INDEX(TABLE INDEX_NAME)/或/+INDEX_DESC(TABLE INDEX_NAME)/
强制使用索引以完成对表的排序。
2.在sybase中
(1)对于sybase 执行计划没有带索引的表的select
不管你的select 语句中是否在where后面使用了索引,sybase均可能基于代价对索引的使用
进行调整。
select * from mytable 按记录的"物理序"返回记录。
对于不带簇索引的堆表,"物理序"就是插入记录的先后顺序。
对于带簇索引的表,"物理序"就是簇索引字段的排序。
(2)对于sybase 执行计划带索引的表的select
按索引字段的顺序返回记录。
总结:
由于sybase基于代价执行计划会对索引的使用进行调整,因此不能像oracle那样利用
非聚簇索引完成返回记录的自然排序,这时最好加上order by以保证排序的准确。
如果需要排序的字段是聚簇索引,那么就可以放心使用索引完成排序。这时,不论
执行计划怎样,sybase均按聚簇索引字段顺序返回记录。
对于不带任何索引的裸表,也可以放心的使用select 完成自然排序,此时记录按插入
的先后顺序返回,这是sybase的一个优点(当然是牺牲了空间和效率换来的),oracle无法做到。
对于堆表,sybase在空间管理中,记录始终向数据页链表的最后一页添加,即使中间页存在空间
也不会被使用到。以保证插入顺序与物理循序的一致性。
对于聚簇索引表,在插入数据时,会引起页内部分记录(值大的记录)的移动,通过移动
sybase保证了数据的物理顺序与聚簇索引顺序一致。
3.在Ms Sql Server中
(1)对于Sql Server 执行计划没有带索引的表的select
不管你的select 语句中是否在where后面使用了索引,Sql Server均可能基于代价对索引的使用
进行调整。即使没有where语句也有可能使用索引,即使有where语句也有可能不用索引。
对于Sql Server 执行计划没有带索引的表的select
select * from mytable 按记录的"物理序"返回记录。
SQL Server空间分配是根据sysindexes中指出的表的索引分配映射表 (IAM) 和文件中的页可用空间表(PFS)
来找到对象中可用的空闲空间。SQL Server随机的(考虑平衡使用文件,并优先使用未用完的扩展区)
将记录插入到空闲页中。在堆表中,即使删除了记录,该记录所在页不会作页内移动。在簇索引表中,
删除了记录后,会按"影子记录"方式对记录进行页内移动。
对于执行计划没有索引的记录的扫描,记录的"物理序"就是IAM中使用的页的顺序,因此后插入的记录可能先
扫描到。尽管记录的存储是随机的,但是对于一个静态的表,连续两次select返回的记录的顺序是一致的,
都是按"物理序"。对于一个动态插入的表,如果select中给出了时间条件排出了当前插入的记录,
则连续两次返回的记录的个数和顺序也是一致的。
(2)对于Sql Server 执行计划带索引的表的select
按索引字段的顺序返回记录。
(3)表的备份
基于3个前提:
不管你的select 语句中是否在where后面使用了索引,Sql Server均可能基于代价对索引的使用
进行调整。即使没有where语句也有可能使用索引,即使有where语句也有可能不用索引。
不管你的delete 语句中是否在where后面使用了索引,Sql Server均可能基于代价对索引的使用
进行调整。即使没有where语句也有可能使用索引,即使有where语句也有可能不用索引。
带相同where语句的select 和 delete 执行计划很可能不一样。
因此select 和 delete 得到的记录顺序很可能不一致,如果要选取前n条记录,那么得到的记录
集尽管条数一致但内容不一致。(尽管我们可以通过with(index(index_name))来强制select对索引
的使用,但delete却不能够强制指定索引,因为delete涉及对索引本身的删除)
这种情况下,如果数据库的性能够好,要备份的数据不多,就不要使用set rowcount来控制条数。
但如果确需要控制一次删除的条数,可以直接在where条件中控制更小的范围,如时间范围控制
到小时,一天的数据通过24小时的循环来备份。
要么采用DTS作备份。