Abstract

This two day seminar is a serious in-depth study of various Oracle Index Structures. It examines how structures such as B-Tree Indexes, Reverse Indexes, Function-Based Indexes, Bitmaps Indexes, Bitmap Join Indexes and Partitioned Indexes are implemented by Oracle. It discusses how indexes behave, how indexes should be maintained and how the Cost Based Optimizer determines their use (or otherwise).

It also dispels many common myths and misconceptions associated with Oracle indexes by proving index behavior with clearly defined case studies and repeatable test examples. By the end of the seminar, attendees will have a solid understanding of Oracle Indexes and knowledge of appropriate maintenance and index usage strategies.
 

Day 1

1. Introduction To Oracle Index Structures

• Summary of Oracle index structures and options
• Summary of Oracle index myths
• Summary of when to consider creating an index

2. Basic B-Tree Indexes

• Overview of B-Tree index structures
• Myth: Oracle Indexes become unbalanced
• Treedump trace event
• Exploring index block dumps
• Index block header
• Branch blocks
• Leaf blocks
• Explore how updates impact index entries
• Reverse Key Indexes
• Index statistics: What do they really mean.
• Myth: High Clustering Factor an index rebuild criteria
• How to appropriately set PCTFREE 
• Myth: Null column values are not indexed
• Index block split internals
• Myth: Deleted index space not reused 
• Index fragmentation issues
• Myth: Indexes require regular rebuilding
• Myth: Index rebuilds are cheap, unobtrusive and safe
• How to determine which rare indexes require a rebuild
• Impact of block splitting on inserts
• Myth: Indexes with a height > X require a rebuild
• Differences Between Rebuild, Coalesce and Shrink commands

3. Indexes and the Cost Based Optimizer

• Overview of how the CBO costs index accesses
• Myth: Indexes should be used if they retrieve X % of rows
• Myth: Index accesses is nearly always preferable to a Full Table Scan
• Index related access paths
• Index related hints
• Index related parameters
• Index related wait events
• Myth: Put most discriminating column first in concatenated index
• Index Compression
• Myth: Skip Index Access Path means column order no longer an issue
• Why isn’t Oracle using my index ?
• Technique to force index use without updating source code
• Technique to improve performance when both Index Scan and Full Table Scan too expensive
• Function-Based Indexes
• Techniques to make B-Tree indexes efficiently access low-cardinality columns or specific columns only
• How constraints can be crucial to appropriate index use
• Virtual Indexes: How to pretend to use an index that’s not really there 

• Overview of Bitmap index structures
• Exploring Bitmap block dumps
• How data distribution impacts Bitmap indexes
• Why OLTP databases and Bitmap Index don’t mix too well
• Myth: Bitmap indexes should only be used for low cardinality columns
• Myth: Bitmap indexes improve low cardinality search performance
• When and how to actually use Bitmap indexes
• Star Transformations
• Bitmap Join Index

5. Partitioned Indexes

• Overview of Partitioned Indexes
• Myth: Local indexes out perform Global Indexes
• Hash Partitioned Global Indexes vs. Reverse Index

6. Miscellaneous Index Topics

• Myth: Small tables don’t really need an index
• Myth: Separating indexes from tables improves performance
• Myth: Index access used in COUNT(1) more efficient than COUNT(*)
• Myth: Primary and Unique constraints require a Unique Index
• Myth: All Foreign Keys should be indexed, always
• Recommendations for further reading