Home arrow MySQL arrow 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).

TABLE OF CONTENTS:
  1. SQL Performance and Tuning Considerations, concluded
  2. Using EXPLAIN PLAN
  3. Microsoft SQL Server Considerations
  4. Displaying Execution Plans Using SQL Query Analyzer
  5. Quiz
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 10
March 23, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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  Its 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;



 
 
>>> More MySQL Articles          >>> More By McGraw-Hill/Osborne
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: