Oracle Indexing Internals & Best Practices Seminar with Richard Foote (Zürich), Zürich, Donnerstag, 28. Juni 2018

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

Session Three: Index Statistics

Available Index Statistics – DBA_INDEXES, INDEX_STATS, V$SEGMENT_STATS
Shortfalls with VALIDATE STRUCTURE
Clustering Factor Study
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
Nullable Column Indexing Strategies
Foreign Keys and Indexing Strategies

Session Five: Rebuilding, Coalescing and Shrinking Indexes

Index PCTFREE
Index Block Split Internals
Index Root Block Internals
Deleted Index Space Management
Index INITRANS
Index Fragmentation Internals
Index Rebuild Criteria
Index Optimal Size
Dangers Of Index Rebuilds
Index BLEVEL
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

Multiple Indexes on Same Column List
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
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

 
FAQs
 
How can I contact the organiser with any questions?
Please email richard@richardfooteconsulting.com with any questions.
 
What's the refund policy?
There are no refunds if seminar proceeds. If unable to attend, attendees may either transfer ticket to another employee (notification required) or attend a subsequent seminar. If seminar is canceled due to unforeseen circumstances, a full refund will be issued.
 
Do I have to bring my printed ticket to the event?
Keep your ticket handy either in the Eventbrite App or print it out and bring with you.
 
Is my registration fee or ticket transferrable?
Yes. Please notify richard@richardfooteconsulting.com if you are transfering your attendance to another person.
 
Is it ok if the name on my ticket or registration doesn't match the person who attends?
Yes. Notify richard@richardfooteconsulting.com if you are transfering your attendance to another person prior to the seminar.

Oracle Indexing Internals & Best Practices Seminar with Richard Foote (Zürich)

Weitere interessante Veranstaltungen
Holen Event Empfehlungen basierend auf Ihre Facebook-Geschmack. Hol es dir jetzt!Zeigen Sie mir die passenden Veranstaltungen für michNicht jetzt