ASE中資料結果集分頁功能的三種實現方法

---------------------------------------------------------------------------

---- 本文為andkylee個人原創，請在尊重作者勞動成果的前提下進行轉載；

---- 轉載務必註明原始出處
:
http://blog.csdn.net/andkylee

--- 2010-05-31 21:13:58

---- 關鍵字： ASE 分頁  top  identity  set rowcount  temp table  自增列 偽劣 暫存資料表

----------------------------------------------------------------------------

 

接上篇的關於MySQL中的分頁方法，本篇簡單討論在Sybase ASE中實現資料結果分頁的方式。

本篇介紹三種方法。

 

第一種：利用遊標

程式開發人員比較喜歡使用遊標，因為遊標的“迴圈”遍曆方式類似程式設計語言中的for，while，loop語句的實現方法，寫起來比較容易。使用遊標一般步驟是：為指定的SQL語句定義一個遊標，開啟並移動遊標，當移動到指定行號的記錄行之後，再按照需要提取的行數來取資料。從表面上看解決了提取指定範圍資料的問題；但是在實際應用
上，有可能會出現嚴重的效能問題。建立遊標需要耗用一定的系統資源之外；當表內的資料量有上千萬甚至到億層級並且需要取大量的資料結果時，用遊標每移動一次就取這行資料，然後再移動遊標，這個過程將是緩慢的。在使用遊標的過程中，系統會給相應的表加上共用鎖定，導致鎖競爭而嚴重影響資料庫的效能。

 

在此不再介紹遊標的實現方式，此法比較簡單。

 

第二種：利用暫存資料表和標誌列

 

在Sybase ASE12.5.3及以後的版本中，我們可以用top關鍵字來限定只返回結果集的前N行資料。在ASE12.5.3之前的版本中只能用set rowcount N 的方法來“曲線救國”了。

對於取結果集的第N行至第N+M行資料的要求，我們考慮利用top來實現的話，比較容易想到的是：執行兩次top，再加l兩次倒序排序。

步驟如下：

　　　　　　　(1)　select top N+M * from table_name
where_clause
order by ID
    把此結果集派生為表:table_name1

                  (2)   select top M * from table_name1
order by ID  DESC
  把此結果集派生為表：table_name2

                  (3)   select * from table_name2
order by ID  DESC

上面的3條語句好像能夠實現返回第N行至第N+M行資料的要求。但是，在Sybase ASE中僅僅利用派生表而不利用暫存資料表是不能實現這個要求的。

僅僅是ASE中的“派生出派生表(derived table)的SQL語句中不能含有order by 子句”這個限制就足以使上面的方法行不通。還有一個限制是，上面的3個步驟中都利用ID列進行排序。如果表中沒有可用的排序列時，那麼上述方法也不能用了。不過幸運的是，一般要求對其結果集進行分頁的表都是有可以用作排序的列的（數字型或者日期型）。

 

繼續尋找一個能用的方法，下面著重介紹目前通用的ASE的分頁思路。此思路的關鍵是產生identity自增列和暫存資料表。

在ASE中大家要是找到了不用暫存資料表就可以實現分頁的方法請麻煩告訴我一聲。 我嘗試了很多次，都不是很理想。

 

概括起來主要語句有兩條：

           (1)   select syb=identity(10),*  into #temp_table
from table_name
where_clause
  order_by_clause

           (2)   select  * from #temp_table
where_clause
and syb >= N
  and syb <= N+M

 

用一個例子示範一下：

(1) 建立測試表:testA

create table testA(id int not null,name varchar(30) null)<br />go

 

(2) 插入測試資料

insert into testA<br />select 1,'liuzhenfu'<br />go<br />insert into testA<br />select 2,'andkylee'<br />go<br />

 

(3) 迴圈插入大量的重複資料，

insert into testA<br />select id+(select max(id) from testA),name from testA<br />go 15

向表testA迴圈插入已有的資料，15次之後，表testA內的資料達到2^16 = 65536 行。

 

(4) 利用暫存資料表 + 自增標誌列來提取第100行至第200行的資料。

語句如下：

select syb=identity(10) ,* into #tempA from testA

select * from
#tempA where syb>=100 and syb<=200

drop table #tempA

返回的結果為：

