Index Internals Seminar
I resigned from Oracle in September 2017 and started my own independent company Richard Foote Consulting. As such I will again for running my “Oracle Indexing Internals and Best Practices” seminar.
Please contact me at richard@richardfooteconsulting.com for further information or upcoming seminars or visit my Seminar Dates page.
I run 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. Many applications and databases are suboptimal and run inefficiently primarily because an inappropriate indexing strategy has been implemented.
This seminar examines most available Oracle index structures/options and discusses in considerable detail how indexes function, how/when they should be used and how they should be maintained. A key component of the seminar is how indexes are costed and evaluated by the Cost Based Optimizer (CBO) and how appropriate data management practices are vital for an effective indexing strategy. It also covers many useful tips and strategies to maximise the benefits of indexes on application/database performance and scalability, as well as maximising Oracle database investments. Much of the material is exclusive to this seminar and is not generally available in Oracle documentation or in Oracle University courses.
This is a must attend seminar that would be of much benefit to not only DBAs, but also to Developers, Solution Architects and anyone else interested in designing, developing or maintaining high performance Oracle-based applications. The seminar is developed and personally delivered by Richard “Mr Index” Foote, a well-respected expert in Oracle Database technologies. All seminars are small class environments, with plenty of opportunity for attendees to ask questions specific to their particular environment.
For list of all upcoming seminars, see the Seminar Dates page.
All seminars include:
- Detailed course notes
- Tea/Coffee
- Lunch
Seminar Content (Subject To Minor Changes)
In no particular order, the following Oracle Indexing “myths” (or misleading generalisations) are discussed throughout my seminar and are still prevalent out there in the Oracle universe:
- Oracle B-Tree indexes become “unbalanced” over time and need to be rebuilt
- Deleted space in index is “deadwood” and over time requires index to be rebuilt
- If index height greater than ‘x’, it becomes inefficient and needs to be rebuilt
- If index grows to 2x its height, it is 2x more costly to use
- PCTFREE enables space for index entries to grow within current leaf block
- If index has a poor (very high) Clustering Factor, rebuild the index
- To improve the Clustering Factor, you have to rebuild the underling table
- Clustering Factor “as good as it gets” after gathering 100% estimate index statistics
- To improve performance, regularly rebuild indexes
- You never have to rebuild an index to improve performance
- Statistics from INDEX_STATS provides reliable metrics on when to rebuild an index
- If delete rows as reported in INDEX_STATS > x%, rebuild
- If delete rows as reported in INDEX_STATS < x%, don’t rebuild
- Analyze Index Validate Structure is a safe method of collecting index metrics
- Index rebuilds are inexpensive and unobtrusive
- Primary/Unique Key constraints require a unique index
- Drop/disable a constraint, unique index policing index is automatically dropped
- All Foreign Key constraints must be indexed
- Indexes should eliminate sorting
- Only indexed columns require statistics
- Bitmap Indexes only useful with low cardinality columns
- Bitmap Index will be used when a B-tree is not for low cardinality columns
- Null values are not indexed
- Small tables (say < 100 rows) don’t benefit from indexing
- Separating indexes from tables in tablespaces improves performance
- Range scans not possible with Reverse Key indexes
- Local indexes improve performance
- Put most discriminating column first in concatenated indexes
- If SQL references all columns in index, index column order is irrelevant
- If leading column of index is not referenced in SQL, index not considered by CBO
- Monitoring Indexes will highlight which indexes can be safely dropped
- Indexing Tracking will highlight which indexes can be safely dropped
- Index Compression make indexes smaller
- B-Tree Index only useful with high cardinality columns
- Pointless indexing a column with one distinct value
- If more than x% rows returned, index is inappropriate, where x% between 0 & 100
- Full Table Scan more efficient than index range scan with table access, when returning 100% of data
- The CBO cost an internal value of no practical use for tuning/comparison purposes
- Index is best solution to return 0% of data
- You don’t need indexes in Exadata
- Indexes less important in Exadata, as Storage Indexes can take over if database index is missing
It’s important to note it potentially only takes just the one bad row inserted in a table, one inaccurate statistic, one index being used inappropriately, one missing index not created during a application upgrade, to bring down a whole Oracle database, RAC nodes running on an Exadata included…
Session One: Overview of Oracle Index Structures and Options
- Common Myths and Misconceptions
- Oracle Indexing Structures
- Oracle Indexing Options
Session Two: Introduction To B-Tree Indexes
- Understanding B-Tree Index Structure and Navigation
- B-Tree Index Structure Internals
- Tree Dumps
- Index Block Dump Examination
- Study of DML Operations on Index Internals
- Study of Concurrent Transactions on Index Internals
- Index Block Split Internals
- Index Root Block Internals
- Index INITRANS
Session Three: Index Statistics
- Available Index Statistics – DBA_INDEXES, INDEX_STATS, V$SEGMENT_STATS
-
Shortfalls with VALIDATE STRUCTURE
- Clustering Factor Study: The Most Important Statistic
- How To Ensure Clustering Factor Has Appropriate Value
- Clustering Attribute
- Table Re-Organisation And Index Importance
- TABLE_CACHED_BLOCKS
- Index Height
- Statistics Collection
Session Four: Indexes and Constraints
- Indexing PK and Unique Constraints
- Important Differences Between Unique and Non-Unique Indexes
- Indexing Options With Database Constraints
- Impact of Constraint Options on Indexing
- Indexing and Constraint Management
- Use Cases For Multiple Indexes On Same Column List
- IGNORE_ROW_ON_DUPKEY_INDEX Use Cases
- Foreign Keys and Indexing Strategies
- 18c Memoptimised Rowstore
- 18c Scalable Indexes
Session Five: Rebuilding, Coalescing and Shrinking Indexes
- Index PCTFREE
- Deleted Index Space Management
- Index Fragmentation Internals
- How To Avoid Index Fragmentation
-
Index Rebuild Criteria: The Chosen Few
- How To Determine Index Optimal Size
- Dangers Of Index Rebuilds
- Index BLEVEL
- Index Rebuild Internals
- Index Coalesce Internals
- Index Shrink Internals
- When to Rebuild or Coalesce or Shrink
- Deferred Invalidation During Index Maintenance
Session Six: Indexes And The Cost Based Optimizer (CBO)
- Indexes and CBO Case Studies
- How CBO Calculates Selectivity
- How CBO Costs Index Accesses
- How to Use Index To Access 100% of Rows
- Table Clustering Attribute
- CBO and System Statistics
- Indexes vs. Full Table Scans
- Index Related Access Paths
- Indexes and Sorting
- Index Related CBO Parameters
- Online Table Maintenance and Index Impact
Session Seven: Miscellaneous Index Tips, Tricks and Traps
- Concatenated Index Column Order Study
- Why Isn’t Oracle Using My Index ?
- Outliers and Other Statistic Anomalies
- Using B-Tree Indexes With Low Cardinality Columns
- Indexing NULLs
- Using Zero Sized Indexes
- Index Compression Options
- Index Monitoring
- Tracking Index Usage
- Indexing Small Tables
- Indexing Extended Data Types
- Indexing Encrypted Data
- SKIP_UNUSABLE_INDEXES
Session Eight: Additional Indexing Options
- Reverse Key Indexes Internals
- Index Organized Tables Internals
- Index Organized Table Secondary Indexes Internals
- Function-Based Indexes and Virtual Columns
- Fake Indexes
- Invisible Indexes
- Case In-Sensitive Indexes
- Indexing JSON Document Store
Session Nine: Partitioned Indexes
- Partitioning Options
- Partitioned Index Block Dumps
- Partition Pruning
- Global Indexes (Partitioned / Non-Partitioned)
- Local Indexes (Prefixed / Non-Prefixed)
- Unique Partitioned Indexes
- Partial Indexes
- Partition Access Paths
- Online Partition Index Conversion
- Asynchronous Global Index Maintenance
- Partition Statistics
Session Ten: Bitmap Indexes
- Overview Of Bitmap Indexes
- Bitmap Index Block Internals
- Bitmap Index Misconceptions
- Bitmap Index Size Considerations
- Bitmap Index Access Paths
- Star Transformations
- OLTP and Bitmap Indexes: Locking Implications
- Bitmap-Join Indexes
- Bitmap Index Restrictions
Optional Section: Indexing with Exadata
- Exadata Smart Scans vs. Index Accesses
- Storage Indexes and their Limitations
- Zone Maps
Richard Foote's Oracle Blog 