Types of index access

Index-only access

   INDEX IX1:  NAME    ASC,
               DEPT    ASC,
               MGR     DESC,
               SALARY  DESC,
               YEARS   ASC

   select name, dept, mgr, salary
     from employee
     where name = 'SMITH'

   create unique index ix1 on employee
     (name asc)
     include (dept, mgr, salary, years)

   select name, dept, mgr, salary
     from employee
     where name = 'SMITH'

Be sure to consider whether the additional storage space and maintenance costs of include columns are justified. If queries that exploit include columns are rarely executed, the costs might not be justified.

Multiple-index access

   INDEX IX2:  DEPT    ASC
   INDEX IX3:  JOB     ASC,
               YEARS   ASC

   where
     dept = :hv1 or
     (job = :hv2 and
     years >= :hv3)

Scanning index IX2 produces a list of record IDs (RIDs) that satisfy the dept = :hv1 predicate. Scanning index IX3 produces a list of RIDs that satisfy the job = :hv2 and years >= :hv3 predicate. These two lists of RIDs are combined, and duplicates are removed before the table is accessed. This is known as index ORing.

   where
     dept in (:hv1, :hv2, :hv3)

   INDEX IX4: SALARY   ASC
   INDEX IX5: COMM     ASC

   where
     salary between 20000 and 30000 and
     comm between 1000 and 3000

In this example, scanning index IX4 produces a bitmap that satisfies the salary between 20000 and 30000 predicate. Scanning IX5 and probing the bitmap for IX4 produces a list of qualifying RIDs that satisfy both predicates. This is known as dynamic bitmap ANDing. It occurs only if the table has sufficient cardinality, its columns have sufficient values within the qualifying range, or there is sufficient duplication if equality predicates are used.

To realize the performance benefits of dynamic bitmaps when scanning multiple indexes, it might be necessary to change the value of the sortheap database configuration parameter and the sheapthres database manager configuration parameter. Additional sort heap space is required when dynamic bitmaps are used in access plans. When sheapthres is set to be relatively close to sortheap (that is, less than a factor of two or three times per concurrent query), dynamic bitmaps with multiple index access must work with much less memory than the optimizer anticipated. The solution is to increase the value of sheapthres relative to sortheap.

The optimizer does not combine index ANDing and index ORing when accessing a single table.

Index access in range-clustered tables

Unlike standard tables, a range-clustered table does not require a physical index (like a traditional B-tree index) that maps a key value to a row. Instead, it leverages the sequential nature of the column domain and uses a functional mapping to generate the location of a specific row in a table. In the simplest example of this type of mapping, the first key value in the range is the first row in the table, the second value in the range is the second row in the table, and so on.

The optimizer uses the range-clustered property of the table to generate access plans that are based on a perfectly clustered index whose only cost is computing the range clustering function. The clustering of rows within the table is guaranteed, because range-clustered tables retain their original key value order.

Index access in column-organized tables

The performance of a select, update, or delete operation that affects only one row in a column-organized table can be improved if the table has unique indexes, because the query optimizer can use an index scan instead of a full table scan.

delete from t1
  where c1 = 99

             Rows
            RETURN
            (   1)
             Cost
              I/O
               |
               1
            DELETE
            (   2)
            16.3893
               2
         /----+-----\
        1            1000
     IXSCAN   CO-TABLE: BLUUSER
     (   3)           T1
     9.10425          Q1
        |
        1 
      1000
INDEX: BLUUSER
       UK2
       Q2