Just SQL Part VI – Two Famous Pseudocolumns

A pseudocolumn is a column that looks like a column but
really is not a column. What! Stick around and find out what a pseudocolumn
really is.

The best way to describe what a pseudocolumn is would be to
take two of Oracle’s most popular pseudocolumns, ROWID and ROWNUM, and explain
their use. While use of these two pseudocolumns has been wide spread, they
have also been misused or misunderstood. This article should shed some light on
them and provide some use cases that you can make use of.

ROWID

Probably the most famous of all Oracle pseudocolumns is the
ROWID pseudocolumn. This returns for each row selected the unique internal row
address for the physical row. The rowid of a row contains such information as
the object_id, file_id, block_id, and row number that uniquely identifies a row
in the table. Moreover, since index lookups only evaluate to a rowid and then
go after the data, accessing a row by rowid is the fastest way to extract
information. If you care to explore these values just look at the DBMS_ROWID
package in the Oracle manuals.


SQL> SELECT ROWID, dog_origin.* FROM dog_origin;
ROWID COUNTRY BREED BREED_SIZE
—————— ————— ———————— —————
AAAQlCAAEAAAAKQAAA Country Breed Breed_size
AAAQlCAAEAAAAKQAAB Germany German Shepherd Dog Big
AAAQlCAAEAAAAKQAAC Germany Dobermann Big
AAAQlCAAEAAAAKQAAD Germany Rottweiler Big
AAAQlCAAEAAAAKQAAE USA Siberian Husky Medium
AAAQlCAAEAAAAKQAAF USA Alaskan Malamute Medium
AAAQlCAAEAAAAKQAAG USA American Bulldog Big
AAAQlCAAEAAAAKQAAH Switzerland Bernese Mountain Dog Big
AAAQlCAAEAAAAKQAAI Switzerland Saint Bernard Dog Big
AAAQlCAAEAAAAKQAAJ Switzerland Entlebuch Cattle Dog Medium
AAAQlCAAEAAAAKQAAK Australia Australian Cattle Dog Medium
AAAQlCAAEAAAAKQAAL Australia Jack Russell Terrier Small

Probably the most famous use for the ROWID is to get rid of
duplicates in a table. To do this you need only construct a SQL statement such
as this. Basically what happens here is that you delete from the table dog_origin
where its rowid is not equal to the MAX(rowid) on the related columns. This
particular query example compares all columns in the table dog_origin, but depending
on your requirements to scrub the data you may compare a subset of the rows.


