Re: Performance problem - help appreciated.

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Wed, 31 Mar 2004 19:50:10 GMT
Message-ID: <ShFac.25034$u_2.9227@nwrddc01.gnilink.net>

"Mark" <simmons_mark_at_yahoo.com> wrote in message news:5366fb41.0403311121.3fea7dfc_at_posting.google.com...
> If you posted the explain plan for these queries, we could decipher
> why it seems to work.
>
> Personally, it's not clear to me why you would add rownum. I usually
> solve these type of problems with a hint. I'm curious as to what that
> does.
>
> Mark Simmons
> Sr. Oracle DBA
> Sabre-Holdings, Southlake, TX

--snip--

Like I stated before. The addition of rownum (which is a pseudo-column) forces Oracle to execute the inner query first. Otherwise Oracle might re-write the query and hence nullify the hint in the inner select.

In other words, as stated in the concepts manual, A view is not inherently modifiable if it contains a pseudo column. The above trick just takes advantage of that and tends to prevent oracle from re-writing that query.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c18trigs.htm#4274

Thanks,

Anurag Received on Wed Mar 31 2004 - 13:50:10 CST