1> select syb=identity(10),* into #tempA from testA<br />2> select * from #tempA where syb>=100 and syb<=200<br />3> go<br />(65536 rows affected)<br /> syb id name<br /> ------------- ----------- ---------------------------<br /> 100 100 andkylee<br /> 101 101 liuzhenfu<br /> 102 102 andkylee<br /> 103 103 liuzhenfu<br /> 104 104 andkylee<br /> 105 105 liuzhenfu<br /> 106 106 andkylee<br /> 107 107 liuzhenfu<br /> 108 108 andkylee<br /> 109 109 liuzhenfu<br /> 110 110 andkylee<br /> 111 111 liuzhenfu<br /> 112 112 andkylee<br /> 113 113 liuzhenfu<br /> 114 114 andkylee<br /> 115 115 liuzhenfu<br /> 116 116 andkylee<br /> 117 117 liuzhenfu<br /> 118 118 andkylee<br /> 119 119 liuzhenfu<br /> 120 120 andkylee<br /> 121 121 liuzhenfu<br /> 122 122 andkylee<br /> 123 123 liuzhenfu<br /> 124 124 andkylee<br /> 125 125 liuzhenfu<br /> 126 126 andkylee<br /> 127 127 liuzhenfu<br /> 128 128 andkylee<br /> 129 129 liuzhenfu<br /> 130 130 andkylee<br /> 131 131 liuzhenfu<br /> 132 132 andkylee<br /> 133 133 liuzhenfu<br /> 134 134 andkylee<br /> 135 135 liuzhenfu<br /> 136 136 andkylee<br /> 137 137 liuzhenfu<br /> 138 138 andkylee<br /> 139 139 liuzhenfu<br /> 140 140 andkylee<br /> 141 141 liuzhenfu<br /> 142 142 andkylee<br /> 143 143 liuzhenfu<br /> 144 144 andkylee<br /> 145 145 liuzhenfu<br /> 146 146 andkylee<br /> 147 147 liuzhenfu<br /> 148 148 andkylee<br /> 149 149 liuzhenfu<br /> 150 150 andkylee<br /> 151 151 liuzhenfu<br /> 152 152 andkylee<br /> 153 153 liuzhenfu<br /> 154 154 andkylee<br /> 155 155 liuzhenfu<br /> 156 156 andkylee<br /> 157 157 liuzhenfu<br /> 158 158 andkylee<br /> 159 159 liuzhenfu<br /> 160 160 andkylee<br /> 161 161 liuzhenfu<br /> 162 162 andkylee<br /> 163 163 liuzhenfu<br /> 164 164 andkylee<br /> 165 165 liuzhenfu<br /> 166 166 andkylee<br /> 167 167 liuzhenfu<br /> 168 168 andkylee<br /> 169 169 liuzhenfu<br /> 170 170 andkylee<br /> 171 171 liuzhenfu<br /> 172 172 andkylee<br /> 173 173 liuzhenfu<br /> 174 174 andkylee<br /> 175 175 liuzhenfu<br /> 176 176 andkylee<br /> 177 177 liuzhenfu<br /> 178 178 andkylee<br /> 179 179 liuzhenfu<br /> 180 180 andkylee<br /> 181 181 liuzhenfu<br /> 182 182 andkylee<br /> 183 183 liuzhenfu<br /> 184 184 andkylee<br /> 185 185 liuzhenfu<br /> 186 186 andkylee<br /> 187 187 liuzhenfu<br /> 188 188 andkylee<br /> 189 189 liuzhenfu<br /> 190 190 andkylee<br /> 191 191 liuzhenfu<br /> 192 192 andkylee<br /> 193 193 liuzhenfu<br /> 194 194 andkylee<br /> 195 195 liuzhenfu<br /> 196 196 andkylee<br /> 197 197 liuzhenfu<br /> 198 198 andkylee<br /> 199 199 liuzhenfu<br /> 200 200 andkylee<br />(101 rows affected)

 

需要將select * from #tempA中的星號*替換為需要返回的列名。

 

繼續。。。。

 

當要求返回滿足name='andkylee'的所有行中的第100行至第200行的資料時， 利用

select syb=identity(10),* into #tempA from testA where name='andkylee'

select * from #tempA where syb>=100 and syb<=200

 

drop table #tempA

 

 

 

第三種：利用rowcount

此種方法有點不足：必須利用可用作排序的列
對結果集進行排序。

還是上面的測試表testA，如果從第9000行開始選擇10行資料，那麼語句如下：

declare @id1 int
set rowcount 9000
select @id1 = id from testA order by id
set rowcount 10
select *from testA where id >= @id1 order by id
set rowcount 0
go

 

此種方法中核心語句是select @id1=id from testA order by id ， 在對錶testA執行查詢的過程中，每讀取一行都會把id列的值賦給@id1這個變數，一直持續到最後一行，@id1這個變數反覆被下一行的id值重新整理，結果只得到最後一樣的id值。如果在此select語句之前加上rowcount的限定，那麼就可用使得@id1這個變數獲得第rowcount行的id值，那麼我們也就獲得了返回範圍結果集的起點了。

後面的 set rowcount 10

         select * from testA where id >= @id1 order by id

這兩句實際上可以用一句select top 10 * from testA where id >= @id1 order by id  來替代。 

 

這樣，兩種不同的實現形式為:

