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

发布于 2024-09-13 03:11:52 字数 6128 浏览 10 评论 0原文

我们最近刚刚将数据库从 9i 迁移到 10G （是的..迟到总比不到好，不 - 目前无法选择迁移到 11g :-)）

我的 Oracle 10G DB 的详细信息是：-

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 相关的其他问题，但找不到类似的内容。

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

请参阅最内层子查询中 *** 之间标记的行。

如果我从查询中评论这一行，则查询工作正常。
AND PEM.ENDORSMENT_STATUS = 'POST'
如果我在查询中注释此行，并且其他所有内容与原始内容保持不变，则查询也可以正常工作
按 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

子句，则将按预期检索结果。不带 order by 的此查询的计划如下

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 可能是由于排序而创建的附加视图。

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

任何帮助表示赞赏！

编辑 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)

原文

We just recently moved our DB from 9i to 10G
(Yes..better late than never and No - moving to 11g is currently not an option :-))

Details of my Oracle 10G DB are :-

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

I am faced with a very weird problem since that move.
A query that was and still is working fine with 9i just wont work on 10G.

I did search through other SO questions related to rownum but couldnt really find anything similar.

SQL Query is :-

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;

Basically, the 21 / 30 are numbers that are the index of the records passed to the query for pagination and in 9i, this query works like expected and returns the specified set of data only.

However in 10G, this same query does not work at all - always returns 0 records.

If i comment the rownum related parts of the query:-

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

then i get the entire result set and thats great.
But since my intention is to do pagination using the rownum, the entire purpose is defeated.

Does anyone know of any reason why this query has stopped working with 10G.
I tried looking up any updates to the rownum implementation but havent been able to really come across anything that will help.

EDIT :-
While doing my debugging, i have come across something that to me, is making no sense.
I am putting in the entire query below as i cant explain without it.

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

Refer the lines marked between *** in the innermost subquery.

If i comment this line from my query, the query works fine.
AND PEM.ENDORSEMENT_STATUS = 'POST'
If i comment this line from my query and everything else remains unchanged from the original, the query works fine too
order by 1 ASC

The earlier points related to rownum still hold true but commenting these lines individually seems to be making the rownum thing irrelevant and the entire query works fine (except for that fact that the results are logically different now)

I am confused. To say the least!!!

EDIT 2:

Adding the execution plan for the above query

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)

EDIT 3:

Exact same query as above but if i remove the

ORDER BY 1 ASC

clause, then the results are retrieved as expected.
The PLAN for this query without the order by is below

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)

Note that the only real difference between the two plans is that the one that is not working has the following two additional steps after step 3 where as these steps are not present in the query without the order by - which is working fine.

As expected, step 5 is the step where the ordering of the data is being done.

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

It seems that step 4 is maybe an additional view being created due to the ordering.

WHY this should prevent the rownum logic from working is what i am still trying to grasp.

Any help appreciated!!

EDIT 4 - Original Query plan from 9i environment

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)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我一向站在原地 2024-09-20 03:11:52

正如 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

As Adam has suggested, the subquery is filtering the results after the sort and ROWNUM are applied.

I think you need to force that subquery to be filtered earlier, by using the PUSH_SUBQ hint:

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

I've also removed the TO_CHAR from the ROWNUM - you want to use numbers for that range comparison.

EDIT

Try #2 - use CTE instead:

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

回复收藏 0 原文

时光倒影 2024-09-20 03:11:52

听起来 Oracle 正在将内联视图合并到主查询中，以便 field8（基于 ROWNUM）计算得太晚。我自己还没有看到这种情况发生，但如果发生这种情况，您可以尝试添加如下所示的 NO_MERGE 提示：（

SELECT /*+ NO_MERGE(vw) */ * 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
  )
) vw
WHERE field8 BETWEEN 21 AND 30;

顺便说一句，当您将其视为 WHERE 子句中的数字时，为什么要在 ROWNMUM 上使用 TO_CHAR 呢？）

It sounds like Oracle is mergeing the inline view into the main query so that field8 (based on ROWNUM) is calculated too late. I haven't seen that happen myself, but if that is what is happening you could try adding a NO_MERGE hint like this:

SELECT /*+ NO_MERGE(vw) */ * 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
  )
) vw
WHERE field8 BETWEEN 21 AND 30;

