chrisrlong, your discussion about these types of queries on the various threads has indeed been quite enlightening. I've only been reading these forums for a short while and discussions like these are what I was hoping to find here; discussions that teach me new things about how PL/SQL works. I wish that I had known about these forums sooner, especially when I participated in another discussion on another list (non-Oracle/DBA related) where someone had asked basically the same question. How do I select the N beginning/middle/ending number of rows inserted into a table?

So in the interests of furthuring my education, let me discuss the following concerning the use of ROWNUM in queries. I am appending to this thread since you suggested to other posters to keep the discussions in single threads so that all of the information is in one place. :)

In the other thread, I originally suggested the use of the ROWNUM column to find these rows. We discussed some queries, tried them out and discovered some problems with using ROWNUM. In particular, the first sentence from the following section from the Oracle 8.0.5 documentation troubled us: "Oracle assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row. However, if an ORDER BY clause causes Oracle to use an index to access the data, Oracle may retrieve the rows in a different order than without the index, so the ROWNUMs may be different than they would be without the ORDER BY clause."

Another person suggested using an inline view as the method for solving this problem (very similar to your example). However, I have not reconciled to myself how inserting an ORDER BY clause in the inline view is going to result in ROWNUM values being assigned to the rows according to the specified order. My understanding of the statement from the Oracle documentation is that ROWNUM values are not affected by the ORDER BY clause. Is this a correct understanding or not? I believe that jmodic asked the same question in his reply on thread http://www.dbasupport.com/forums/sho...threadid=5480. To phrase the question again, is using the ORDER BY clause in the inline view guaranteed to assign ROWNUM values based upon the ordered set of rows?

I'm not an expert, so feel free to correct me. Just be nice. :)

On a related note, I have seen one other solution posted elsewhere to this problem. They suggested using the INDEX_DESC hint along with ROWNUM to get the last N number of rows from a table based upon some id column. For example,

select /*+ INDEX_DESC( ) */
from

where ROWNUM < N;

or something similar to that. Is using these types of hints an acceptable way of addressing this type of problem?

Oh, and I can post a really horribly inefficient query to determine these values as well. I'll refrain since it depends upon taking the cartesian product of a table with itself to assign the "numbers" to the rows. I suspect that it would be considered worse than the MINUS approach. :)
Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules



Click Here to Expand Forum to Full Width