SQL> DELETE FROM dog_origin a
WHERE rowid <> ( SELECT MAX(rowid) FROM dog_origin b
WHERE a.country = b.country
AND a.breed = b.breed
AND a.breed_size = b.breed_size;

ROWNUM

The ROWNUM pseudocolumn returns, for each row, a number that
indicates the order in which the row was selected from the table. For instance,
we can select all rows from the dog_origin table and ROWNUM is assigned in the
following manner.


SQL> SELECT rownum, country, breed, breed_size FROM dog_origin;
ROWNUM COUNTRY BREED BREED_SIZE
———- ————— ————————- ———-
1 Country Breed Breed_size
2 Germany German Shepherd Dog Big
3 Germany Dobermann Big
4 Germany Rottweiler Big
5 USA Siberian Husky Medium
6 USA Alaskan Malamute Medium
7 USA American Bulldog Big
8 Switzerland Bernese Mountain Dog Big
9 Switzerland Saint Bernard Dog Big
10 Switzerland Entlebuch Cattle Dog Medium
11 Australia Australian Cattle Dog Medium
12 Australia Jack Russell Terrier Small

The major misconception in using ROWNUM is that many believe
it can be used to rank a row just by ordering the results of table. As can be
seen in the next query, when the rows are ordered by BREED, the ROWNUM does not
provide a true ranking of the order by breed name.


SQL> SELECT rownum, country, breed, breed_size FROM dog_origin ORDER BY breed;
ROWNUM COUNTRY BREED BREED_SIZE
———- ————— ————————- ———-
6 USA Alaskan Malamute Medium
7 USA American Bulldog Big
11 Australia Australian Cattle Dog Medium
8 Switzerland Bernese Mountain Dog Big
1 Country Breed Breed_size
3 Germany Dobermann Big
10 Switzerland Entlebuch Cattle Dog Medium
2 Germany German Shepherd Dog Big
12 Australia Jack Russell Terrier Small
4 Germany Rottweiler Big
9 Switzerland Saint Bernard Dog Big
5 USA Siberian Husky Medium

In order to get around this limitation of ROWNUM for ranking
a sort order, other than taking a look at my last
Just SQL’ article
, you can use the following subquery to first
order the rows and then assign a ROWNUM to the rows. What you should notice is
that ROWNUM is assigned and dependent upon the order in which a row is selected
from a table or ordered from within a subquery of a SQL statement. Sometimes an
index may be employed to enforce the order of selection but should not be
relied upon as statistics or data patterns may change that render the index useless.


SQL> SELECT rownum, country, breed, breed_size
FROM (SELECT country, breed, breed_size FROM dog_origin ORDER BY breed)
ROWNUM COUNTRY BREED BREED_SIZE
———- ————— ————————- ——————–
1 USA Alaskan Malamute Medium
2 USA American Bulldog Big
3 Australia Australian Cattle Dog Medium
4 Switzerland Bernese Mountain Dog Big
5 Country Breed Breed_size
6 Germany Dobermann Big
7 Switzerland Entlebuch Cattle Dog Medium
8 Germany German Shepherd Dog Big
9 Australia Jack Russell Terrier Small
10 Germany Rottweiler Big
11 Switzerland Saint Bernard Dog Big
12 USA Siberian Husky Medium

So now, we can ask, and provide, a query to return the top-3
breeds in alphabetical order.


SQL> SELECT rownum, country, breed, breed_size
FROM (SELECT country, breed, breed_size FROM dog_origin ORDER BY breed)
WHERE rownum < 4
ROWNUM COUNTRY BREED BREED_SIZE
———- ————— ————————- ——————–
1 USA Alaskan Malamute Medium
2 USA American Bulldog Big
3 Australia Australian Cattle Dog Medium

When using the ROWNUM, one must be cautious when trying to
evaluate the ROWNUM to a variable and the greater than (>) or equality (=)
predicate. For instance, the following query produces a result set where the
ROWNUM is equal to 1.


SQL> SELECT * FROM dog_origin WHERE rownum = 1;
COUNTRY BREED BREED_SIZE
————— ————————- —————–
Country Breed Breed_size

However, when we issue the following two SQL statements
there is returned NO ROWS!


SQL> SELECT * FROM dog_origin WHERE rownum = 2;
no rows selected
SQL> SELECT * FROM dog_origin WHERE rownum > 1;
no rows selected

No rows are returned because the rownum is evaluated during
row retrieval and assigned if passing the WHERE clause test. The first row
returned is assigned a rownum of 1, it does not pass either test (equal to 2 or
greater than 1), and so is not part of the result set. The next row returned is
again assigned the rownum of 1 and again does not pass the test. This happens
for every row and thus no rows are returned.

ROW_NUMBER

Do not confuse the previous ROWNUM with the function
ROW_NUMBER. ROWNUM is a true pseudocolumn while ROW_NUMBER is a function that
also assigns a unique number to a row, based on an order by clause. This is the
true method, in Oracle, to produce a ranking of rows from a SQL statement. An
example is presented here only to contrast the previous ROWNUM method and
because it is a problem that faces many SQL practitioners. It is much easier to
understand then implementing a subquery.


SQL> SELECT rownum, country, breed, breed_size, row_number()
OVER (ORDER BY breed) as row_number
FROM dog_origin
ROWNUM COUNTRY BREED BREED_SIZE ROW_NUMBER
———- ————— ————————- ———- ———-
6 USA Alaskan Malamute Medium 1
7 USA American Bulldog Big 2
11 Australia Australian Cattle Dog Medium 3
8 Switzerland Bernese Mountain Dog Big 4
1 Country Breed Breed_size 5
3 Germany Dobermann Big 6
10 Switzerland Entlebuch Cattle Dog Medium 7
2 Germany German Shepherd Dog Big 8
12 Australia Jack Russell Terrier Small 9
4 Germany Rottweiler Big 10
9 Switzerland Saint Bernard Dog Big 11
5 USA Siberian Husky Medium 12

Both ROWID and ROWNUM have been around Oracle for quite
some time. They have been used for many purposes from assigning unique keys to
rows during the modeling process to maintenance tasks that allow you to ‘tag’
rows that need deletion or other updates applied. Understanding how to use
these two pseudocolumns and their limitations will enable you to use them with
more efficiency and exactness in your daily activities.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles