> As for sorting, rownum is assigned before any sorting. However, if the data
> is read via an index, the rownum appears to be assigned after the sort.
Dan,
I wouldn't say that. What *appears* is the sort, which doesn't take
place since data is fetched in sorted order. Rownum is assigned in the
It is not the predicate that causes the behavior, but the distinct. I'm
taking an educated guess here, so please correct me if I am wrong.
As the row is read, the predicate is applied. If the row matches the
predicate, a rownum is assigned and it is saved as part of the result set.
Once the rownu
Yes sir.
Paul Baumgartel wrote:
>I believe you have ROWNUM confused with ROWID.
>
>--- ltiu <[EMAIL PROTECTED]> wrote:
>
>
>>Holes in your extents/segments. Normal. You get this after repeated
>>updates/deletes/inserts.
>>
>>ltiu
>>
>>Paul Baumgartel wrote:
>>
>>
>>
>>>I've been given a
I agree. I got confused and my second email to disregard my erroneous
first emai on this matterl was apparently shot down by the mail filters
so it never got to you guys in time.
Sorry for the confision.
ltiu
Fink, Dan wrote:
>Actually, ROWNUM has nothing to do with
>extents/segments/inserts
To be exact...
Rownum is sequentially assigned for each row that meets the predicate. If
the row does not match the predicate condition, it is discarded and not
assigned a rownum. Below is an example using our friendly EMP table.
As for sorting, rownum is assigned before any sorting. However, if
Actually, ROWNUM has nothing to do with
extents/segments/inserts/updates/deletes. It is all about access paths. The
solution posted makes sense as ROWNUM can be affected by many subsequent
operations.
-Original Message-
Sent: Thursday, August 29, 2002 11:04 AM
To: Multiple recipients of l
I believe you have ROWNUM confused with ROWID.
--- ltiu <[EMAIL PROTECTED]> wrote:
> Holes in your extents/segments. Normal. You get this after repeated
> updates/deletes/inserts.
>
> ltiu
>
> Paul Baumgartel wrote:
>
> >I've been given a query to investigate. The performance varies
> >extr
Paul,
It's because of how ROWNUM is interpreted.
The first query only returned the first 50 rows it found and you got 24 that
passed the rest of your criteria.
The second query returned 1000 rows of which only 336 passed the rest of
your criteria.
Makes perfect sense to me. This is why ROWNU
Title: RE: ROWNUM strangeness?
show the full sql please, perhaps its how you are grouping your
conditions
Paul Baumgartel wrote:
>I've been given a query to investigate. The performance varies
>extremely depending on whether the entire result set is fetched or a
>ROWNUM
Paul Baumgartel wrote:
>
> I've been given a query to investigate. The performance varies
> extremely depending on whether the entire result set is fetched or a
> ROWNUM < n clause is attached; that makes sense to me. What doesn't
> make sense is the following: if we add WHERE ROWNUM < 50, the
>As we all know, ROWNUM is *usually* assigned when rows are
>read into the cache, before sorting;
Always
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Naveen Nahata
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
Actually. Scratch my previous email on this matter.
We have seen this problem before and I couldn't figure out how to fix it.
ltiu
Paul Baumgartel wrote:
>I've been given a query to investigate. The performance varies
>extremely depending on whether the entire result set is fetched or a
>ROWN
Hi Paul,
How's going?
Maybe there are other criteria's that limits the result set to only
return 24 rows?
Richard Ji
-Original Message-
Sent: Thursday, August 29, 2002 11:53 AM
To: Multiple recipients of list ORACLE-L
I've been given a query to investigate. The performance varies
ex
Holes in your extents/segments. Normal. You get this after repeated
updates/deletes/inserts.
ltiu
Paul Baumgartel wrote:
>I've been given a query to investigate. The performance varies
>extremely depending on whether the entire result set is fetched or a
>ROWNUM < n clause is attached; that m
In my haste, I hadn't noticed that, although the query did not contain
an ORDER BY, it did contain SELECT DISTINCT, which causes an implicit
ORDER BY. As we all know, ROWNUM is usually assigned when rows are
read into the cache, before sorting; thus the order of ROWNUMs in the
output is not seque
Post your query
-Original Message-
Sent: Thursday, August 29, 2002 9:23 PM
To: Multiple recipients of list ORACLE-L
I've been given a query to investigate. The performance varies
extremely depending on whether the entire result set is fetched or a
ROWNUM < n clause is attached; that ma
I've been given a query to investigate. The performance varies
extremely depending on whether the entire result set is fetched or a
ROWNUM < n clause is attached; that makes sense to me. What doesn't
make sense is the following: if we add WHERE ROWNUM < 50, the query
returns 24 rows. If we add
17 matches
Mail list logo