Prev: Oracle ODBC scalability with increased user volumes
Next: Fine tuning my queries !
From: strepxe on 27 Jun 2008 02:18
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.
* 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 ....

From: Marcin Wróblewski on 27 Jun 2008 15:29
strepxe(a)yahoo.co.uk pisze:
> 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.
> * 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 ....
>

Could you give some examples?
From: Ed Prochak on 27 Jun 2008 17:29
On Jun 27, 1:18 am, stre...(a)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
 | 
Pages: 1
Prev: Oracle ODBC scalability with increased user volumes
Next: Fine tuning my queries !