RowId Vs Index

and the winner is dbarepublic... I am kidding right?

With this article, we will find out who is the real champion among INDEX and ROWID? Many of you have guessed it; the winner is Index because we use Index to speed up the query search.
This is a logical thinking when performing a SQL tuning. Let's re-cap briefly on Index and RowID, you may skip if you know already know their definition, purpose and when to use them.

RowID: A Pseudo-column is a virtual column that behaves like a table column, but it is not stored in a table. Rowid returns the address of the row and it contains information to locate a row. This also uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluseter can have the same rowid. The rowid is of datatype ROWID or UROWID.

You can SELECT but cannot INSERT, UPDATE or DELETE these values. For each row in the database, the ROWID pseudo-columns returns the address of the row. Oracle Database ROWID values contain information necessary to locate the row. ROWID values have several uses. They are the FASTEST way to access a single row. It shows you how the rows in a table are stored and it uniquely identify for rows in a table. The ROWID may change if you delete and re-insert the row.

You cannot insert, update, or delete a value of the ROWID pseudo-column. The ROWID may change if the table row movement is enabled and the table is UPDATED, SHRINK, and FLASHBACK and Tablespace/table MOVE.

ROWID have several uses:

• It is the fastest way to access a single row. 
• It shows you how the rows in a table are stored. 
• It uniquely identifies rows in a table. 

ROWID is most frequently used for finding and removing duplicate records from a table. I use ROW id mostly for DML Operations like update/delete. What else do you use for beside these operations? Display ROWID:

SELECT ROWID, last_name 
   FROM employees
   WHERE dept = 'CIS';

The statement selects the address of all rows that contain data for employees in CIS department.
Index: Index is a database object which contains an entry of each record that appears in the indexed column to provide faster access of data. Indexes are used in a View or a Table. Knowing the concept of Index in detail is very crucial to database administrators, database developers, application developer, and anyone who likes to work with database. The proper use of Index makes your query and database perform faster. This may hurt a query performance and add overhead to database if not used properly. You can get more here on index here.

Why using RowID to SELECT, UPDATE, DELETE records are much faster compared to using indexed column? It is because Index first finds data and then pass a ROWID associated to the data and pulls the record.Lets prove that with example.
 PL/SQL Update using Index:

CREATE TABLE emp_temp AS
SELECT empno, salary FROM emp 
WHERE empno > 2000; 

SET serveroutput ON
SET timing ON
DECLARE
  CURSOR emp_temp_cur
  IS
    SELECT empno, salary FROM emp_temp;
  l_emp_temp_cur emp_temp_cur%ROWTYPE;
BEGIN
  OPEN emp_temp_cur;
  LOOP
    FETCH emp_temp_cur INTO l_emp_temp_cur;
    EXIT
  WHEN emp_temp_cur%NOTFOUND;
    UPDATE EMP
    SET salary  = (l_emp_temp_cur.salary + 4000.00)
    WHERE EMPNO = l_emp_temp_cur.empno;
  END LOOP;
  COMMIT;
  CLOSE emp_temp_cur;
END;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.023

PL/SQL Update using RowID:

CREATE TABLE emp_temp AS
SELECT rowid AS emp_rowid, salary, empno 
FROM emp;

SET serveroutput ON
SET timing ON
DECLARE
  CURSOR emp_temp_cur
  IS
    SELECT rowid, salary FROM emp_temp;
  l_emp_temp_cur emp_temp_cur%ROWTYPE;
BEGIN
  OPEN emp_temp_cur;
  LOOP
    FETCH emp_temp_cur INTO l_emp_temp_cur;
    EXIT
  WHEN emp_temp_cur%NOTFOUND;
    UPDATE EMP
    SET salary  =(l_emp_temp_cur.salary + 4000.00)
    WHERE rowid = l_emp_temp_cur.emp_rowid;
  END LOOP;
  COMMIT;
  CLOSE emp_temp_cur;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.016

....and the winner is RowID. Will you still Index when you know RowID is the legend?