0131-0133

Previous Table of Contents Next

Page 131

Index Partitioning

In addition to partitioned indexes, Oracle8 offers several new types of indexes that you can use to improve performance for particular types of applications.

Reverse Key Indexes

Reverse key indexes can be useful in high-volume OLTP applications. As the name implies, these indexes reverse the bits of each column value in the index, but column order is maintained . Reverse key indexes are useful in situations in which the natural order of the key would cause inserts and updates to be concentrated in a small number of leaf blocks.

Suppose that a sales application is used to generate a large number of invoices, and that it inserts sequentially numbered invoice data into a table where the invoice number column has a unique index to maintain integrity. Invoice numbers 100000 to 100999, for example, would be concentrated in a small number of index leaf blocks. Reversing the key causes insertions to be distributed across a wider range of leaf keys, making the index more selective. The REVERSE keyword is used to define a reverse key index, as shown here:

 CREATE INDEX ix_invoice_num ON invoice_master (invoice_num) REVERSE; 

NOTE

The disadvantage of using reverse key indexes is that you cannot perform a range scan against them, because the true key values no longer are adjacent.

Cooperative Indexes

Oracle8 also provides a cooperative index, implemented as an index-only table. You can use index-only tables to build indexes for large or complex datatypes, such as video and audio clips. You access these tables as if they were regular tables ”you can apply all DML and SQL operations to them. Index-only tables are stored as indexes, though, and you cannot build additional indexes on them. The CREATE TABLE syntax has been extended in Oracle8 to provide the mechanism for creating index-only tables. The following statement creates an index-only table that could be used to store MPEG video clips:

 CREATE TABLE press_conferences (date_recorded DATE,     topic         VARCHAR2(20),     video_data    BLOB,     CONSTRAINT pk_press_conf PRIMARY KEY (date_recorded, topic)) ORGANIZATION INDEX TABLESPACE ts_pc_index PCTTHRESHOLD 40 OVERFLOW TABLESPACE ts_pc_video; 

Note that the ORGANIZATION INDEX clause informs Oracle that this is an index-only table. The overflow area is used to prevent leaf nodes of the index from being used up with pieces of video data, and the PCTTHRESHOLD keyword indicates the maximum percentage of the block size a row

Page 132

can use before non-key column values are placed in the overflow area. In the preceding example, the video data always should be larger than one block, so the data for that column should always overflow. This table can be queried as the following:

 SELECT video_data FROM press_conferences WHERE date_recorded = `16-JUN-97' AND topic = `Oracle8 Release'; 

The B-tree structure of the index-only table can provide considerable performance gains if the table contains a large number of rows. The structure also eliminates the data redundancy that would result from the creation of a table as defined in the preceding code line, with a separate index for the primary key. These characteristics make index-only tables particularly well-suited for very large databases that store full documents or multimedia data.

Bitmap Indexes

The bitmap index is an index type that was introduced in version 7.3. Unlike the standard B*-tree Oracle indexes, bitmap indexes do not store row IDs with corresponding key values. Instead, the index is a list of possible key values and a bitmap, with each bit corresponding to an actual row in the table. If the bit is set for a particular row, the row contains the key value. An internal mapping function converts the bitmap representation to actual row IDs as needed. Bitmap indexes are particularly efficient (in terms of space use and performance) for indexes that have a low degree of cardinality. You can create these indexes by using the BITMAP keyword, as shown here:

 CREATE BITMAP INDEX ix_inv_region ON invoices(region_code); 

TIP

Data warehousing applications will derive the most benefit from bitmap indexes, because they often query very large tables based on a column(s) with a relatively low number of distinct values, such as an indicator, or a Boolean value. These indexes are not particularly useful in OLTP applications, though, because they take longer to update and do not provide the same advantages for queries based on values with a high degree of cardinality. Obviously, there is no benefit in creating a bitmap index for a unique key.

SQL Language Extensions

Oracle8 provides new built-in datatypes, object datatypes, nested tables, and a number of other features that require new DDL extensions. In this section, the DDL extensions that have less to do with physical storage and more to do with representation are discussed.

Built-In Types

Three new built-in types are introduced in Oracle8: VARRAY, REF, and large objects (LOBs). These new types provide powerful capabilities for dealing with objects and arrays of objects. The new LOBs accommodate improved multimedia storage.

Page 133

VARRAY

VARRAYs are one example of an Oracle8 collection that correspond roughly to an array. Before a VARRAY type can be used in a DDL statement, it must be declared. The following statement declares a VARRAY type consisting of numbers:

 CREATE TYPE invoice_details AS VARRAY(5) OF NUMBER (10); 

After you define a type, you can use it in a DDL statement directly, as shown here:

 CREATE TABLE invoice (invoice_num  NUMBER(10),     invoice_date DATE,     customer_id  NUMBER(10),     total_amt    NUMBER(10, 2),     status_code  NUMBER(1),     details         invoice_details); 

In this example, the invoice_details type is used to store a collection of foreign keys to invoice detail records. This method eliminates the need for a purely associative entity to relate an invoice to its detail records.

The individual elements of a VARRAY cannot be referenced by index in DML or SQL statements. The following DML statement inserts a row into the invoice table:

 INSERT INTO invoice (invoice_num, invoice_date, customer_id, total_amt, status_code, details)     VALUES (100292, '03-JUL-97', 23123, 33500, 1, invoice_details(2122939, 2122940,         null, null, null)); 

NOTE

Note that all values for details must be supplied. Indexes can be used to reference specific elements of VARRAYs in PL/SQL blocks.

Listing 7.2 creates a procedure that displays the detail keys for any invoice.

Listing 7.2. Displaying the detail keys for invoices.

 CREATE OR REPLACE PROCEDURE ShowDetails(InvoiceNum IN NUMBER) AS BEGIN DECLARE     test     invoice_details;     i     NUMBER; BEGIN     i := 1;     SELECT details INTO test FROM invoice WHERE invoice_num = InvoiceNum;     WHILE (i <= test.count) LOOP         dbms_output.put_line(test(i));         i := i + 1;     END LOOP; END; END ShowDetails; 

Previous Table of Contents Next