oracle10g - Oracle 10G - 从 9i 迁移后使用 rownum 的查询停止工作

Question

我们最近刚刚将我们的数据库从 9i 移动到 10G(是的..迟到总比不到好，而且不 - 目前不能选择迁移到 11g :-))

我的 Oracle 10G 数据库的详细信息是:-

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production

自那次搬家后，我遇到了一个非常奇怪的问题。过去和现在都可以在 9i 上正常工作的查询只是在 10G 上无法工作。

我确实搜索了与 rownum 相关的其他 SO 问题，但找不到任何类似的东西。

SQL 查询是:-

SELECT * FROM 
( SELECT field1, field2 , field3, field4, field5, field6, field7, to_char(rownum) field8
  FROM
    ( SELECT 
        field1,
        field2,
        field3,
        field4,
        field5,
        field6,
        field7,
        ''
      FROM
      .......REST OF MY COMPLEX INNER QUERY
  )
) 
WHERE field8 BETWEEN 21 AND 30;

基本上，21/30 是传递给分页查询的记录索引的数字，在 9i 中，此查询按预期工作并仅返回指定的数据集。

但是在 10G 中，同样的查询根本不起作用 - 总是返回 0 条记录。

如果我注释查询的 rownum 相关部分:-

to_char(rownum) field8  and
WHERE field8 BETWEEN 21 AND 30;

然后我得到了整个结果集，这太棒了。但由于我的意图是使用 rownum 进行分页，所以整个目的都落空了。

有谁知道此查询停止使用 10G 的任何原因。我尝试查找对 rownum 实现的任何更新，但未能真正找到任何有用的信息。

编辑:-在进行调试时，我遇到了一些对我来说毫无意义的事情。我将整个查询放在下面，因为没有它我无法解释。

SELECT * FROM 
( SELECT field1, field2 , field3, field4, field5, field6, field7, to_char(rownum) field8 from 
 ( SELECT PM.POLICY_NO field1
   ,PM.INSURED_CODE field2
   ,PM.INSURED_NAME field3
   ,TO_CHAR(PM.POLICY_EFFECTIVE_DATE,'DD/MM/YYYY') field4
   ,TO_CHAR(PM.POLICY_EXPIRATION_DATE,'DD/MM/YYYY') field5
   ,'' field6
   ,'' field7
   ,'' field8
   FROM POLICY_MAIN PM
   ,POLICY_ENDORSEMENT_MAIN PEM
   ,MASTER_UW_LOB_CLASS MAS
   WHERE PM.POLICY_NO = PEM.POLICY_NO
   AND PM.POLICY_NO LIKE UPPER('%%')
   AND PM.INSURED_CODE LIKE UPPER('%%')
   AND PM.SOURCE_OF_BUSINESS LIKE UPPER('%%')
   AND PM.POLICY_TYPE IS NULL
   AND PM.POLICY_STATUS = 'POST'
   AND PM.POLICY_LOB = MAS.UW_LOB_CODE
   AND MAS.UW_CLASS_CODE LIKE UPPER('AUTO')
   AND PEM.POLICY_ENDORSEMENT_NO =
    (SELECT MAX(PEM2.POLICY_ENDORSEMENT_NO)
     FROM   POLICY_ENDORSEMENT_MAIN       PEM2
     WHERE  PEM.POLICY_NO                 = PEM2.POLICY_NO
     ***AND    PEM.ENDORSEMENT_STATUS        = 'POST'***
     )
   ***order by 1 ASC***
  )
) 
WHERE field8 BETWEEN 21 AND 40

引用最内层子查询中***之间标记的行。

1. 如果我从我的查询中注释这一行，查询工作正常。

   AND PEM.ENDORSEMENT_STATUS = 'POST'

2. 如果我对查询中的这一行进行注释，并且其他所有内容都与原始内容保持不变，则查询也可以正常工作

   按 1 ASC 排序

与 rownum 相关的早期观点仍然适用，但单独评论这些行似乎使 rownum 无关紧要并且整个查询工作正常(除了现在结果在逻辑上不同的事实)

