Database Reference
In-Depth Information
A good example demonstrating such an implementation is offered by one of Oracle's tools: SQL Developer. For
instance, when SQL Developer displays data, it gets both the data itself as well as the rowids. For the
emp
table of the
scott
schema, the tool executes the following query. Notice that the first column in the
SELECT
clause is the rowid:
SELECT ROWID,"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "SCOTT"."EMP"
Later, the rowid can be used to access each specific row directly. For example, if you open the Single Record View
dialog box (see Figure
13-11
), edit the
comm
column, and commit the modification, the following SQL statement is
executed. As you can see, the tool uses the rowid to reference the modified row instead of the primary key (thus saving
the overhead of reading several blocks from the primary key index):
UPDATE "SCOTT"."EMP" SET COMM=:sqldevvalue WHERE ROWID = :sqldevgridrowid
Figure 13-11.
The SQL Developer's dialog box is used to display, browse, and edit data
Using the rowid is very good from a performance point of view because the row can be accessed directly, without
the help of a secondary access structure such as an index. The following is an execution plan that is related to such a
SQL statement:
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | EMP |
| 2 | TABLE ACCESS BY USER ROWID| EMP |
--------------------------------------------
