Week 5 - Slow Indexes and Transactions

Slow Index

The author explains that despite the efficiency of index look up as indexes are supposed to speed up performance of query they can still have slow performance results. The article focuses on three reasons why this might be true. The first reason is because of the leaf node chain as searching a value not only travels through the index tree but through the leaf nodes to find the result. The second instance is that of accessing a table. The data is scattered across many table blocks, so having to access the table again is required for each matching data. Lastly it's the combination of these steps from the leaf node chain to the accessing and fetching of data. All of these involve accessing numerous blocks and slows down the process. So to summarize these ideas up, the author is saying that a slow index as they call a myth involves not only traversing an index tree but rather when the process requires following a leaf node chain through INDEX RANGE SCAN and accessing the actual table data for each record through TABLE ACCESS BY INDEX ROWID. The slowness occurs through the steps of the look up, it has to do more work such as following the trails of data and finding information from different spots of the table. 



Transactions

Throughout the week I have learned about the intricacies of transactions. One of the biggest things I have learned was the ACID properties for transactions. ACID is an acronym for Atomic, Consistent, Isolated and Durability. These are properties of a transaction which are the sequence of operations that a database must complete or reject. In order for a transaction to be successful they have to be atomic which involves that either all or none of the operations will be executed and applied to the database. The database must return to the state it was in when it was not completed or partially completed. The transaction has to have completed results. The next thing is it has to be consistent, so all the transactions but maintain the constraints and rules to all relational data. Any violated rules will result in a rollback or a return to the state it was in before the transaction. Next the transaction needs to be isolated which involves no interference from other transactions. This is an example of concurrent transactions which is when there are multiple transactions executing simultaneously, they need to be managed properly through a certain system called a concurrency system. Lastly is the durability which involves transaction failures, system failures, and recovery of data. It makes sure that data must be committed after a transaction.