SQL Performance and Tuning Considerations, concluded

Oracle Considerations

This section covers some tuning considerations that are specific to Oracle databases.

Oracle Index Considerations

Oracle provides some additional indexing options that are worth consideration:

• Function-based indexes  Normally a predicate such as WHERE UPPER(MOVIE_TITLE) LIKE ‘BIG%’ precludes the use of an index. However, Oracle allows an index to be created on a function, such as UPPER(MOVIE_TITLE), which then is usable for queries that reference the function. There are several prerequisites for the use of this option, so you may need some help from your Oracle DBA before you can use it. The syntax for a function-based index simply uses the function specification instead of the column name in the ON clause of the CREATE INDEX statement:
  
  CREATE INDEX IX_MOVIE_TITLE_UPPER
    ON MOVIE (UPPER(MOVIE_TITLE));
  
• Bit-map indexes  Oracle bit-map indexes are designed to handle columns where the cardinality is low (that is, where there are relatively few data values among many rows). A bit-map index contains records that have one bit for every row in the indexed table and one index record for every possible data value in the indexed column. A bit is “on” (a binary 1) if the corresponding row in the table contains the value that the index record represents, and “off” (a binary 0) if not. For example, if the MOVIE table contained 5000 rows and you built a bit-map index on the MPAA_RATING_CODE column, the index would contain records that were 5000 bits (roughly 625 bytes) in size, and there would be 6 such records (one for each value of MPAA_RATING_CODE). The first index record would represent the first MPAA_RATING_CODE value (“G”) and bits in that record would be “on” when the corresponding row in the MOVIE table had a rating of “G”. The DBMS can use matrix algebra to quickly find desired data rows, particularly when the search predicates reference multiple columns that have bit-map indexes. A bit-map index is created using the normal CREATE INDEX syntax with the keyword BITMAP used instead of the keyword UNIQUE:
  
  CREATE BITMAP INDEX IX_MOVIE_MPAA_RATING
    ON MOVIE (MPAA_RATING_CODE);
  
• Index organized tables  It’s a good practice to create an index on the primary key of every table. However, for tables with only a few columns, such as reference tables and small intersection tables, this always seems wasteful because most or all of the data in the table is repeated in the index. Oracle provides a nice solution that allows the entire table to be stored in the index. Essentially, an index organized table (IOT) is a primary key index and a table rolled into a single structure. While you can create additional indexes on an IOT, in those cases you might be better off using a conventional table. Here is an example that creates a reference table for media formats as an IOT:
  
  CREATE TABLE MEDIA_FORMAT (
    MEDIA_FORMAT_CODE    CHAR(1),
    MEDIA_FORMAT_DESC    VARCHAR(50),
    CONSTRAINT PK_MEDIA_FORMAT
        PRIMARY KEY (MEDIA_FORMAT_CODE))
    ORGANIZATION INDEX;