Indexing in Oracle - Reykjavik (10/26/17)

Objective

During this class we’ll exam the index options you have with Oracle. This will give you a firm understanding of what indexes really are and how they can help your queries run faster. You’ll know more about how function based index can be used for more complex situation then just and upper function on a string datatype. Also you will know how to maintain your indexes, when to rebuild vs coalesce for example.

Target Audience

The course is designed for anyone who writes SQL in Oracle. With a better understanding of indexing within Oracle you will be able to create the right indexes to have the most optimal queries.

Prerequisites

Before coming to class, please register for an account at hotsos.com.

Students are expected to have a working knowledge of Oracle SQL through experience either as a database administrator or application developer.

Syllabus

1. Welcome
   
   • Introductions
   • Index Fundamentals
   • Types of Indexes
   • Full table scan or index
   • Benefit of indexes
2. B-Tree Indexes
   
   • Structure
   • ROWIDs
   • Cluster Factor
   • Column Expressions
   • Implicit datatype conversion
   • Indexing null values
   • Composite index column order
   • Invisible indexes
3. Index Access Paths
   
   • LIOs and PIOs
   • Full Table Scan
   • Index Unique Scan
   • Index Range Scan
   • Index Full Scan
   • Index Fast Full Scan
   • Index Skip Scan
   • Index Join Scan
4. Function Based Indexes (FBI)
   
   • What is a function based index
   • Creating an FBI
   • User-Definated Functions
   • FBI for Low Cardinality Columns
   • Enforcing a business rule
   • Limitations
5. Bitmap Indexes
   
   • Basics of bitmap indexes
   • The Bitmap entry
   • When to use
   • Bitmap steps, cost and time
   • Locking and bitmaps
   • Bitmap index restrictions
   • Bitmap Join Indexes
6. Index Maintenance
   
   • Analyzing
   • Rebuilding and Coalescing
   • Storage Characteristics
   • Block splits
   • Index usage
   • Useful views

Class Forum

For questions and discussion about the course, please visit our forum.

Instructional Format

The one-day course is approximately 60% instructor-led lecture and discussion, 40% in-class exercises. The course is conducted in English.

Materials

Each student receives the following materials:

• Course Notes
  A PDF copy of the presentation is provided prior to the beginning of training. Please download this prior to the start of training.
• Tools
  Access to course software tools and supplemental documentation at hotsos.com.