Oracle rowid and postgresql ctid

Oracle rowid and postgresql ctid first introduces oracle rowid, the physical identification SQL> select rowid from book_info where rownum <= 1; ROWID ---------------- aaaqtjaaaaaoaaaa ROWID format: data Object number file number block number row number Oooooooo fff bbbbbb rrr from this we can see that AAAQTJ is the data object number, AAa is the file number, AAAAoa is the block number, AAA is the row number select rowid, substr (rowid,) "OBJECT", substr (rowid,) "FILE", substr (rowid,) "BLOCK", substr (rowid) "ROW" from book_info where rownum <= 5; query Output 64-bit encoding value: rowid object file block row pruning ---------- AAAQTJ AAa AAAAoC AAAQTJ AAa aaaaaaoc AAa AAAQTJ AAa aaaaaaoc AAF: SQL> select dbms_rowid.rowid_object (rowid) object _ Id, struct (rowid) file_id, dbms_rowid.rowid_block_number (rowid) block_id, dbms_rowid.rowid_row_number (rowid) num from book_info where rownum <= 5; OBJECT_ID FILE_ID BLOCK_ID NUM ---------- 66761 26 2568 0 66761 26 2568 1 66761 26 2568 2 66761 26 2568 3 66761 26 2568 4 get rowid function: create or replace function get_rowid (l_rowid in varchar2) return varc Optional varchar2 (200); rowid_type number; object_number number; relative_fno number; block_number number; row_number number; begin struct (l_rowid, rowid_type, object_number, struct, block_number, row_number); Comment: = 'object # is: '| to_char (object_number) | chr (10) | 'relative _ fno is:' | to_char (relative_fno) | chr (10) | 'block number is: '| to_char (block_number) | Chr (10) | 'row number is: '| to_char (row_number); return ls_my_rowid; end;/select get_rowid (rowid) from book_info where rownum <= 1; object # is: 66761Relative_fno is: 26 Block number is: 2586Row number is: 0 of course, the most common is to use rowid to remove duplicates: Find duplicate data: select. rowid,. * from table name a where. rowid! = (Select max (B. rowid) from table name B where. field 1 = B. field 1 and. field 2 = B. field 2) delete duplicate data: delete from table name a where. rowid! = (Select max (B. rowid) from table name B where. field 1 = B. field 1 and. field 2 = B. field 2) You can use the distinct function if all rows are repeated. The following describes ctidtestuser = # select ctid, * from t1 limit 1; ctid | a ------- + ----------- (100000000) | and oracle rowid are similar to physical fields and are automatically generated, however, the structure is different from that of the oracle rowid. We can see that the (blockid, itemid) ctid also changes after the data is changed. Remove duplicate data using ctid: create a test table and insert data: testuser = # CREATE table t2 (id int, name varchar (20); create TABLEtestuser = # insert into t2 values (1, 'apple'); INSERT 0 1 testuser = # insert into t2 values (1, 'apple'); INSERT 0 1 testuser = # insert into t2 values (1, 'apple'); INSERT 0 1 testuser = # insert into t2 values (2, 'Orange '); INSERT 0 1 testuser = # insert into t2 values (2, 'Orange '); INSERT 0 1 testuser = # insert into t2 values (2, 'Orange'); INSERT 0 1 testuser = # insert into t2 values (2, 'Orange '); INSERT 0 1 testuser = # insert into t2 values (3, 'Banana'); INSERT 0 1 testuser = # insert into t2 values (3, 'banana '); INSERT 0 1 testuser = # select * from t2; id | name ---- + -------- 1 | apple 1 | apple 1 | apple 2 | orange 2 | orange 2 | orange 2 | orange 3 | banana 3 | banana query duplicate data: testuser = # select ctid, * from t2 where ctid in (select min (ctid) from t2 group by id); ctid | id | name ------- + ---- + -------- (0, 1) | 1 | apple () | 2 | orange () | 3 | banana: testuser = # delete from t2 where ctid not in (select min (ctid) from t2 group by id); DELETE 6 testuser = # select * from t2; id | name ---- + -------- 1 | apple 2 | orange 3 | banana (3 rows)