Complex Text Query with ROWNUM Returns Wrong Order of Results
(Doc ID 1072116.1)
Last updated on NOVEMBER 22, 2019
Applies to:
Oracle Text - Version 10.2.0.1 to 11.2.0.1 [Release 10.2 to 11.2]Information in this document applies to any platform.
Symptoms
Query with ROWNUM predicate returns wrong order of rows. See below sample queries:-- Good Query
-- <GOOD Query>
select level_4_id l
from IDX00 where contains(indexXml,'( MDATA(INDEX_STATUS,1)
AND MDATA(M_5,TRUE)) and ( MDATA(M_2,fmt/45))
and ( (rtf controlword error 45) WITHIN AB_19)
and ( SDATA(OWNER like ''CRS00.INS00%'')
OR SDATA(OWNER like ''DPS%''))')>0
and rownum < 20000 order by level_2_id
/
L
---------------------------------------------------------------------------
FL1005
FL1329
FL1425
FL1434
FL1746
FL1745
FL1747
FL1742
FL1743
FL1750
FL1749
FL1748
FL1751
FL1744
FL1765
FL1761
FL1766
FL1764
FL1759
FL1762
FL1758
FL1767
FL1760
FL1763
FL1774
FL1773
FL1776
FL1775
FL1781
FL1772
FL1780
FL1778
FL1779
FL1777
34 rows selected.
-- Bad Query
-- <BAD Query>
select *
from
( select level_4_id l from IDX00 where
contains(indexXml,'(MDATA(INDEX_STATUS,1) AND MDATA(M_5,TRUE))
and ( MDATA(M_2,fmt/45)) and ((rtf controlword error 45) WITHIN AB_19)
and ( SDATA(OWNER like ''CRS00.INS00%'')
OR SDATA(OWNER like ''DPS%''))')>0 and rownum < 20000
order by level_2_id
)where rownum <= 10
/
L
---------------------------------------------------------------------------
FL1005
FL1329
FL1425
FL1434
FL1750
FL1745
FL1746
FL1743
FL1742
FL1747
10 rows selected.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Cause |
| Solution |