我很困惑。至少可以说!!!

编辑 2:

为上述查询添加执行计划

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=19 Card=1 Bytes=114)

   1    0   VIEW (Cost=19 Card=1 Bytes=114)
   2    1     COUNT
   3    2       FILTER
   4    3         VIEW (Cost=17 Card=1 Bytes=128)
   5    4           SORT (ORDER BY) (Cost=17 Card=1 Bytes=130)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'POLICY_ENDORSEMENT_MAIN' (TABLE) (Cost=2 Card=1 Bytes=39)
   7    6               NESTED LOOPS (Cost=16 Card=1 Bytes=130)
   8    7                 NESTED LOOPS (Cost=14 Card=1 Bytes=91)
   9    8                   TABLE ACCESS (FULL) OF 'POLICY_MAIN' (TABLE) (Cost=14 Card=1 Bytes=82)
  10    8                   INDEX (UNIQUE SCAN) OF 'PK_MASTER_UW_LOB_CLASS' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=9)
  11    7                 INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (INDEX (UNIQUE)) (Cost=1 Card=1)
  12    3         SORT (AGGREGATE)
  13   12           FILTER
  14   13             INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (INDEX (UNIQUE)) (Cost=2 Card=2 Bytes=68)

编辑 3:

与上面的查询完全相同，但如果我删除

ORDER BY 1 ASC

子句，然后按预期检索结果。没有排序依据的此查询的计划如下

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=18 Card=1 Bytes=114)
   1    0   VIEW (Cost=18 Card=1 Bytes=114)
   2    1     COUNT
   3    2       FILTER
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'POLICY_ENDORSEMENT_MAIN' (TABLE) (Cost=2 Card=1 Bytes=39)
   5    4           NESTED LOOPS (Cost=16 Card=1 Bytes=130)
   6    5             NESTED LOOPS (Cost=14 Card=1 Bytes=91)
   7    6               TABLE ACCESS (FULL) OF 'POLICY_MAIN' (TABLE) (Cost=14 Card=1 Bytes=82)
   8    6               INDEX (UNIQUE SCAN) OF 'PK_MASTER_UW_LOB_CLASS' (INDEX (UNIQUE)) (Cost=0 Card=1 Bytes=9)
   9    5             INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (INDEX (UNIQUE)) (Cost=1 Card=1)
  10    3         SORT (AGGREGATE)
  11   10           FILTER
  12   11             INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (INDEX (UNIQUE)) (Cost=2 Card=2 Bytes=68)

请注意，这两个计划之间唯一真正的区别是，不起作用的计划在第 3 步之后有以下两个额外的步骤，因为这些步骤在没有 order by 的查询中不存在 - 这工作正常。

正如预期的那样，第 5 步是完成数据排序的步骤。

   4    3         VIEW (Cost=17 Card=1 Bytes=128)
   5    4           SORT (ORDER BY) (Cost=17 Card=1 Bytes=130)

似乎第 4 步可能是由于排序而创建的附加 View 。

为什么这会阻止 rownum 逻辑工作是我仍在努力理解的。

感谢任何帮助!!

EDIT 4 - 来自 9i 环境的原始查询计划

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   VIEW
   2    1     COUNT
   3    2       VIEW
   4    3         SORT (ORDER BY)
   5    4           FILTER
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'POLICY_MAIN'
   7    6               NESTED LOOPS
   8    7                 NESTED LOOPS
   9    8                   TABLE ACCESS (FULL) OF 'POLICY_ENDORSEMENT_MAIN'
  10    8                   INDEX (RANGE SCAN) OF 'PK_MASTER_UW_LOB_CLASS' (UNIQUE)
  11    7                 INDEX (RANGE SCAN) OF 'PK_POLICY_MAIN' (UNIQUE)
  12    5             SORT (AGGREGATE)
  13   12               FILTER
  14   13                 INDEX (RANGE SCAN) OF 'PK_POLICY_ENDORSEMENT_MAIN' (UNIQUE)

