Implementing IOTs into Existing Applications

Several of the Sales Tracking table objects are good candidates for IOTs. A good candidate is a table with infrequent updates but frequent use of some of the columns. The ST_VENDOR table (see Listing 13.9), as well as the ST_CUSTOMER table, lends itself well to IOTs. Both tables have frequent read access with just the ID and names fields being frequently accessed. Notice the INCLUDING clause lists the vendor_name field. This field is frequently accessed by the ST_INVENTORY application when the vendor_id field is used to verify the accuracy of information entered onscreen.

Listing 13.9. ST_VENDOR Index-Organized Table

CREATE TABLE st_vendor
    (vendor_id            NUMBER(6)     PRIMARY KEY,
    vendor_name           VARCHAR2(30)     NOT NULL,
    vendor_street1       VARCHAR2(30),
    vendor_street2        VARCHAR2(30),
    vendor_city           VARCHAR2(20),
    vendor_state          VARCHAR2(2),
    vendor_zipcode        VARCHAR2(10),
    vendor_tax_id         VARCHAR2(20)     NOT NULL)
    ORGANIZATION INDEX
    TABLESPACE st_data02
    PCTTHRESHOLD 20 INCLUDING vendor_name
    OVERFLOW TABLESPACE st_iot_overflow01
    STORAGE (INITIAL 5K
        NEXT 5
        MINEXTENTS 5
        MAXEXTENTS 100); ok pm

Index Tips and Techniques

Here are a series of tips that should help you decide which indexes are best for your application needs. Also listed are some of the restrictions and caveats of using the various indexing methods.

Unique and NON-Unique Index Tips

Unique indexes store the ROWID of the table's data block along with the key value. This makes for a quick retrieval of the individual row from the table's data block. Nonunique indexes are set up with the pairing of ROWID's to assist Oracle with a range-of-values type of a search.

Reverse-Key Index Tips

Reverse-key indexes assist Oracle with a better balance (resulting in much better index space utilization) of data across the b*tree structure but Oracle does not perform range-scans across reverse-key indexes.

If an application is frequently reading and updating the same key value, a lock will be put on the leaf block during the DML operation. In this case it might be best to consider reverse-key indexes to remove this possible locking contention by spreading the row access across many leaf blocks.

Monitoring Index Usage Tips

Unused indexes not only waste disk space but also cause Oracle to perform maintenance on yet another structure during DML operations. Oracle9i supports a monitoring method to help you see if a particular index is being used or not. When these unused indexes are identified, they should be dropped. Listing 13.10 shows the syntax used to initiate monitoring.

Listing 13.10. Index Monitor Example

SQL> ALTER INDEX ST_BT_STAFF_ID MONITORING USAGE;

This command uses the new V$OBJECT_USAGE, which has five columns: index_name, table_name, monitoring (currently being monitored—ON or OFF), start_monitoring, and end_monitoring. This view is based on a data dictionary table so the data will remain visible even after a crash or a system restart.

Index Space Utilization Tips

Oracle does not physically delete unused leaf blocks. A table with considerable amount of deletes could have indexes with quite a number of unused blocks. Because Oracle always starts from the left-most leaf block (low order) when processing a range of keys, having a significant number of empty or near empty blocks will definitely have an affect on a SQL statement's response time.

A high PCTREE used during initial index creation will assist Oracle later by doing most of the block splitting during the initial load of data. A small PCTFREE is useful to maintain some room in the leaf blocks for later updates and inserts to help keep from having to perform split operations. A PCTFREE of 0 might be useful for those indexes with keys (not reverse-key indexes) that are sequence numbers where the index is always growing on one end. Because no keys will be inserted into the middle of the index, there is no need to leave any additional room for this kind of activity.

Oracle8i and 9i can sense the type of index rows being added via a conventional INSERT statement. If the key data is always random in nature, Oracle v8.1.6 recognizes this and loads the blocks 70% full, overriding the PCTFREE storage clause value. If the key is noticed to be sequential in nature, Oracle v8.1.6 fills the leaf blocks as full as 95%, once again overriding the PCTFREE storage clause.

Index Reorganization Tips

The SQL script illustrated in Figure 13.4 shows a good way to monitor the growth of indexes. The height is the number of levels an index contains. The fewer the levels, the better the index performance is. On larger indexes, a good time to reorganize the index is when the height changes. Indexes can be reorganized by either dropping them and re-creating them or using the alter index <INDEX NAME> rebuild command. To use this command, you must have enough room in the tablespace to hold both indexes. This method guarantees that an index is available for application use while the index is being rebuilt.

IOT Tips

When using IOTs, it is important to keep a certain number of rows per index leaf block. If a leaf block only holds a few rows of data, it defeats the purpose of using an index at all. Conversely, if most of the data being accessed is relatively short in length, the remainder (possibly seldomly accessed data) can be stored in the overflow table by carefully using a mix of 'pctthreshold' and 'including' syntax.

IOT's are ideal for indexing longs, BLOBS, and CLOBS. All the key fields can be stored in the IOT and the long field stored in the overflow area.

The real benefit of using an IOT is that there is no duplication of data between an index and a table structure.

Bitmap Index and Bitmap Join Index Tips

Oracle processes bitmap and bitmap join indexes very efficiently. When two or more bitmap indexes are present on a table object, Oracle can perform Boolean operations (and/or/minus) effectively on the bitmap strings. Count operations are very efficient as well, as all Oracle has to do is count the 1's in the bitmap.

The block structure of a bitmap index is not a b*tree index structure and it does not have any of the Oracle data block header information either. The fewer the unique ids in a column being indexed, the fewer the rows, the fewer the leaf blocks. In other words, the fewer unique values, the faster bitmap indexes perform.

On the other hand, because Oracle has to shuffle the ROWID's when inserting rows into the underlying table, DML operations on bitmap indexes are very slow, as compared to DML operations on the standard b*tree index.

The other thing to note is that there is NO row-level locking. There is no interested-transaction list or other header information that tracks undo/ redo information for Oracle in the event of a read-consistent view or a rollback. When DML is being performed on any bitmap index, an exclusive lock is placed on the table object.

Bitmap indexes are only supported by the cost-based optimizer. The rule-based optimizer simply ignores them.

Bitmap indexes are very good for decision-support applications, data warehouse applications with large volumes of rows (and columns with just a few values), and so on.