All geek questions in one place

Oracle 10G - query using rownum stopped working after switching from 9i

We just moved our database from 9i to 10G (Yes ... later than never, and No - switching to 11g is currently not an option :-))

Details of my 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

Since then I have encountered a very strange problem. The query, which was and still works fine with 9i, just doesn't work on 10G.

I was looking for other questions related to rownum but could not find anything like it.

SQL Query: -

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, 21/30 is the numbers that are the index of the records passed to the pagination request, and in 9i this query works as expected and only returns the specified dataset.

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

, rownum: -

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

, . rownum, .

- , 10G. rownum, havent -, .

: - , -, . , .

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

, *** .

  • , .

    PEM.ENDORSEMENT_STATUS = 'POST'

  • , ,

    1 ASC

, 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, , , - .

, 5 - , .

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

, 4 - , , , - .

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)
+3
4

, 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

TO_CHAR ROWNUM - .

# 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
+2

, Oracle , 8 ( 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;

(, TO_CHAR ROWNMUM, WHERE?)

+1

:

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'
  • .
  • vs. positional notaion
  • PEM.ENDORSEMENT_STATUS = 'POST' , .

  • , , , :

    select * from dual where '211' between '21' and '40';

    select * from dual where 211 between 21 and 40;

.

+1

. , . , RONUM > 1, .

, , , . , , . CARDINALITY rownum.

"rownum() over (order by 1) rn" . ( , 1 ). "/* + FIRST_ROWS (20) */" . http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html .

0

Source: https://habr.com/ru/post/1757780/

More articles:


All Articles