HomeMySQL SQL Performance and Tuning Considerations, concluded
SQL Performance and Tuning Considerations, concluded
This article, the second of two parts, will show you how to make the SQL statements you write run faster and more efficiently. It is excerpted from chapter 11 of the book SQL DeMYSTiFied, written by Andy Oppel (McGraw-Hill/Osborne, 2005; ISBN: 0072262249).
This section covers some tuning considerations that are speciﬁc 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 speciﬁcation 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 ﬁrst index record would represent the ﬁrst 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 ﬁnd 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: