6.3 Pseudocolumns

---

There are some ways in which pseudocolumns can be used to increase performance.

6.3.1 Sequences

A sequence is often used to create unique integer identifiers as primary keys for tables. A sequence is a distinct database object and is accessed as sequence.NEXTVAL and sequence.CURRVAL. Using the Accounts schema Supplier table we can show how a sequence is an efficient method in this case.

EXPLAIN PLAN SET statement_id='TEST' FOR       INSERT INTO supplier (supplier_id, name, ticker)       VALUES(supplier_seq.NEXTVAL,'A new supplier', 'TICK');     Query                            Cost     Rows     Bytes ------------------------------   ----   ------   ------- INSERT STATEMENT on                 1       11       176  SEQUENCE on SUPPLIER_SEQ     EXPLAIN PLAN SET statement_id='TEST' FOR       INSERT INTO supplier (supplier_id, name, ticker)       VALUES((SELECT MAX(supplier_id)+1       FROM supplier), 'A new supplier', 'TICK');     Query                            Cost     Rows     Bytes ------------------------------   ----   ------   ------- INSERT STATEMENT on                 1       11       176

The query plan above is the same. There is a problem with it. Notice that a subquery is used to find the next SUPPLIER_ID value. This subquery is not evident in the query plan. Let's do a query plan for the subquery as well.

EXPLAIN PLAN SET statement_id='TEST' FOR       SELECT MAX(supplier_id)+1 FROM supplier;     Query                            Cost     Rows     Bytes ------------------------------   ----   ------   ------- 1. SELECT STATEMENT on              2        1         3 2.  SORT AGGREGATE on 1 3 3.   INDEX FULL SCAN (MIN/MAX)       on XPK_SUPPLIER               2     3874     11622

We can see that the subquery will cause extra work. Since the query plan seems to have difficulty with subqueries it is difficult to tell the exact cost of using the subquery. Use sequences for unique integer identifiers; they are centralized, more controllable, more easily maintained, and perform better than other methods of counting.

6.3.2 ROWID Pointers

A ROWID is a logically unique database pointer to a row in a table. When a row is found using an index the index is searched. After the row is found in the index the ROWID is extracted from the index and used to find the exact logical location of the row in its respective table. Accessing rows using the ROWID pseudocolumn is probably the fastest row access method in Oracle Database since it is a direct pointer to a unique address. The downside about ROWID pointers is that they do not necessarily point at the same rows in perpetuity because they are relative to datafile, tablespace, block, and row. These values can change. Never store a ROWID in a table column as a pointer to other tables or rows if data or structure will be changing in the database. If ROWID pointers can be used for data access they can be blindingly fast but are not recommended by Oracle Corporation.

6.3.3 ROWNUM

A ROWNUM is a row number or a sequential counter representing the order in which a row is returned from a query. ROWNUM can be used to restrict the number of rows returned. There are numerous interesting ways in which ROWNUM can be used. For instance, the following example allows creation of a table from another, including all constraints but excluding any rows. This is a useful and fast method of making an empty copy of a very large table.

CREATE TABLE tmp AS SELECT * FROM generalledger WHERE    ROWNUM < 1;

One point to note is as in the following example. A ROWNUM restriction is applied in the WHERE clause. Since the ORDER BY clause occurs after the WHERE clause the ROWNUM restriction is not applied to the sorted output. The solution to this problem is the second example.

SELECT * FROM customer WHERE ROWNUM < 25 ORDER BY    name;     SELECT * FROM (SELECT * FROM customer ORDER BY name) WHERE    ROWNUM < 25;

---