Oracle Database Indexing (ENG)

Why attend this workshop?

Indexing is one of the most important topics when developing or maintaining applications based on relational databases.

However, unfortunately there are still a lot of misconceptions and misunderstandings popular regarding this topic and what makes a good indexing.

Therefore this workshop should provide a thorough understanding how B*Tree and Bitmap Indexes work and what the consequences are given this knowledge.

Based on this foundation we will work on what is relevant when designing indexes using many practical examples, how a reasonable design approach could look like and which factors influence this design.

An easy to use calculation method and corresponding formula will be introduced that allows a quick estimation of different index access patterns providing a qualified and quantified basis for a decision which variant should be preferred in which scenario.

This covers not only single table access patterns, but also joins. Top N / pagination queries will also be covered.

Additional, potentially relevant aspects will be covered, too - like the possibility of using an index in a more flexible way by means of Oracle's capability to perform a so called INDEX SKIP SCAN operation as well as the potential disk space savings of the available index compression options.

Depending on the available time many more index related topics can be covered, see the list of optional topics below.

Objectives

• Provide a basic understanding how a B*Tree and Bitmap Index works. What happens when such a structure needs to be modified?
  
  
• Index design: Given the foundation how such an index structure works - which aspects are relevant when designing indexes? What kind of different variants are possible and how can these be assessed?
  
  
• Assess index access patterns: Which access patterns do have which implications - and how can this be quantified? Exercises using practical examples applying a suitable calculation formula
  
  
• Optionally cover many more index related topics like the various indexing options available in Oracle, index monitoring, indexes and constraints, optimising DML operations, index maintenance and rebuild options, partitioning etc.

Required skills

Thorough understanding of Oracle Architecture and SQL

Target audience

DBAs and developers

Topics covered

In this seminar the most relevant topics regarding B*Tree and Bitmap Indexes are covered:

- Indexes basics: What does a B*Tree Index look like internally, how is it built and maintained, how does a Bitmap Index look like and what are the consequences of that?

- Index access, filter and data clustering: What operations does an index access path consist of, how much effort do these operations cause under which circumstances? Why does the database sometimes calculate a wrong “Clustering Factor” statistic and what can be done to correct this? Why is it possible that a Full Table Scan operation is much efficient than an index based access? Indexes and the optimizer – which information is considered, which isn’t?

- Index design: What should be considered when designing indexes? Which points are relevant when determining the order of columns in a composite index consisting of multiple columns? When could a Bitmap Index be useful? 

- Index Compression: How does it work and what are the benefits? What are the implications and potential side effects? Difference between Basic and Advanced Compression Low / High

Optional topics

- Index Options: Invisible / Unusable / Virtual / Partial indexes. Primary Key / Unique / Deferred constraints and indexes.

- Indexes and DML: What are the implications of indexes when applying DML to a table? Common scenarios when concurrently modifying a table - which measures can help and what are the ramifications of such measures?

- Index monitoring: How to monitor the index usage of an existing application and how to use the information provided?

- Foreign Keys and indexes: When should foreign keys be indexed?

- Index Rebuild: When should indexes be rebuilt? Which options exist and how do these differ?

- Partitioning and indexing: What is the difference between local and global indexes? What are the partitioning options for indexes? What are the new features since 12c regarding index partitioning and when can these be useful?

- Case Insensitive / Linguistic indexes / Collation: Searching case insensitive using indexes, new features since version 12.2

- Beyond B*Tree and Bitmap Indexes – which other features and further possibilities exist? Index Organized Tables, Hash and Indexed Cluster, Attribute Clustering, Basic concept of Domain Indexes, Text / XML / JSON Indexes

- Automatic Indexing: Currently only available on Exadata based environments - (On Premise / Cloud / Autonomous Database) – how does this feature work? What needs to be considered? Which scenarios are not covered yet? What are the possible use cases?