The Same Query Returns Random Number of Rows

symptom: The same query returns random number of rows
symptom: ROWNUM clause is used
symptom: DISTINCT clause is used
cause: A query with both ROWNUM and DISTINCT clauses, e.g.
select DISTINCT columns from table where ROWNUM<10;
first applies ROWNUM clause and then DISTINCT clause.
Oracle does not guarantee which rows are returned when ROWNUM clause is used:
if there are many duplicate values, DISTINCT (and the whole query) then returns
less rows than in case there are few duplicate values.
The probability of this behavior is higher with parallel processing. In this
case
rows returned by ROWNUM clause depend on which parallel processes return rows
faster at the particular run of the query.

fix:

Use an inline view to force applying DISTINCT clause first.
SQL> select columns from (select DISTINCT columns from table) where ROWNUM&
lt;10;