declare @id1 int
set rowcount 9000
select @id1 = id from testA  order by id
set rowcount 0
select top 10  *from testA where  id >= @id1 order by id
go

 

分別看看執行結果吧。

1> declare @id1 int<br />2> set rowcount 9000<br />3> select @id1 = id from testA order by id<br />4> set rowcount 10<br />5> select *from testA where id >= @id1 order by id<br />6> set rowcount 0<br />7> go<br />(9000 rows affected)<br /> id name<br /> ----------- ------------------------------<br /> 9000 andkylee<br /> 9001 liuzhenfu<br /> 9002 andkylee<br /> 9003 liuzhenfu<br /> 9004 andkylee<br /> 9005 liuzhenfu<br /> 9006 andkylee<br /> 9007 liuzhenfu<br /> 9008 andkylee<br /> 9009 liuzhenfu<br />(10 rows affected)<br />1>

第二種方式的結果：

1> declare @id1 int<br />2> set rowcount 9000<br />3> select @id1 = id from testA order by id<br />4> set rowcount 0<br />5> select top 10 *from testA where id >= @id1 order by id<br />6> go<br />(9000 rows affected)<br /> id name<br /> ----------- ------------------------------<br /> 9000 andkylee<br /> 9001 liuzhenfu<br /> 9002 andkylee<br /> 9003 liuzhenfu<br /> 9004 andkylee<br /> 9005 liuzhenfu<br /> 9006 andkylee<br /> 9007 liuzhenfu<br /> 9008 andkylee<br /> 9009 liuzhenfu<br />(10 rows affected)<br />1>

當然，兩種結果一模一樣。

 

 

最後我們測試表testA中的ID列順序值打亂， 來看看以上語句的執行情況。執行：

update testA set id = id + cast( rand() * 65536 as int )

 

ID列值打亂之後，前100行的資料為：

1> select top 100 * from testA<br />2> go<br /> id name<br /> ----------- ------------------------------<br /> 51366 liuzhenfu<br /> 33573 andkylee<br /> 19447 liuzhenfu<br /> 19408 andkylee<br /> 57839 liuzhenfu<br /> 18817 andkylee<br /> ......................<br /> 19075 liuzhenfu<br /> 17081 andkylee<br /> 26444 liuzhenfu<br /> 6620 andkylee<br /> 52344 liuzhenfu<br /> 49348 andkylee<br />(100 rows affected)

 

 

我們要求返回滿足name='andkylee'的從第9000行開始的10行資料。

declare @id1 int
set rowcount 9000
select @id1 = id from testA where name='andkylee' order by id
set rowcount 10
select *from testA where name='andkylee' and id >= @id1 order by id
set rowcount 0
go

 

結果為：

1> declare @id1 int<br />2> set rowcount 9000<br />3> select @id1 = id from testA where name='andkylee' order by id<br />4> set rowcount 10<br />5> select *from testA where name='andkylee' and id >= @id1 order by id<br />6> set rowcount 0<br />7> go<br />(9000 rows affected)<br /> id name<br /> ----------- ------------------------------<br /> 48639 andkylee<br /> 48639 andkylee<br /> 48641 andkylee<br /> 48641 andkylee<br /> 48642 andkylee<br /> 48643 andkylee<br /> 48644 andkylee<br /> 48644 andkylee<br /> 48650 andkylee<br /> 48650 andkylee<br />(10 rows affected)

 

 

如果不對ID列進行排序， 有下面的sql語句：

declare @id1 int
set rowcount 9000
select @id1 = id from testA where name='andkylee'
set rowcount 10
select *from testA where name='andkylee' and id >= @id1 
set rowcount 0
go

 

相應的結果集為：

1> declare @id1 int<br />2> set rowcount 9000<br />3> select @id1 = id from testA where name='andkylee'<br />4> set rowcount 10<br />5> select *from testA where name='andkylee' and id >= @id1<br />6> set rowcount 0<br />7> go<br />(9000 rows affected)<br /> id name<br /> ----------- ------------------------------<br /> 74076 andkylee<br /> 74514 andkylee<br /> 74053 andkylee<br /> 74385 andkylee<br /> 74339 andkylee<br /> 74792 andkylee<br /> 74794 andkylee<br /> 74984 andkylee<br /> 75052 andkylee<br /> 74138 andkylee<br />(10 rows affected)<br />1>

 

可以發現這個兩句的結果是不同的。

 

我想既然都要求返回指定範圍的結果集， 肯定是有排序的依據了， 否則怎麼知道該返回哪個範圍呢？

 

還有，我給出的第三種方法，在進行表掃描的時候，即使不指定排序，也是能夠得到正確結果的。因為表掃描時很可能會按照表內資料在物理頁面上的物理位置來返回結果。

 

就先介紹到這裡吧， 後續可能會根據情況進行補充。