ROWNUM and ROWID Psuedo Columns usage in Oracle SQL

Importance of Psuedo Columns 

Frequently used Psuedo Columns in Oracle SQL:

1.ROWID

2.ROWNUM

3.CURRVAL

4.NEXTVAL

5.LEVEL CONNECT BY 

Q:How to display rowids for any table in Oracle SQL?

SELECT ROWID,EMPNO,ENAME,SAL FROM EMP; 

ROWID is a physical address in DB.

It is used to delete duplicate records.

It contains 18-Digits(alpha-numeric.)-Base-64 String

Example :- 

CREATE TABLE EMP77 (

    ENO   NUMBER(4),

    ENAME VARCHAR2(50),

    SAL   NUMBER(10, 2)

);

INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (101, 'ARJUN', 55000);

INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (102, 'BEATRICE', 62000);

INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (103, 'CHENG', 48500);

INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (104, 'DAHLIA', 71000);

INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (105, 'ELIAS', 53000);

INSERT INTO EMP77 (ENO, ENAME, SAL) VALUES (101, 'ARJUN', 55000);

ENO ENAME SAL

101 ARJUN 55000

102 BEATRICE 62000

103 CHENG 48500

104 DAHLIA 71000

105 ELIAS 53000

101 ARJUN 55000

MIN(ROWID): Returns the physical address of the row that was  inserted earliest or sits at the lowest physical point in the data blocks.

select MIN(ROWID) from emp77;

Result: AAAJxaAAEAAAAOdAAA

MAX(ROWID): Returns the physical address of the row that sits at the highest physical point.

select MIN(ROWID) from emp77;

Result:AAAJxaAAEAAAAOdAAE

DELETE FROM EMP77 WHERE ROWID LIKE '%AAF'; --it deletes duplicate row.

Note: Deleting duplicates row-by-row is difficult if table contains lakhs of duplicate rows , so we need to use sub-queries to delete all duplicate rows from table.

METHOD 1:- Using Co-related Sub-query.

--------

 DELETE 

  FROM EMP77  e

  WHERE ROWID <> (SELECT MIN(ROWID)

                   FROM EMP77

                   WHERE ENO = e.ENO

                   ) ;

METHOD 2 :- Using Single Row Sub-query

---------      

DELETE 

FROM EMP77

WHERE ROWID NOT IN (SELECT MIN(ROWID)

                    FROM EMP77

                   GROUP BY ENO,ENAME,SAL);

Expected Interview questions

 1. What is the fundamental difference between ROWID and ROWNUM?

2. Can you use WHERE ROWNUM > 1 to get all records except the first?

3. How do you delete duplicate rows using ROWID?

4. Does ROWNUM change if you use an ORDER BY clause?

5. When does a ROWID change?