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.

Webinar FAQ

How do I register? Please contact me at richard@richardfooteconsulting.com and I will give you all the necessary registration and payment instructions.

How much does it cost? To attend the full 5 day x 4 hours webinar series costs $1250.00 Australian Dollars. If you’re from Australia, the full cost will be $1375.00 which includes the 10% GST.

What are the payment options? You can pay either by direct bank transfer (I will provide you with all the necessary banking information) or by Credit Card (I will send you a PayPal invoice which allows for Credit Card payments. You do not need a PayPal account for this service).

Are there group discounts? Yes, if you have 3 or more persons from your organisation that is interested in attending a webinar, please contact me for group discount rates.

If my payment hasn’t come through yet, can I still join the webinar? No. For obvious reasons, I must receive full payment before you can be registered for the webinar. I try to provide plenty of prior warning before the webinars to enable obtaining managerial permission and completing payments. Please don’t leave it too late.

Can I get a refund? No. However, if you’re not able to join for some reason, you can either transfer the registration to someone else within your organisation, or you can attend a later webinar at not addition cost. Please contact me ASAP if you’re not able to attend.

Do you run customer dedicated webinars? Yes, if you have 8 or more persons in your organisation interested in attending a webinar, contact me to determine if a webinar just for your organisation might be the better option.

What are the start and end times for each webinar? They are detailed within the webinar description. All times listed are in local Australian time as I’m based in Australia. You will need to convert these times to determine your corresponding local times. Webinars are scheduled to be more favourable in different parts of the world so select the webinar series that best suits you. Note: because of time differences, the webinar could be run in a different day to those listed in your part of the world. For example, the webinar scheduled for 6-10 November 2018 starts on Tuesday, 6 November at 5am in Australia, but this is actually Monday, 5 November at 10am in San Francisco.

What is the schedule for each webinar series? Each webinar series runs for 5 days (Monday-Friday at the targeted time zone), with 4 hours of activity per day. Each day, there are 3 x approx. 1 hour, 10 min sessions, with a 15 minute break following each one. During the 15 minute break, there is an opportunity for further questions to be asked.

Is the material covered the same as the in person seminars? Yes, the same material is covered. There is however a little more time available during a webinar, so I might be able to cover more topics. Maybe.

Will I be able to ask questions during the webinar? Yes. However, I will mute all connections during the webinar, with questions during the sessions possible via a texting interface. This will reduce noise issues and unnecessary questions from disrupting the presentations. I will try to answer appropriate questions during the presentations or during the 20 minute break following each presentation session. You can also ask me follow up questions after the webinar.

Do you get any webinar materials to keep? Yes. Each attendee will get a softcopy of the some 850+ pages of content in PDF format. This will allow everyone to review all the material after the webinar and to also cut ‘n’ paste the numerous demos I cover and re-run them in your own environments. Note this material is of course copyrighted and that replicating or distributing this material is strictly prohibited.

What webinar software is used? I will be using GoToWebinar.

Are the webinars recorded? No, not initially anyways. Remember, you do have a copy of all the webinar content covered in the supplied PDF.

How do I join the webinar? I will provide full instructions on how to join the webinar beforehand. You will be given a unique link that allows you to join in the fun. Do not distribute this link to anyone else as only one connection is possible per supplied link. I recommend downloading the GoToWebinar interface (I will provide the instructions) in plenty of time prior to joining in the webinar.

When should I login to the webinar? I will start the webinar session 30 minutes prior to the official start to give everybody plenty of time to logon and iron out any issues you might have.

Are there any exercises or hands on labs? No. There are parts where I get customer participation, but there are no labs or requirements to have a database environment on hand. That said, some people do like to periodically cut ‘n’ paste some of my demos from the supplied PDFs and run them in their own database environments during the class. However, the pace I set makes this difficult to do while keeping up with the presentation.

Do I get a certificate for attending? Yes. Everyone will get a certificate of attendance.

Is there a limit to the number of attendees per webinar? Yes. To keep the webinar running smoothly and to give everyone an opportunity to ask some questions, numbers will be strictly limited.

How often are these webinars scheduled? Unsure. I will try and run a webinar series in different time zone friendly every “few months”, but this will depend largely on demand and my availability.

Is the “Oracle Indexing Internals and Best Practices” learning experience really as good as people say? Yes, it really is 🙂

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