Re: rownum

From: <xhoster_at_gmail.com>
Date: 05 Jan 2005 23:55:48 GMT
Message-ID: <20050105185548.575$bH@newsreader.com>

"Mark D Powell" <Mark.Powell_at_eds.com> wrote:
> This is expected behavior of the rownum psuedo column going as far back
> as I can remember. I do not know where you find this specific fact in
> the manuals. In the SQL manual section on pseudo columns where it
> convers rownum the manual tells you that any > rownum condition returns
> FALSE and shows examples using less than, <, but it does not mention
> the fact you can only query for = 1.

I assume you meant any "rownum >" condition returns false (rather than any "> rownum" condition....). But reality does not look that simple. A "> rownum" condition can return true in some cases, for example in a disjunction.

  1 select dbms_utility.get_hash_value(owner,0,1000), rownum from   all_objects 2* where dbms_utility.get_hash_value(owner,0,1000)=394 or rownum > 10 SQL> /

DBMS_UTILITY.GET_HASH_VALUE(OWNER,0,1000) ROWNUM

----------------------------------------- ----------
                                      394          1
                                      394          2
                                      394          3
                                      394          4
                                      394          5
                                      394          6
                                      394          7
                                      394          8
                                      394          9
                                      394         10
                                      394         11
                                      394         12
                                      604         13
                                      604         14
                                      604         15
                                      604         16
                                      604         17
                                      604         18
                                      604         19
                                      604         20
                                      604         21
                                      604         22

... for many more rows.

Once the left side of the "or" causes more than 10 rows to be returned, then the right side of the "or", involving a "rownum >" condition, keeps being true for the rest of the table.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB

Received on Wed Jan 05 2005 - 17:55:48 CST