Complex Text Query with ROWNUM Returns Wrong Order of Results

(Doc ID 1072116.1)

Last updated on MARCH 09, 2010

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms