Index Internals Seminar
Note: List of currently scheduled seminars can be found on the Public Appearances page.
Please contact me at richard.foote@bigpond.com for further information or availability.
I currently present a very in-depth 2 day Oracle Indexing Internals and Best Practices Seminar. There is also a condensed 1 day version of the Oracle Index Internals and Best Practices Seminar upon request.
Indexes are fundamental to every Oracle database and are crucial for optimal performance. However, there’s an incredible amount of misconception, misunderstanding and pure myth regarding how Oracle indexes function and should be maintained. This seminar examines most Oracle index structures and discusses in considerable detail how they function and how they should be used and maintained.
Introduction: Overview of Index Structures and Options
- When To Consider Creating a Particular Index
- When To Consider Various Options
- Overview Of Common Myths and Misconceptions
Session One: Introduction To B-Tree Indexes
- Understanding B-Tree Index Structure and Navigation
- B-Tree Index Structure Internals
- Tree Dumps
- Block Dump Examinations
- Study of DML Operations on Index Internals
- Study of Concurrent Transactions on Index Internals
Session Two: Index Statistics
- Gathering Index Statistics – Issues, Traps and Tricks
-
VALIDATE STRUCTURE study
- Study of Index Statistics including 11g Extended Statistics
- Indexes, Histograms and Densities
- System Statistics
- Clustering Factor – Issues, Traps and Tricks
- Index Height – Issues, Traps and Tricks
Session Three: Indexes and Constraints
- Indexing PK and Unique Constraints
- Indexing Options With Constraints
- Constraint Options – Deferrable, Novalidate, Rely
- NOT NULLs and Indexing Strategies
- Foreign Keys and Index Strategies
- Internals Between Unique and Non-Unique Indexes
- Disable and Novalidate Constraints – Issues Tricks and Traps
Session Four: Rebuilding, Coalescing and Shrinking Indexes
- Locking Implications
- PCTFREE – Issues, Tricks and Traps
- Index Block Split Internals
- Deleted Space Management
- Index Fragmentation Internals
-
Index Rebuild Criteria – The Real One
- Index Selectivity Issues
- Dangers Of Index Rebuilds
- Index Coalesce Internals
- Index Shrink Internals
- When to Rebuild or Coalesce or Shrink
Session Five: Indexes And The Cost Based Optimizer
- How CBO Costs Index Accesses
- How CBO Calculates Selectivity
- CBO and System Statistics
- Index vs. Full Table Scans
- CBO Worked Examples
- DBMS_XPLAN
- Index Access Paths
- Indexes and Sorting
- Index Hints
- Index Related Parameters
- Stored Outlines
- SQL Profiles
Session Six: Miscellaneous Index Tips, Tricks and Traps
- Why Isn’t Oracle Using My Index ?
- Concatenated Index Column Order Study
- Outliers and Other Statistic Anomalies
- Materialized Views
- Using B-Tree Indexes With Low Cardinality Columns
- Indexing NULL columns
- Index Compression
- Index Monitoring
- Indexing Small Tables
- Separating Indexes From Tables: Should We or Shouldn’t We
- Storing Indexes In Separate Large Block Tablespaces: Should We or Shouldn’t We
Session Seven: Linguistic Indexes
- Issues With Case Insensitive Searches
- Linguistic Index Parameters
- Linguistic Index Internals
- Creating Linguistic Indexes
- Case Insensitive Searches and Sorting
- Linguistic Index Restrictions
Session Eight: Additional Indexing Options
- Reverse Key Indexes
- Index Organised Tables
- Index Organised Table Secondary Indexes
- Function-Based Indexes
- 11g Virtual Columns
- Fake Indexes
- 11g Invisible Indexes
Session Nine: Partitioned Indexes
- Partitioning Options
- Partitioned Index Block Dumps
- Partitioning Statistics Study
- Partition Pruning
- Global Indexes (Partitioned / Non-Partitioned)
- Local Indexes (Prefixed / Non-Prefixed)
- Unique Partitioned Indexes
- Global vs. Local Indexes Study
- Partition Access Paths
- Partitioning Tricks
- Maintaining Partitioned Indexes
Session Ten: Bitmap Indexes
- Overview Of Bitmap Indexes
- Bitmap Index Block Internals
- Bitmap Index Size Considerations
- Bitmap Index Access Paths
- Bitmap Indexes: Are They Just For Low Cardinality Columns ?
- OLTP and Bitmap Indexes, Water and Oil
- When To Use Bitmap Indexes
- Star Transformations
- Bitmap-Join Indexes
- Bitmap Index Restrictions
