Indexes, Buffer Cache and More

In April's session we looked at the impact of reading old "cold" data into the buffer cache. We also reviewed index maintenance and discussed when - if ever - you should rebuild an index (hint: the answer's almost always never).

Highlights include:

01:20 - The impact of reading old data into the buffer cache
11:30 - Using direct path reads & writes to load data into the PGA instead of buffer cache
14:10 - Using parallel DML to do direct path operations; challenges with doing this
21:45 - Avoid moving data by using partition exchange
27:40 - Recap of techniques to reduce buffer cache contention

30:40 - Are index rebuilds necessary?
31:25 - Overview of B-tree index structure
33:30 - B-tree index maintenance demo using https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
42:20 - When is an index rebuilt useful?
45:00 - Using coalesce to reclaim space in an index
58:00 - Using an index rebuild to change its physical properties; e.g. move to a new tablespace or change compression level
1:03:00 - Recap of index rebuilding

AskTOM Office Hours offers free, monthly training and tips on how to make the most of Oracle Database, from Oracle product managers, developers and evangelists.

https://asktom.oracle.com/

Oracle Developers portal: https://developer.oracle.com/
Sign up for an Oracle Cloud trial: https://cloud.oracle.com/en_US/tryit