Oracle SQL: ROWID Pseudocolumn

Again, one of my students asked me a question during the Oracle 11g Introduction to SQL class.

"How would you delete only 1 record of 2 identical records, all with same column values and there is no PK (Primary Key) to uniquely identify them?"

I told her that we can make use of the ROWID pseudocolumn.The ROWID pseudocolumn returns the physical address of the row. Oracle Database rowid values contain information necessary to locate a row:

• The data object number of the object
• The data block in the datafile in which the row resides
• The position of the row in the data block (first row is 0)
• The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid. Values of the ROWID pseudocolumn have the datatype ROWID or UROWID.

Rowid values have several important uses:

• They are the fastest way to access a single row.
• They can show you how the rows in a table are stored.
• They are unique identifiers for rows in a table.

You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.

The ROWID Demo

To simulate the scenario, I created a sample table named Test:

SQL> CREATE TABLE Test
(test varchar2(10));

Then, I inserted two identical records with no primary key

SQL> INSERT INTO Test
VALUES ('Test1');

SQL> INSERT INTO Test
VALUES ('Test1');

Then, selected the rowid of two identical records

SQL> SELECT rowid, test
FROM Test;

Then, the query returned

ROWID                  TEST
--------------------   ---------------
AAASDcAAEEAADOMAAA     Test1
AAASDcAAEEAADOMAAB     Test1

Then, I tried to delete one of the two identical records

SQL> DELETE FROM Test
WHERE rowid = 'AAASDcAAEEAADOMAAA';

Then, the result says 1 row deleted

Yey, we've successfully deleted one of the two identical records. ^_~

Oracle SQL: Traditional Outer Join Syntax

I was teaching Oracle 11g SQL Fundamentals and PL/SQL Fundamentals course when one of my students asked me a startling question right after I've discussed JOIN topic.

"What's the difference of using the JOIN keyword and using the (+) for joining of tables?"

Then, he showed me a SQL script using the (+) syntax and explained to me that his mentor taught him to use this syntax and that they are also using this SQL syntax in all their systems and applications.

For a while, I wondered... because I've never encountered this (+) syntax with Oracle 9i, 10g and 11g books and course materials. I told him, maybe it was the old JOIN syntax being used by earlier versions of Oracle.

To support my wild guess... that it is really an old JOIN syntax still supported by Oracle, I started to google about it and I landed on Oreilly's Oracle SQL*Plus Pocket Reference, 2nd Edition, by Jonathan Gennick, sample chapter excerpt: http://oreilly.com/catalog/orsqlpluspr2/chapter/ch01.html


According to the book, the (+) syntax for joining tables is an old syntax for outer joins until Oracle 8i. An outer join returns rows for one table, even when there are no matching rows in the other. You specify an outer join in Oracle by placing a plus sign (+) in parentheses following the column names from the optional table in your WHERE clause. For example:

SELECT ut.table_name, uc.constraint_name
FROM user_tables ut, user_constraints uc
WHERE ut.table_name = uc.table_name(+)

The (+) after uc.table_name makes the user_constraint table optional. The query returns all tables, and where there are no corresponding constraint records, Oracle supplies a null in the constraint name column. Full outer join is not supported using the (+) syntax.

At the release of Oracle 9i and later versions, full outer join is already supported using the JOIN syntax. 

SELECT ut.table_name, uc.constraint_name

FROM user_tables ut FULL OUTER JOIN user_constraints uc

ON ut.table_name = uc.table_name

Although you could still use the old (+) syntax for outer joins for backward compatibility, Oracle strongly encourage to use the new JOIN syntax for full outer join support.