4 ms·
An Oracle story. Read queries can also write to disk due to the way Oracle handles consistency. Information on which transactions last changed particular rows i
by ComodoHacker 5y ago
An Oracle story. Read queries can also write to disk due to the way Oracle handles consistency. Information on which transactions last changed particular rows in a table gets stored with the rows. Read query then looks up those transactions to see whether they were committed or not. If not, read query skips rows they touched (and looks up their previous versions). If they were, that transaction info gets cleared up, so next queries don't have to look it up again.
This is simplified explanation, but the point is the process executing a read query is often best positioned to do this maintenance work. This decision trades off a little latency for throughput.
- sokols 5y agoSimilarly, in Oracle, Temporary Tablespaces might be utilized when a query uses sort operations for example.
- ccleve 5y agoPostgres does something similar. Every time a query returns a row the system must do a lookup into a visibility map to see if the row is visible to the current transaction. It can also check if it's a completely dead row. If there is an index involved, then on the next row fetch Postgres will report to the index whether the last row was dead or not. The index can elect to delete the row reference, which means a write during a select statement.
- chasil 5y agoThis Oracle behavior is called "block cleanout," and makes it unnecessary for a transaction to touch all effected blocks when it commits or rolls back - some future transaction will do this. Oracle also has two init.ora parameters, SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE, that are similar to the WORK_MEM mentioned in the subject article. As far as I know, SORT_AREA_SIZE is global, and differing values cannot be assigned to roles.