Indexing in Oracle

Register here

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

About the speaker

Ric Van Dyke is an Oracle Ace with over 30 years of experience in the IT industry. Coming from a 3GL programing back ground he started working with Oracle Version 6 as a developer with Forms 2.3 and became a DBA at Ford Motor Credit. He then worked at a variety of companies as an independent consultant. Ric worked for Oracle Corporation for 10 years, starting as a core database instructor where he taught and developed several courses. He became the Education Manager of the North Central Region. Ric then served in the Advanced Technologies Services consulting group as a Technical Manager where he worked with several clients doing RAC installs and performance engagements. Ric is currently the Education Director at Hotsos where he coordinates training activities. He also teaches both in-person and on-line training. Ric frequently speaks at user group meetings and gives on-line webinars.

When is it planned

October 26th 2017
Time 12.30 – 20.00

Venue

The exact location will be announced later but we can tell you that the event will take place in Reykjavík, Iceland.

Price

The price for the course is ISK 84,990 (roughly $ 795).

This price includes the fee for the course, refreshments during breaks.

Email us at info@miracle.is for more information or register here