Fetching a particular row from a table

[dsuva]

i need to write a query which will fetch me a particular row of a table (for ex. say i want to select the 12500th row from the customer table) .

Can anyone help me out

[dsuva]

Ok gurus, i have solved the problem.......
here is solution if any of you would like to make use of this

select account_no from
(select rownum rum, account_no
from customer) where rum = 125000;

This query returns tha value of account_no column of the 125000 row perfectly.


if any of you can come up with a better solution please let me know.

[Zaggy]

Rownum is not a reliable way of getting the postion within the table. Rownum is created and added to a row as it is read in. So, deletes, indexes and other situation can create a wrong order for the query, that does not reflect the order of insertion of that value.

Here is a link on Rownum:
http://otn.oracle.com/doc/oracle8i_8...ch26.htm#33158

[dknight]

I saw the same link but reached a slightly different conclusion.

ROWID may not be a continuous sequence due to deletes. But if a query is structured the same way each time with the exception of the ROWNUM number, shouldn't it have the same execution plan and therefore be consistent?

[kmesser]

According to Oracle, you can NEVER rely on the sequence of results in a query, unless you explicitly identify in your query how to order the result set. You may get lucky and often get the same result set in an unordered query, particularly in a static or rarely changed dataset. However, when you start adding/updating/deleting, that perceived consistency can disappear. These circumstances can be different for an IOT.

Set the order.

Considering the above, why would you wish to select a record with a specific rownum? If you do it once, and wish to find that same record again, capture the rowid.

[jmodic]

Originally posted by dknight
ROWID may not be a continuous sequence due to deletes. But if a query is structured the same way each time with the exception of the ROWNUM number, shouldn't it have the same execution plan and therefore be consistent?

ROWNUM assigned to each row is not consistent, even if the query have the same execution plan each time it is executed. Many reasons for this. Let's assume the table rows are *allways* accessesd via full table scan (FTS). In FTS, we assume the data will be read in the ascending order from the first to the last row inside block, blocks will be read in the ascending order inside extent, and also extents will be read in the ascending order from the first towards last. In short: sequentialy from the first block of the first extent to the HWM block in the last ocuppied extent. Let's see two examples, where particular row will be assigned different ROWNUM at different runs of a query.

Example 1.
If there are deletes/updates on the table, blocks might be put on/of the free lists. This means that new rows might be inserted in the existing blocks, causing new rows to be physicaly put *between* two existing rows.
So in first run your query might return:
- rowX .. rownum=N
- rowY .. rownum=N+1
Now new rowZ is inserted, physicaly between rowX and rowY, the same query will return:
- rowX .. rownum=N
- rowZ .. rownum=N+1
- rowY .. rownum=N+2

Example 2.
You move the table using ALTER TABLE tab MOVE ... PARALLEL ...; If the table is rebuilt in parallel, the relative physical locations of rows will be different than in original table, meaning that rownum assigned to a particualr row with the same query will be different as it was prior to rebuild.

[marist89]

Originally posted by kmesser
Considering the above, why would you wish to select a record with a specific rownum?

And if you need to select a specific row number, why not have an extra field and populate it via a sequence?