. Here is the ROWID of one row in Oracle:<\/p>\n There’s enough information here to get directly to the block with file_name and offset:<\/p>\n The ROWID also contains the index of the row within the block’s row directory:<\/p>\n And the TID of similar row in Postgres:<\/p>\n The file is known from the table, as there is only one file per table:<\/p>\n The blocksize is common for the whole database:<\/p>\n Then the block is at offset 142+8192=8334. We have seen in the previous post that we can select using the ROWID\/TID and Oracle and Postgres behave the same: only one block to read, cost estimation based on one random read:<\/p>\n Different units but same signification: cost=1 for Oracle is for random reads, cost=1 for Postgres is for sequential reads and random reads are estimated to cost=4:<\/p>\n Now I’m updating this row, changing the column X which contains 1000 ‘x’ characters to 1000 ‘y’ characters:<\/p>\n In addition to the access to the block (1 buffer) the update had to read 3 additional buffers. There are no indexes on this updated column and then Oracle has no additional maintenance to do. One buffer is the table block to update (the TABLE ACCESS BY USER ROWID was a consistent get, the update needs the current version of the block). <\/p>\n Additional buffers are from the UNDO tablespace for MVCC (Multi Version Concurrency Control). It is the first modification in my transaction and then has to update the transaction table and undo segment, which is why we see 2 additional buffers. Another update within the same transaction reads only two buffers in total:<\/p>\n Only the table blocks are read: one consistent read as of the beginning of the query (or the transaction if in serializable isolation level) and one for the current block. Oracle has an optimization called In-Memory UNDO to avoid frequent access undo blocks.<\/p>\n There are no further re-visits needed. Oracle may choose to come back at commit if it can be done quickly (few blocks still in buffer cache) but that’s not required. The block can stay like this for years without the need to read it again for cleanup. If another session has to read it, then cleanup may be done by this session.<\/p>\n Here is the same update in Postgres:<\/p>\n The Tid Scan is the same as for the select. Then the update has read 5 blocks and modified 3 of them. The update in Postgres is processed as a delete+insert. Here is my guess about those numbers. The new version is inserted, in a new block if there is no free space in the same block. The old version is updated. And the index must be maintained. Those are 3 blocks to modify. Here, the row was directly accessed through its TID. But we must find the index entry. The row contains the index value, and then an index scan is possible: two block reads for this small index having one branch only.<\/p>\n I said that with Oracle the row is updated in-place and doesn’t need further cleanup. If I run the same SELECT as the one I did before the UPDATE, I still have only one block to read:<\/p>\n In Postgres, because the update was processed as insert+delete, running the same also reads only one block, but it returns no rows:<\/p>\n The new version is in another block, then the TID to find it is different:<\/p>\n There was not enough space for another version of the whole row within the same block. Free space was found in the last block (1428). Of course, this is why the index was updated even if the indexed column did not change: it had to address a different block.<\/p>\n Let’s query with the new TID:<\/p>\n Only one buffer read. However, as we have seen with the Index Only Scan<\/a>, there is a need for cleanup to avoid Heap Fetches. There are also the old tuples that should be removed later or the updated tables and indexes grow forever.<\/p>\n
\nIn the previous post<\/a> we have seen the cheapest way to get one row, reading only one block from its physical location. But that’s the optimal case where the row has not moved. I’ll (nearly) conclude this series about access path with an update.
\n<\/p>\nROWID in Oracle<\/h3>\n
\nselect rowid from demo1 where n=1000;\nROWID\n------------------\nAAAR4WAAMAAAAEaAAF\n<\/code><\/pre>\nselect file_name,dbms_rowid.rowid_block_number('AAAR4WAAMAAAAEaAAF')*block_size offset\n from dba_data_files join dba_tablespaces using(tablespace_name)\n where file_id=dbms_rowid.rowid_to_absolute_fno('AAAR4WAAMAAAAEaAAF','DEMO','DEMO1');\n \nFILE_NAME OFFSET\n---------------------------------------- ----------\n\/u01\/oradata\/CDB1A\/PDB\/users01.dbf 2310144\n<\/code><\/pre>\n\nselect dbms_rowid.rowid_row_number('AAAR4WAAMAAAAEaAAF') from dual;\n \nDBMS_ROWID.ROWID_ROW_NUMBER('AAAR4WAAMAAAAEAAAF')\n-------------------------------------------------\n 5\n<\/code><\/pre>\nTID in Postgres<\/h3>\n
\nselect ctid from demo1 where n=1000;\n ctid\n---------\n (142,6)\n<\/code><\/pre>\n\nshow data_directory;\n data_directory\n----------------------------\n \/usr\/share\/postgresql\/data\n \nselect pg_relation_filepath('demo1');\n pg_relation_filepath\n----------------------\n base\/16437\/125852\n<\/code><\/pre>\n\nshow block_size;\n block_size\n------------\n 8192\n<\/code><\/pre>\n
\nWithin the block, the row is at index 6.<\/p>\nSELECT<\/h3>\n
\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 8mj3ms08x0sfh, child number 0\n-------------------------------------\nselect \/*+ *\/ a from demo1 where rowid='AAAR4WAAMAAAAEaAAF'\n-----------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |\n-----------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 1 (100)| 1 |00:00:00.01 | 1 |\n| 1 | TABLE ACCESS BY USER ROWID| DEMO1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 1 |\n-----------------------------------------------------------------------------------------------------------\nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n 1 - \"A\"[NUMBER,22]\n<\/code><\/pre>\n\nexplain (analyze,verbose,costs,buffers) select a from demo1 where ctid='(142,6)' ;\n QUERY PLAN\n------------------------------------------------------------------------------------------------------\n Tid Scan on public.demo1 (cost=0.00..4.01 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)\n Output: a\n TID Cond: (demo1.ctid = '(142,6)'::tid)\n Buffers: shared hit=1\n Planning time: 0.358 ms\n Execution time: 0.016 ms\n<\/code><\/pre>\nOracle UPDATE<\/h3>\n
\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID gpqv4k6m1q20y, child number 0\n-------------------------------------\nupdate \/*+ *\/ demo1 set x=lpad('y',1000,'y') where rowid='AAAR4WAAMAAAAEaAAF'\n------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |\n------------------------------------------------------------------------------------------------------------\n| 0 | UPDATE STATEMENT | | 1 | | 1 (100)| 0 |00:00:00.01 | 4 |\n| 1 | UPDATE | DEMO1 | 1 | | | 0 |00:00:00.01 | 4 |\n| 2 | TABLE ACCESS BY USER ROWID| DEMO1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 1 |\n------------------------------------------------------------------------------------------------------------\nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n 2 - (upd=2) ROWID[ROWID,10], \"X\"[VARCHAR2,1000]\n<\/code><\/pre>\n\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID gpqv4k6m1q20y, child number 0\n-------------------------------------\nupdate \/*+ *\/ demo1 set x=lpad('z',1000,'z') where rowid='AAAR4WAAMAAAAEaAAF'\n------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |\n------------------------------------------------------------------------------------------------------------\n| 0 | UPDATE STATEMENT | | 1 | | 1 (100)| 0 |00:00:00.01 | 2 |\n| 1 | UPDATE | DEMO1 | 1 | | | 0 |00:00:00.01 | 2 |\n| 2 | TABLE ACCESS BY USER ROWID| DEMO1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 1 |\n------------------------------------------------------------------------------------------------------------\nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n 2 - (upd=2) ROWID[ROWID,10], \"X\"[VARCHAR2,1000]\n<\/code><\/pre>\nPostgres UPDATE<\/h3>\n
\nexplain (analyze,verbose,costs,buffers) update demo1 set x=lpad('y',1000,'y') where ctid='(142,6)' ;\n QUERY PLAN \n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n Update on public.demo1 (cost=0.00..4.01 rows=1 width=46) (actual time=0.214..0.214 rows=0 loops=1)\n Buffers: shared hit=6 dirtied=3\n -> Tid Scan on public.demo1 (cost=0.00..4.01 rows=1 width=46) (actual time=0.009..0.009 rows=1 loops=1)\n Output: n, a, 'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy'::text, ctid\n TID Cond: (demo1.ctid = '(142,6)'::tid)\n Buffers: shared hit=1\n Planning time: 0.405 ms\n Execution time: 0.232 ms\n<\/code><\/pre>\nSELECT again<\/h3>\n
\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 8mj3ms08x0sfh, child number 0\n-------------------------------------\nselect \/*+ *\/ a from demo1 where rowid='AAAR4WAAMAAAAEaAAF'\n-----------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |\n-----------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 1 (100)| 1 |00:00:00.01 | 1 |\n| 1 | TABLE ACCESS BY USER ROWID| DEMO1 | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 1 |\n-----------------------------------------------------------------------------------------------------------\nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n 1 - \"A\"[NUMBER,22]\n<\/code><\/pre>\n\nexplain (analyze,verbose,costs,buffers) select a from demo1 where ctid='(142,6)' ;\n QUERY PLAN\n------------------------------------------------------------------------------------------------------\n Tid Scan on public.demo1 (cost=0.00..4.01 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1)\n Output: a\n TID Cond: (demo1.ctid = '(142,6)'::tid)\n Buffers: shared hit=1\n Planning time: 0.442 ms\n Execution time: 0.028 ms\n<\/code><\/pre>\n\nselect ctid from demo1 where n=1000;\n ctid\n----------\n (1428,5)\n(1 row)\n<\/code><\/pre>\n\nexplain (analyze,verbose,costs,buffers) select a from demo1 where ctid='(1428,5)' ;\n QUERY PLAN\n------------------------------------------------------------------------------------------------------\n Tid Scan on public.demo1 (cost=0.00..4.01 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)\n Output: a\n TID Cond: (demo1.ctid = '(1428,5)'::tid)\n Buffers: shared hit=1\n Planning time: 0.449 ms\n Execution time: 0.023 ms\n<\/code><\/pre>\n