Indexing

1
Indexing

• Indexes are employed to avoid the need for
  large-table, full-table scans and disk sorts,
  which are required when the SQL optimiser cannot
  find an efficient way to service the SQL query.

2
Indexing Problems

• Super-fast indexes need updating as data is
  changed gt DB slows down.
• More complex index more complex update
  mechanism more rollback .
• DB physical structure degrades, so does index
  (e.g. split blocks)
• Performance decreases over time
• Rebuild needed (which interferes with operations)

INDEX_STATS tells you how big the index has grown
3
Side Effects

• Definition of DB Consistency
• A reading process may need an older version of
  the data
• Need to create a private version of the data
• Attempt to describe all required operations in
  finishing to execute query on old data

4
Effects of Read Consistency

• Must find an older version of data
• Must apply roll back
• Must find old roll back block (I/O)
• Roll back index (I/O)
• Find data (I/O)
• Roll back data
• Read old data
• Reverse all changes
• Significant I/O implications buffer full of old
  stuff

5
Conclusions Regarding I/O

• Writers and Readers DO interfere with each other
• The mechanisms used by Oracle to bypass locking
  have performance side effects
• Performance come with minimising I/O
• i.e. with good access techniques
• Precision of data location
• Physical proximity of related data (i.e. caching)
• However Techniques to reduce I/O numbers tend to
  reduce the speed of access!

6
Indexing Types

• Oracle offers a wealth of index structures, each
  with their own benefits and drawbacks
• B-Tree Indexes - This is the standard tree index
  that Oracle has been using since the earliest
  releases. 
• Bitmap Indexes - Bitmap indexes are used where an
  index column has a relatively small number of
  distinct values (low cardinality). These are
  super-fast for read-only databases, but are not
  suitable for systems with frequent updates.  
• Bitmap Join Indexes - This is an index structure
  whereby data columns from other tables appear in
  a multi-column index of a junction table. This is
  the only create index syntax to employ a SQL-like
  from clause and where clause.

7
Indexing Partitions

• Performance requirements may mean Partitioned
  tables should be indexed
• Create index JOHN_NAME on JOHN (name)
• Local Partitions (Part1, Part2, Part3, Part4)
• Local means create separate index for each
  partition of the table
• Alternative is to create a global index with
  values from different partitions
• Global indexes cannot be created for Hash
  partitions