ITPub博客

首页 > Linux操作系统 > Linux操作系统 > The Same Query Returns Random Number of Rows

The Same Query Returns Random Number of Rows

原创 Linux操作系统 作者:xhailiang 时间:2007-08-04 00:00:00 0 删除 编辑

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;


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-172009/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-02-17

  • 博文量
    270
  • 访问量
    414279