Indexing Internals Seminar/Webinar

NOTE: I’M NOW RETIRED AND NO LONGER PROVIDE  ORACLE TRAINING OR CONSULTING SERVICES!!!

For information on Richard Foote art, please visit richardfooteart.com.

 

Oracle Indexing Internals and Best Practices Seminar/Webinar

I currently present a fully updated and revised version of the very popular and successful “Oracle Indexing Internals and Best Practice” 2 day seminar, which has previously been run in some 20 countries.

I also present a 5 x 1/2 day version of this seminar as a webinar. See the Seminar/Webinar Dates page for all scheduled seminars and webinars.

This is a must attend seminar of 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. It’s a fun, but intense, content rich seminar that is suitable for people of all experiences (from beginners to seasoned Oracle experts). 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.

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 in 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.

Note: Numbers are strictly limited due to the small class nature of the seminars. Please book early to avoid disappointment as seminars are not scheduled regularly.

For a list of all upcoming seminars, see the Seminar/Webinar Dates page.

If you have 5 or more persons in your organisation who would like to attend the seminar, contact me as I can customise it and run it exclusively at your premises for a great price. No waiting for the seminar to come near you and no travel expenses for your employees. Just the seminar for your staff, with just the topics that are of most interest to your organisation. We can even look at your specific issues and address them as part of the seminar.

All seminars include:

  • Detailed course notes
  • Tea/Coffee
  • Lunch

Seminar/Webinar 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

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)

  • Detailed look at the new 19c Automatic Indexing capability (NEW!!)
  • 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

“Indexing Internals and Best Practices” Webinar

Webinars run for 4 hours each day, spanning a full week period (Monday to Friday) in various time zones that are friendly to different parts of the world. So you can attend the webinar for part of the day and still have time for other work activities.

RFC Icon

Leave a comment