使用 SQL 搜索 DB2 进行分页的最快/最有效的方法

现在我执行两个单独的 SQL 语句，其中一个执行SELECT COUNT(*)与搜索语句的标准基本相同。我不是最擅长做出这些陈述，有时有点慢，我想知道是否有更好的方法来做我所做的事情。可能只执行一条 SQL 语句并在 PHP 中执行更多工作？这是我有语句的“搜索包含”示例。

在第二个语句中，您将看到 Y 之间的 X，它部分是由第一个行计数语句的结果计算得出的。

SQL 行数：

SELECT COUNT(*) 
FROM itemmast 
LEFT OUTER JOIN itemweb 
ON iline = line 
AND iitem = item 
JOIN linemst 
ON iline = lline 
LEFT OUTER JOIN custord 
ON opline = iline 
AND opitem = iitem 
AND opcust = '12345' 
LEFT OUTER JOIN ordwdtl 
ON owline = iline 
AND owitem = iitem 
AND owusr ='user' 
AND owcust ='12345' 
WHERE ico = 01 
AND iecomm = 'Y'  
AND (UPPER(ITEMDESC) || UPPER(PRODDESC)) LIKE '%FOO%' 
     OR LINE LIKE '%FOO%' 
     OR UPPER(MFGNAME) LIKE '%FOO%' 
     OR UPPER(ITEM) LIKE '%FOO%' 
     OR UPPER(PRODNAME) LIKE '%FOO%' 
     OR UPPER(IDESC1 || IDESC2) LIKE '%FOO%' 
     OR UPPER(IMFGNO) LIKE '%FOO%' 
     OR UPPER(IITEM) LIKE '%FOO%') 

SQL 搜索：

SELECT * 
FROM (SELECT iline AS line, iitem AS item, rownumber() OVER (ORDER BY item) AS ROW_NUM 
      FROM itemmast 
      LEFT OUTER JOIN itemweb 
      ON iline = line 
      AND iitem = item 
      JOIN linemst 
      ON iline = lline 
      LEFT OUTER JOIN custord 
      ON opline = iline 
      AND opitem = iitem 
      AND opcust = '12345' 
      LEFT OUTER JOIN ordwdtl 
      ON owline = iline 
      AND owitem = iitem 
      AND owusr = 'user' 
      AND owcust = '12345' 
      WHERE ico = 01 
      AND iecomm = 'Y' 
      AND (UPPER(ITEMDESC) || UPPER(PRODDESC)) LIKE '%FOO%' 
           OR LINE LIKE '%FOO%' 
           OR UPPER(MFGNAME) LIKE '%FOO%' 
           OR UPPER(ITEM) LIKE '%FOO%' 
           OR UPPER(PRODNAME) LIKE '%FOO%' 
           OR UPPER(IDESC1 || IDESC2) LIKE '%FOO%' 
           OR UPPER(IMFGNO) LIKE '%FOO%' 
           OR UPPER(IITEM) LIKE '%FOO%')) 
      AS TEMP 
WHERE ROW_NUM BETWEEN 0 AND 25

如果您尝试在分页计数旁边显示结果总数（即 38 中的 0 到 25），那么单独的语句可能是您的最佳选择。我已经尝试了很多方法来获取各个行的计数，但性能（即使在中等测试数据库上）很糟糕。

您可能应该做的是创建一个可以查询的视图，其中包含所有选择标准，然后用必要的行为包装它：
Count:

SELECT COUNT(*)
FROM view

排名行：

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY item) as RANK
      FROM view) as TEMP
WHERE RANK BETWEEN 0 AND 25

您当然需要添加相对 where 条件，但这是视图要处理的事情类型。

如果您实际上不需要提前知道总行数，则可以简单地将最终排名设置为起始排名加上一些偏移量。然后，当您使用 PHP 显示结果时，只需编辑结束显示值即可。

一些随机笔记： 1) 是否有理由认为line isn't upper()d?
2）无论你做什么，这个查询的性能几乎都会受到影响，仅仅是因为所有的字符串操作/比较。是否可以消除或忽略某些条件？除非在各个字符串列上使用的索引已经有upper应用于它们（某些更高版本的 DB2 允许将某些标量函数应用于索引键），大多数索引将完全无用（这对您正在寻找的索引没有帮助）%ANYTHING%毕竟）。

好吧，有一种“棘手”的方法可以做这样的事情，并且似乎获得了不错的性能...... 尝试这样的事情（首先定义的视图确实会有帮助）：

SELECT TEMP.*, CASE WHEN RANK = 0 THEN (SELECT COUNT(*)
                                        FROM view)
                    ELSE 0 END
FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY item) as RANK
      FROM view) as TEMP
WHERE RANK BETWEEN 0 AND 25

当然，你仍然需要拥有你的where子选择中也定义了子句...