ROWNUM and ROWID Psuedo Columns usage in Oracle SQL
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?
Comments
Post a Comment