Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Sep 15, 2005

Just SQL Part VI - Two Famous Pseudocolumns

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM