Thursday, October 06, 2005

ROWNUM and ROWID

Questions:
How do I limit the number of rows returned by a query?
How do I write a query to get the Top-N salaries from the employee table?
How can I add unique, sequential numbers to an existing table?
How can I differentiate between two completely identical rows?
How can I find a faster way to retrieve a queried row?
How can I find the last row processed in a big batch?

There is one thing all these questions have in common: the answer involves either ROWNUM or ROWID.

So what is ROWNUM and ROWID?

First of all, both are covered in the SQL Reference, Basic Elements of Oracle SQL, Chapter 2:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540.pdf

They are also both referred to as pseudo-columns. That is, they are not "real" columns that will show up when you DESC a table. They don't actually exist anywhere in the database. But they're available for you to use.

In fact, ROWNUM only exists for a row once it is retrieved from a query. It represents the sequential order in which Oracle has retrieved the row. Therefore it will always exist, be at least 1, and be unique (among the rows returned by the query). Obviously it will change from query-to-query. Let's look at a quick example:

scott@Robert> SELECT ROWNUM, ENAME, SAL
2 FROM EMP;

ROWNUM ENAME SAL
---------- ---------- ----------
1 SMITH 800
2 ALLEN 1600
3 WARD 1250
4 JONES 2975
5 MARTIN 1250
6 BLAKE 2850
7 CLARK 2450
8 SCOTT 3000
9 VOLLMAN 5000
10 TURNER 1500
11 ADAMS 1100
12 JAMES 950
13 FORD 3000
14 MILLER 1300


Ok so let's say we want the 5 highest paid employees. Should be easy:

scott@Robert> SELECT ROWNUM, ENAME, SAL
2 FROM EMP
3 WHERE ROWNUM < 6
4 ORDER BY SAL DESC;

ROWNUM ENAME SAL
---------- ---------- ----------
4 JONES 2975
2 ALLEN 1600
3 WARD 1250
5 MARTIN 1250
1 SMITH 800


Whoops! Turns out ROWNUM is assigned before results are ordered, not after. Knowing that, we can write it like this:

scott@Robert> SELECT ENAME, SAL
2 FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) E
3 WHERE ROWNUM < 6;

ENAME SAL
---------- ----------
VOLLMAN 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850


What about ROWID? ROWID actually represents the physical location of the record/row in the database. That being the case, it is (according to Oracle documentation) the fastest way to retrieve a particular row. Faster than an index, even.

Can you use ROWID to differentiate between duplicate rows?
Yes, you can. Since it actually represents the physical location of a row, no two rows within the same table will have the same ROWID. Notice the caveat I added: within the same table. If you're using clustering, two records from different tables could theoretically share the same ROWID.

Do ROWIDs change?
Yes, especially with index organized or partitioned tables. Because ROWIDs represent the physical location of a record/row, the ROWID will change every time the record is physically moved.

Can you use ROWID as a primary key?
No, that's not advisable. While the ROWID will be unique, you would ideally want to use a primary key that doesn't change.

How do you use ROWID to figure out what was the last record that was processed?
Using DBMS_SQL.LAST_ROW_ID to get the ROWID of the last row processed.

You'll see ROWNUM and ROWID pop up occasionally within solutions to problems on AskTom and various Discussion Forums, so I recommend adding it to your own toolbelt as well.

Comments:
Can I use rowid as a primary key

SQL> create table t1(c1 number);

Table created.

SQL> alter table t1 add constraint pk_t1
2 primary key (rowid);
primary key (rowid)
*
ERROR at line 2:
ORA-00904: "ROWID": invalid identifier


Not only not advisable, not doable. You won't be able to reference it in a foreign key constraint either.

Cheers

Niall
 
> Do ROWIDs change?
also in 10g when you "shrink space", you have to enable row movement
 
I need to pick a nit here.

"the 5 highest paid employees"

SELECT ENAME, SAL
FROM (SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) E
WHERE ROWNUM < 6;

This is not quite correct. In reality, this query will return exactly five employees, who will be *among* the set of employees who have the five highest salaries.

For example, if Bloggs had a salary of 2850, he may or may not appear in the result set instead of Blake, because they have the same salary - and on different runs of the same query you may get different results.

The correct answer to "the 5 highest paid employees" may have more than 5 rows, so ROWNUM is not an acceptable solution. One could use the RANK or DENSE_RANK analytic functions to resolve this.

Cheers
Jeff
 
jeff,
> () where rownum<6
I find this is correct, it is all about the specification.

Query above return the five highest paid employees. In case of duplicates (for the 5th and 6th place for example), oracle decides who to chose, and will never return more than five rows.

Use rank, dense_rank, row_number/rownum strictly depends on how you are going to handle duplicates. If you do not specify it explicitely, there is no wrong and no right answer.

cheers
Laurent
 
Rob,
Good intro to ROWNUM and ROWID. In theory, you could use ROWID as a primary key but why would you? You will have to create a column where you can store the ROWID and to be able to update this column when the rows are migrated/moved.

Laurent,
I agree with Jeff that the example query that Rob showed only retrieve the first five records not the top five highest salaries (as there could be more than 5). I would think that a question like this from the business will mean "show me all employees who are paid the top five salaries".
 
peter, I do not disagree with Jeff, his solution is accurate, but if "Business" does not specify correctly , there is than no correct or incorrect answer.