(Incidentally, why the TO_CHAR on ROWNMUM when you are treating it as a number in the WHERE clause anyway?)

回复收藏 0 原文

时光沙漏 2024-09-20 03:11:52

试试这个：

SELECT field1, field2 , field3, field4, field5, field6, field7, to_char(rn) 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 
         ,rownum as rn
   FROM POLICY_MAIN PM 
        inner join POLICY_ENDORSEMENT_MAIN PEM 
           on PM.POLICY_NO = PEM.POLICY_NO 
        inner join MASTER_UW_LOB_CLASS MAS 
           on PM.POLICY_LOB = MAS.UW_LOB_CODE 
  WHERE 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 MAS.UW_CLASS_CODE = 'AUTO'
    AND PEM.ENDORSEMENT_STATUS = 'POST'
    AND PEM.POLICY_ENDORSEMENT_NO = 
         (SELECT MAX(PEM2.POLICY_ENDORSEMENT_NO) 
            FROM POLICY_ENDORSEMENT_MAIN PEM2 
           WHERE PEM.POLICY_NO           = PEM2.POLICY_NO 
         ) 
  order by pm.policy_no ASC) 
WHERE rn BETWEEN 21 AND 40

更改：

重组连接以使用 ANSI 语法来区分连接和过滤器。
将 LIKE UPPER('AUTO') 更改为 = 'AUTO'
删除了不必要的嵌套级别。
将顺序更改为使用表达式与位置符号
将过滤条件 PEM.ENDORSMENT_STATUS = 'POST' 从相关子查询移至主查询，这可能会纠正错误结果问题。
更改了分页条件以使用数字表达式而不是字符表达式，因为：
从“21”和“40”之间的“211”中选择*；
select * from Dual where 211 Between 21 and 40;

不要返回相同的结果。

Try this:

SELECT field1, field2 , field3, field4, field5, field6, field7, to_char(rn) 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 
         ,rownum as rn
   FROM POLICY_MAIN PM 
        inner join POLICY_ENDORSEMENT_MAIN PEM 
           on PM.POLICY_NO = PEM.POLICY_NO 
        inner join MASTER_UW_LOB_CLASS MAS 
           on PM.POLICY_LOB = MAS.UW_LOB_CODE 
  WHERE 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 MAS.UW_CLASS_CODE = 'AUTO'
    AND PEM.ENDORSEMENT_STATUS = 'POST'
    AND PEM.POLICY_ENDORSEMENT_NO = 
         (SELECT MAX(PEM2.POLICY_ENDORSEMENT_NO) 
            FROM POLICY_ENDORSEMENT_MAIN PEM2 
           WHERE PEM.POLICY_NO           = PEM2.POLICY_NO 
         ) 
  order by pm.policy_no ASC) 
WHERE rn BETWEEN 21 AND 40

Changes:

Restructured joins to use ANSI syntax to differentiate joins from filters.
Changed LIKE UPPER('AUTO') to = 'AUTO'
Removed unnecessary level of nesting.
Changed order by to use expression vs. positional notaion
Moved filtering criteria PEM.ENDORSEMENT_STATUS = 'POST' from correlated subquery to main query, which may correct wrong results issue.
Changed pagination condition to use a numeric expression rather than a character one, because:
select * from dual where '211' between '21' and '40';
select * from dual where 211 between 21 and 40;

Do not return the same results.

回复收藏 0 原文

心是晴朗的。 2024-09-20 03:11:52

解释计划应该可以帮助您识别问题。正如托尼所说，任何将内部查询合并到外部查询中都会破坏您的查询。条件 RONUM > 的任何查询1.无条件申请将会失败。

您正在构建的查询可能需要构建整个结果集，然后过滤掉页面的行。您可能需要考虑在内部查询中为所需行构建键集，然后在外部查询中添加其他列。关于在 rownum 上选择的查询的 CARDINALITY 提示可能会有所帮助。

尝试使用“rownum() over (order by 1) rn”来生成顺序。（我假设顺序有时不同于 1。）将“/*+ FIRST_ROWS(20) */”添加到第一个内部查询。 http://www.oracle.com/technology/oramag/ oracle/07-jan/o17asktom.html 获取更多帮助。

回复收藏 0 原文

~没有更多了~