Answer 1

正如 Adam 所建议的，子查询在应用排序和 ROWNUM 之后过滤结果。

我认为您需要通过使用 PUSH_SUBQ 提示强制更早地过滤该子查询:

SELECT * FROM 
( SELECT field1, field2 , field3, field4, field5, field6, field7,
         ROWNUM field8 from 
 ( SELECT PM.POLICY_NO field1
   ,PM.INSURED_CODE field2
   ,PM.INSURED_NAME field3
   ,TO_CHAR(PM.POLICY_EFFECTIVE_DATE,'DD/MM/YYYY') field4
   ,TO_CHAR(PM.POLICY_EXPIRATION_DATE,'DD/MM/YYYY') field5
   ,'' field6
   ,'' field7
   ,'' field8
   FROM POLICY_MAIN PM
   ,POLICY_ENDORSEMENT_MAIN PEM
   ,MASTER_UW_LOB_CLASS MAS
   WHERE PM.POLICY_NO = PEM.POLICY_NO
   AND PM.POLICY_NO LIKE UPPER('%%')
   AND PM.INSURED_CODE LIKE UPPER('%%')
   AND PM.SOURCE_OF_BUSINESS LIKE UPPER('%%')
   AND PM.POLICY_TYPE IS NULL
   AND PM.POLICY_STATUS = 'POST'
   AND PM.POLICY_LOB = MAS.UW_LOB_CODE
   AND MAS.UW_CLASS_CODE LIKE UPPER('AUTO')
   AND PEM.POLICY_ENDORSEMENT_NO =
    (SELECT /*+ PUSH_SUBQ*/
            MAX(PEM2.POLICY_ENDORSEMENT_NO)
     FROM   POLICY_ENDORSEMENT_MAIN       PEM2
     WHERE  PEM.POLICY_NO                 = PEM2.POLICY_NO
     AND    PEM.ENDORSEMENT_STATUS        = 'POST'
     )
   order by 1 ASC
  )
) 
WHERE field8 BETWEEN 21 AND 40

我还从 ROWNUM 中删除了 TO_CHAR - 您想使用数字进行范围比较。

编辑

尝试 #2 - 使用 CTE 代替:

WITH q AS
( SELECT /*+MATERIALIZE*/
         field1, field2 , field3, field4, field5, field6, field7,
         ROWNUM field8 from 
 ( SELECT PM.POLICY_NO field1
   ,PM.INSURED_CODE field2
   ,PM.INSURED_NAME field3
   ,TO_CHAR(PM.POLICY_EFFECTIVE_DATE,'DD/MM/YYYY') field4
   ,TO_CHAR(PM.POLICY_EXPIRATION_DATE,'DD/MM/YYYY') field5
   ,'' field6
   ,'' field7
   ,'' field8
   FROM POLICY_MAIN PM
   ,POLICY_ENDORSEMENT_MAIN PEM
   ,MASTER_UW_LOB_CLASS MAS
   WHERE PM.POLICY_NO = PEM.POLICY_NO
   AND PM.POLICY_NO LIKE UPPER('%%')
   AND PM.INSURED_CODE LIKE UPPER('%%')
   AND PM.SOURCE_OF_BUSINESS LIKE UPPER('%%')
   AND PM.POLICY_TYPE IS NULL
   AND PM.POLICY_STATUS = 'POST'
   AND PM.POLICY_LOB = MAS.UW_LOB_CODE
   AND MAS.UW_CLASS_CODE LIKE UPPER('AUTO')
   AND PEM.POLICY_ENDORSEMENT_NO =
    (SELECT MAX(PEM2.POLICY_ENDORSEMENT_NO)
     FROM   POLICY_ENDORSEMENT_MAIN       PEM2
     WHERE  PEM.POLICY_NO                 = PEM2.POLICY_NO
     AND    PEM.ENDORSEMENT_STATUS        = 'POST'
     )
   order by 1 ASC
  )
) 
SELECT * from q
WHERE field8 BETWEEN 21 AND 40