Re: ROWID, ROWNUM and views

From: Ed Prochak <edprochak_at_gmail.com>
Date: Fri, 27 Jun 2008 14:29:31 -0700 (PDT)
Message-ID: <e89381b8-9aff-458f-b703-0cd6d82d4816@d45g2000hsc.googlegroups.com>


On Jun 27, 1:18 am, stre..._at_yahoo.co.uk wrote:
> Here's some advice for you about the pseudo-columns ROWID, ROWNUM and
> their behaviour with views.
>
> * As a general rule avoid using ROWID espcially with complex views
> made up of multiple base tables where a DISTINCT clause is used. The
> DISTINCT clause will result in the SELECT statement with ROWID not
> returning anything.

Ignoring the problem of using DISTINCT in a query (separate issue),

Do not use ROWID because it is a physical pointer value. It can change between queries.

Remember you are using a Relational database, so the maxim: the key, the whole key and nothing but the key so help me Codd is worth putting int practice.

> * As another general rule avoid using ROWNUM. Have found that if the
> view has an ORDER BY in the inner query then the SELECT return with
> ROWNUM is very very slow ....

Agreed.

However the fact is both things are old news to the regulars here. What prompted your post today?

  Ed Received on Fri Jun 27 2008 - 16:29:31 CDT

Original text of this message