Top-N sql queries (order by in subquery), typically use this example to define how to define the 5 highest paid employees, no matter if some other employee has the same sal as the n°5 but do not figure in the list.

If business want to give exactly 5 teadybears for exactly 5 persons, it will be fine :-)

cheers
 
How do I find, The first 3 records that were inserted into a particular table or The last 10 records inserted into the table.
Assumption we are using distributed database and we do not have any column storing creation date time.
 
Sunaad,

To my knowledge, there is no easy way to tell, for any arbitrary table with NO column (anywhere) storing creation time date and with NO sequential IDs, which were the last n records inserted into a table.

However, there are some very smart Oracle people who may know some trick. Try posting your question to an Oracle forum.

Thanks,
Robert
 
I have a question here.

Suppose I have a table my_tab as:

SQL> desc my_tab
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NOT NULL NUMBER(38)
COL2 NOT NULL DATE
COL3 NOT NULL NUMBER(38)
COL4 NOT NULL VARCHAR2(10)

SQL> select * from my_tab;

COL1 COL2 COL3 COL4
---------- --------- ---------- ----------
1 11-NOV-81 3 sandeep
3 11-NOV-81 4 sumeet
5 11-NOV-81 6 other

Now if I run the following query to fetch the row with highest value in cols3, I get the results as mentioned by you in your blog

SQL> select * from my_tab where rownum < 2 order by col3 desc;

COL1 COL2 COL3 COL4
---------- --------- ---------- ----------
1 11-NOV-81 3 sandeep

i.e it fetches all rows, assigns them rownum, fetches first row from the set and then tried to perform the sorting on first row.

But if the sort key is a primary key (i.e. col1 in this case), we see different behavior. Sorting precedes allocation of rownum:

SQL> select * from my_tab where rownum < 2 order by col1 desc;

COL1 COL2 COL3 COL4
---------- --------- ---------- ----------
5 11-NOV-81 6 other


My query is why is there a different behaviour if sort key is a primary key.
 
Sandeep,

The ROWNUM is assigned before the sort. In Oracle, rows will be fetched in different ways (and orders) depending on things such as keys, indexes, how the table is organised, the where clauses you used, and so on.

That's all that is happening. You can learn more about how Oracle is fetching the rows by looking at the query plan.

Robert
 
This comment has been removed by a blog administrator.
 
This comment has been removed by a blog administrator.
 
write an ANSI-92 standard query
SELECT A.A1,B.B1
FROM A,B
WHERE A.A1=B.B1(+)
 
what is difference between in the following
select a1
from a
where exists(select 1 from b where a.a1=b.b1)

select a1
from a
where a1 in (select b.b1 from b)
 
a b c d
1 x y z
1 a b c
3 t x z

a is primary key

a1 b1 c1 d1
1 x1 y1 z1
2 a1 b1 c1
1)updating coloumns b,c,d in table A from table B wherever join condition is satisfied A.a=B.a1
2)results of table A after the update
 
I'm glad to make this site available as a resource, but you still have to do your own homework.

However, you'll be happy to know that I've got articles that show you how to answer each of your questions. Make use my site's search bar!
 
hi my requirement is, i have a table, in that i have 50 rows for each brand, and for each brand i need to display top10 sites based on their sales. How can i ...
 
Everything you need to answer that question is here in this article. Go ahead and take a crack at it and show us what you come up with!
 
awesome post
 
Is there a way to combine these two SQL statements into one achiving the same in one call/trip-to-oracle.


INSERT INTO master(KEY,DATA) VALUES ('XYZ','012345698789');
INSERT INTO child (KEY,DATA) VALUES ('XYZ',(select rowid from master where KEY='XYZ'));
 
What about the nested select queries for the generation of sequence within a repeated values, e.g.
select a.id
(select count(*) from cnt b
where a.id = b.id
and b.rowid <= a.rowid) sequence_no
from
cnt a
CNT TABLE
=========
id
1
1
2
2
3
4
5
5
5
Will the sequence number generated with the above view always the same?
Thanks
 
can we use '=' with rownum,
select ename, sal from (select distinct(sal) from emp order by sal desc) where rownum =1;

Can we replace 1 by 2, 3 4 ....and so on to get nth highest value..


Many Thanks
Vishal
 
This comment has been removed by the author.
 
can we use a '>' operator with ROWNUM?

SQL> select hisal from salgrade;

HISAL
----------
1200
1400
2000
3000
9999

SQL> select hisal from salgrade where rownum>2;

no rows selected


Thanks in Advance!!!!!
 
Nice Blog with exact solutions
 
sir if i have t1 table there is 8 row and i want that row no 6 no budy change and other row may be updated,insert.......
so can u tell me what is the query for that question
 
Thanks and I like the phrase 'adding it to your own toolbelt'. I have added these details to my toolbelt. :)
 
how can i find nth row of a table
 
when a null value exist then how to select notnull from table
 
this helped me.
 
SELECT ENAME, SAL
FROM
(SELECT ENAME, SAL FROM EMP
ORDER BY SAL DESC) E
WHERE ROWNUM < 6;
ENAME SAL
---------- ---------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850

but i want answer like
ENAME SAL
---------- ---------
KING 5000
SCOTT 3000
JONES 2975
BLAKE 2850

how to write query to above output
 
i have executed delete comand after that i have created table whether deletion will commit or not,if table successfuly created?
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?