Indexing Seminar

Oracle Indexing Internals and Best Practices Seminar

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 18 countries. There is also a condensed 1 day version of the Oracle Index Internals and Best Practices Seminar upon request.

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 Dates page.

All seminars include:

  • Detailed course notes
  • Tea/Coffee
  • Lunch

 

Seminar Content (Subject To Minor Changes)

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

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
  • SKIP_UNUSABLE_INDEXES

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
  • Zero Sized Indexes
  • Index Compression Options
  • Index Monitoring
  • Tracking Index Usage
  • Indexing Small Tables
  • Deferred Invalidation During Index Maintenance
  • Indexing Extended Data Types
  • Indexing Encrypted Data

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

 

For more information or to request a seminar near you, please email richard@richardfooteconsulting.com.

 

RFC Icon

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s