Oracle7 Server Tuning

Library

Product

Contents

Index

Data Access Methods

Data Access Methods
Using Clusters
Using Hash Clusters
Using Anti-Joins
Using Indexes
Bitmap Indexing

Data Access Methods

This chapter provides an overview of data access methods that can enhance performance, and warns of situations to avoid. You can use hints to force various approaches. Topics in this chapter include:

Using Clusters

Follow these guidelines when deciding whether to cluster tables:

Consider clustering tables that are often accessed by your application in join statements.
Do not cluster tables if your application only joins them occasionally or modifies their common column values frequently. Modifying a row's cluster key value takes longer than modifying the value in an unclustered table because Oracle may have to migrate the modified row to another block to maintain the cluster.
Do not cluster tables if your application often performs full table scans of only one of the tables. A full table scan of a clustered table can take longer than a full table scan of an unclustered table. Oracle is likely to read more blocks since the tables are stored together.
Consider clustering master-detail tables if you often select a master record and then the corresponding detail records. Since the detail records are stored in the same data block(s) as the master record, they are likely to still be in memory when you select them, so Oracle may perform less I/O.
Consider storing a detail table alone in a cluster if you often select many detail records of the same master. This measure improves the performance of queries that select detail records of the same master but does not decrease the performance of a full table scan on the master table.
Do not cluster tables if the data from all tables with the same cluster key value exceeds more than one or two Oracle blocks. To access a row in a clustered table, Oracle reads all blocks containing rows with that value. If these rows take up multiple blocks, accessing a single row could require more reads than accessing the same row in an unclustered table.

Consider the benefits and drawbacks of clusters with respect to the needs of your application. For example, you may decide that the performance gain for join statements outweighs the performance loss for statements that modify cluster key values. You may want to experiment and compare processing times with your tables both clustered and stored separately. To create a cluster, use the CREATE CLUSTER command.

Using Hash Clusters

Hash clusters cluster table data based on the result of applying a hash function to each row's cluster key value. All rows with the same hash key value are stored together on disk. Consider the benefits and drawbacks of hash clusters with respect to the needs of your application. You may want to experiment and compare processing times with a table both stored in a hash cluster and stored alone with an index. This section describes:

When to Use a Hash Cluster

Follow these guidelines for choosing when to use hash clusters:

Consider using hash clusters to store tables that are often accessed by SQL statements with WHERE clauses that contain equality conditions that use the same column or combination of columns. Designate this column or combination of columns as the cluster key.
Store a table in a hash cluster if you can determine how much space is required to hold all rows with a given cluster key value, including rows to be inserted immediately as well as rows to be inserted in the future.
Do not use hash clusters if space in your database is scarce and you cannot afford to allocate additional space for rows to be inserted in the future.
Do not use a hash cluster to store a constantly growing table if the process of occasionally creating a new, larger hash cluster to hold that table is impractical.
Do not store a table in a hash cluster if your application often performs full scans of the table and you feel you must allocate a great deal of extra space to the hash cluster in anticipation of the table growing a great deal in the future. Such full table scans must read all blocks allocated to the hash cluster, even though some blocks may contain few rows. Storing the table alone would reduce the number of blocks read by full table scans.
Do not store a table in a hash cluster if your application frequently modifies the cluster key values. Modifying a row's cluster key value can take longer than modifying the value in an unclustered table because Oracle may have to migrate the modified row to another block to maintain the cluster.
Storing a single table in a hash cluster can be useful, regardless of whether the table is often joined with other tables, provided that hashing is appropriate for the table based on the previous points in this list.

How to Use a Hash Cluster

To create a hash cluster, use the CREATE CLUSTER command with the HASH and HASHKEYS parameters.

When you create a hash cluster, you must use the HASHKEYS parameter of the CREATE CLUSTER statement to specify the number of hash values for the hash cluster. For best performance of hash scans, choose a HASHKEYS value that is at least as large as the number of cluster key values. Such a value reduces the chance of collisions, or multiple cluster key values resulting in the same hash value. Collisions force Oracle to test the rows in each block for the correct cluster key value after performing a hash scan. Collisions reduce the performance of hash scans.

Oracle always rounds up the HASHKEYS value that you specify to the nearest prime number to obtain the actual number of hash values. This rounding is designed to reduce collisions.

Using Anti-Joins

An anti-join is a form of join with reverse logic. Instead of returning rows when there is a match (according to the join predicate) between the left and right side, an anti-join returns those rows from the left side of the predicate for which there is no match on the right. This behavior is exactly that of a NOT IN subquery with the right side of the anti-join predicate corresponding to the subquery.

When to Use an Anti-Join

An anti-join uses sort-merge or hash joins to evaluate the NOT IN subquery provided that certain conditions are met. Assume that the subquery predicate is of the form (colA1, colA2, ... colAn) NOT IN (SELECT colB1, colB2, ..., colBn FROM ...). The following conditions must be true for the subquery to be transformed into a hash or sort-merge anti-join:

All of the references to the columns in A must be simple reference to columns. References to columns in B must either be simple references to columns or aggregate functions (MIN, MAX, SUM, COUNT, or AVG) applied directly to a simple column if the subquery contains a GROUP BY. No other expressions are allowed.
All column references must be known to be not null.
The subquery must not have any correlation predicates. That is, predicates referencing anything in surrounding query blocks.
The WHERE clause of the surrounding query must not have ORs at the top-most logical level.
Anti-joins can only be used with the cost-based approach.

How to Use an Anti-Join

Oracle transforms NOT IN subqueries into sort-merge or hash anti-joins if the conditions in the previous section are true and there is a hint or initialization parameter specifying that the transformation take place.

For a specific query, place the MERGE_AJ or HASH_AJ hints into the NOT IN subquery. MERGE_AJ uses a sort-merge anti-join and HASH_AJ uses a hash anti-join. For example:

SELECT * FROM emp
	WHERE ename LIKE 'J%' AND
			deptno IS NOT NULL AND
			deptno NOT IN (SELECT /*+ HASH_AJ */ deptno FROM dept
					WHERE deptno IS NOT NULL AND
					loc = 'DALLAS');

If you wish the anti-join transformation always to occur if the conditions in the previous section are met, set the ALWAYS_ANTI_JOIN initialization parameter to MERGE or HASH. The transformation to the corresponding anti-join type then takes place whenever possible.

Using Indexes

This section describes:

When to Create Indexes

Indexes improve the performance of queries that select a small percentage of rows from a table. As a general guideline, you should create indexes on tables that are often queried for less than 2% or 4% of the table's rows. This value may be higher in situations where all data can be retrieved from an index, or where the indexed columns can be used for joining to other tables.

This guideline is based on these assumptions:

Rows with the same value for the column on which the query is based are uniformly distributed throughout the data blocks allocated to the table.
Rows in the table are randomly ordered with respect to the column on which the query is based.
The table contains a relatively small number of columns.
Most queries on the table have relatively simple WHERE clauses.
The cache hit ratio is low and there is no operating system cache.

If these assumptions do not describe the data in your table and the queries that access it, the percentage of the table's rows selected under which an index is helpful may increase to as much as 25%.

Tuning the Logical Structure

Although cost-based optimization is excellent at avoiding the use of non-selective indexes within query execution, the SQL engine must continue to maintain all indexes defined against a table whether they are ever used or not. Index maintenance presents a significant CPU and I/O resource demand in any I/O intensive application. Put another way, building indexes "just in case" is not a good move; rather they should not be built until required.

Indexes which are not used should be dropped. If all of the application SQL can be processed through EXPLAIN PLAN and the resulting plans are captured then any indexes which are not referenced in any execution plan can be detected. These indexes are typically, though not necessarily, non-selective.
However, indexes may have uses which are not immediately apparent from a survey of the execution plans of the statements within the application. In particular, Oracle7 uses "pins" (non-transactional locks) on foreign key indexes in order to avoid the need to place share locks on the parent table when enforcing foreign key constraints.

In many applications this foreign key index is never (or rarely) used to support a query. In the example shown there may be no normal requirement to locate all of the order lines for a given product. However when no index exists with LINES(PCODE) as its leading edge then a share lock will be placed on the Products table each time DML is performed against the Lines table. This in turn will only be a problem if the Products table itself is subject to frequent DML and in the example shown we might assume that the column QTY_ON_HAND is volatile, and that the table level share locks would cause severe contention problems.

If this contention starts to occur, then to remove it the application must either

accept the additional load of maintaining the index
accept the risk of running with the constraint disabled

Fortunately this issue does not normally affect traditional master/detail relationships where the foreign key is generally used as the leading edge of the primary key as in the example.

Foreign Key Constraint

How to Choose Columns to Index

Follow these guidelines for choosing columns to index:

Consider indexing columns that are used frequently in WHERE clauses.
Consider indexing columns that are used frequently to join tables in SQL statements. For more information on optimizing joins, see the section "How to Use a Hash Cluster" on page 8-4.
Only index columns with good selectivity. The selectivity of an index is the percentage of rows in a table having the same value for the indexed column. An index's selectivity is good if few rows have the same value.

Note: Oracle implicitly creates indexes on the columns of all unique and primary keys that you define with integrity constraints. These indexes are the most selective and the most effective in optimizing performance.

You can determine the selectivity of an index by dividing the number of rows in the table by the number of distinct indexed values. You can obtain these values using the ANALYZE command. A selectivity calculated in this manner should be interpreted as a percentage.

Do not index columns with few distinct values. Such columns usually have poor selectivity and, therefore, do not optimize performance unless the frequently selected column values appear less frequently than the other column values.
For example, consider a column containing equal numbers of the values 'YES' and 'NO'. Indexing this column would not normally improve performance. However, if the value 'YES' appears relatively infrequently and your application often queries for 'YES', then indexing the column may improve performance.
Do not index columns that are frequently modified. UPDATE statements that modify indexed columns and INSERT and DELETE statements that modify indexed tables take longer than if there were no index. Such SQL statements must modify data in indexes as well as data in tables.
Do not index columns that only appear in WHERE clauses with functions or operators. A WHERE clause that uses a function (other than MIN or MAX) or an operator with an indexed column does not make available the access path that uses the index.
Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows Oracle to modify data in the child table without locking the parent table.
When choosing whether to index a column, consider whether the performance gain for queries is worth the performance loss for INSERT, UPDATE, and DELETE statements and the use of the space required to store the index. You may want to experiment and compare the processing times of your SQL statements with and without indexes. You can measure processing time with the SQL trace facility.

How to Choose Composite Indexes

A composite index is an index that is made up of more than one column. Composite indexes can provide additional advantages over single-column indexes:

better selectivity	Sometimes two or more columns, each with poor selectivity, can be combined in a composite index with good selectivity.
additional data storage	If all the columns selected by a query are in a composite index, Oracle can return these values from the index without accessing the table.

A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Consider this CREATE INDEX statement:

CREATE INDEX comp_ind
   ON tab1(x, y, z);

These combinations of columns are leading portions of the index: X, XY, and XYZ. These combinations of columns are not leading portions of the index: YZ and Z.

Follow these guidelines for choosing columns for composite indexes:

Consider creating a composite index on columns that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either column individually.
If several queries select the same set of columns based on one or more column values, consider creating a composite index containing all of these columns.

Of course, consider the guidelines associated with the general performance advantages and trade-offs of indexes described in the previous sections. Follow these guidelines for ordering columns in composite indexes:

Create the index so that the columns that are used in WHERE clauses make up a leading portion.
If some of the columns are used in WHERE clauses more frequently, be sure to create the index so that the more frequently selected columns make up a leading portion to allow the statements that use only these columns to use the index.
If all columns are used in WHERE clauses equally often, ordering these columns from most selective to least selective in the CREATE INDEX statement best improves query performance.
If all columns are used in the WHERE clauses equally often but the data is physically ordered on one of the columns, place that column first in the composite index.

How to Write Statements That Use Indexes

After you create an index, the optimizer cannot use an access path that uses the index simply because the index exists. The optimizer can only choose such an access path for a SQL statement if it contains a construct that makes the access path available.

To be sure that a SQL statement can use an access path that uses an index, be sure the statement contains a construct that makes such an access path available. If you are using the cost-based approach, you should also generate statistics for the index. Once you have made the access path available for the statement, the optimizer may or may not choose to use the access path, based on the availability of other access paths.

If you create new indexes to tune statements, you can also use the EXPLAIN PLAN command to determine whether the optimizer will choose to use these indexes when the application is run. If you create new indexes to tune a statement that is currently parsed, Oracle invalidates the statement. When the statement is next executed, the optimizer automatically chooses a new execution plan that could potentially use the new index. If you create new indexes on a remote database to tune a distributed statement, the optimizer considers these indexes when the statement is next parsed.

Also keep in mind that the means you use to tune one statement may affect the optimizer's choice of execution plans for others. For example, if you create an index to be used by one statement, the optimizer may choose to use that index for other statements in your application as well. For this reason, you should re-examine your application's performance and rerun the SQL trace facility after you have tuned those statements that you initially identified for tuning.

How to Write Statements That Avoid Using Indexes

In some cases, you may want to prevent a SQL statement from using an access path that uses an existing index. You may want to do this if you know that the index is not very selective and that a full table scan would be more efficient. If the statement contains a construct that makes such an index access path available, you can force the optimizer to use a full table scan through one of these methods:

You can make the index access path unavailable by modifying the statement in a way that does not change its meaning. The following example illustrates this method.
You can use the FULL hint to force the optimizer to choose a full table scan instead of an index scan.
You can use the INDEX or AND_EQUAL hint to force the optimizer to use one index or set of indexes instead of another.

Since the behavior of the optimizer may change in future versions of Oracle, relying on methods such as the first to choose access paths may not be a good long-range plan. Instead, use hints to suggest specific access paths to the optimizer.

Assessing the Value of Indexes

A brute force way to determine whether an index is good is to create it, analyze it, and use EXPLAIN PLAN on your query to see if the optimizer uses it. If it does, keep the index unless it is very expensive to maintain. This, however, is very time and resource expensive. You can also assess the value of the index by comparing the optimizer cost (in the first row of EXPLAIN PLAN output) of the plans with and without the index.

The parallel query option utilizes indexes effectively. It does not perform parallel index range scans, but it does perform parallel index lookups for parallel nested loop join execution. If an index is very selective (there are few rows per index entry), then it may be better to use sequential index lookup than parallel table scan.

FAST FULL SCAN

The fast full index scan is an alternative to a full table scan when there is an index that contains all the columns that are needed for the query. FAST FULL SCAN is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan. Unlike regular index scans, however, no keys can be used, and the rows will not necessarily come back in sorted order. If there is a predicate that could be used as start or stop key for the index, the optimizer will not consider a fast full scan. The following query and plan illustrate this feature.

SELECT COUNT(*) FROM t1, t2

WHERE t1.c1 > 50 and t1.c2 = t2.c1;

The plan is as follows:

SELECT STATEMENT

 SORT					AGGREGATE

    HASH JOIN

      TABLE ACCESS		T1		FULL

      INDEX			T2_C1_IDX	FAST FULL SCAN

Here, the fast full index scan can be used for table T2 since only column C1 is needed and there is no predicate that can be used as an index key. By contrast, FAST FULL SCAN could not be used for a nested loop join with the same join order: the join predicate can be used as an index key for that type of join.

FAST FULL SCAN has a special index hint, INDEX_FFS, which has the same format and arguments as the regular INDEX hint.

Recreating an Index

You may wish to recreate an index in order to compact it and clean up fragmented space, or to change the index's storage characteristics. When creating a new index which is a subset of an existing index, or when rebuilding an existing index with new storage characteristics, Oracle uses the existing index instead of the base table to improve performance.

Consider, for example, a table named CUST with columns NAME, CUSTID, PHONE, ADDR, BALANCE, and an index named I_CUST_CUSTINFO on columns NAME, CUSTID and BALANCE of the table. To create a new index named I_CUST_CUSTNO on columns NAME and CUSTID, you would enter:

CREATE INDEX I_CUST_CUSTNO on CUST(NAME,CUSTID)

Oracle will automatically use the existing index (I_CUST_CUSTINFO) to create the new index rather than accessing the entire table. Note that the syntax used is the same as if the index I_CUST_CUSTINFO did not exist.

Use the ALTER INDEX REBUILD command to reorganize or compact an existing index, or to change its storage characteristics. The REBUILD uses the existing index as the basis for the new index. All index storage commands are supported, such as STORAGE (for extent allocation), TABLESPACE (to move the index to a new tablespace), and INITRANS (to change the initial number of entries).

Bitmap Indexing

This section describes:

Benefits for Data Warehousing

Bitmap indexing benefits data warehousing applications which have large amounts of data and ad hoc queries, but a low level of concurrent transactions. For such applications, bitmap indexing provides:

reduced response time for large classes of ad hoc queries
a substantial reduction of space usage compared to other indexing techniques
dramatic performance gains even on very low end hardware

Fully indexing a large table with a traditional B-tree index can be prohibitively expensive in terms of space since the index can be several times larger than the data in the table. Bitmap indexes are typically only a fraction of the size of the indexed data in the table.

Bitmap indexes are not suitable for OLTP applications with large numbers of concurrent transactions modifying the data. These indexes are primarily intended for decision support in data warehousing applications where users typically query the data rather than update it.

Bitmap indexes are integrated with the Oracle cost-based optimization approach and execution engine. They can be used seamlessly in combination with other Oracle execution methods. For example, the optimizer can decide to perform a hash join between two tables using a bitmap index on one table and a regular B-tree index on the other. The optimizer considers bitmap indexes and other available access methods, such as regular B-tree indexes and full table scan, and chooses the most efficient method, taking parallelism into account where appropriate. Note also that parallel query works with bitmap indexes as with traditional indexes. Parallel create index and concatenated indexes are supported.

What Is a Bitmap Index?

Oracle provides four indexing schemes: B-tree indexes (currently the most common), B-tree cluster indexes, hash cluster indexes, and bitmap indexes. These indexing schemes provide complementary performance functionality.

The purpose of an index is to provide pointers to the rows in a table that contain a given key value. For a regular index, this is achieved by storing a list of rowids for each key corresponding to the rows with that key value. (In ORACLE, each key value is stored repeatedly with each stored rowid.) With a bitmap index, a bitmap for each key value is used instead of a list of rowids. Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it means that the row with the corresponding rowid contains the key value. A mapping function is used to convert the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, bitmaps are very space efficient.

Bitmap indexing efficiently merges indexes corresponding to several conditions in the WHERE clause. Rows that satisfy some, but not all the conditions, are filtered out before the table itself is accessed. As a result, response time is improved, often dramatically.

Cardinality

The advantages of using bitmap indexes are greatest for low cardinality columns: that is, columns in which the number of distinct values is small compared to the number of rows in the table. If the values in a column are repeated more than a hundred times, the column is a candidate for a bitmap index. Even columns with a lower number of repetitions (and thus higher cardinality), can be candidates if they tend to be involved in complex conditions in the WHERE clauses of queries.

For example, on a table with one million rows, a column with 10,000 distinct values is a candidate for a bitmap index. A bitmap index on this column can out-perform a B-tree index, particularly when this column is often queried in conjunction with other columns.

B-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER. A regular B-tree index can be several times larger than the indexed data. Used appropriately, bitmap indexes can be significantly smaller than a corresponding B-tree index.

In ad hoc queries and similar situations, bitmap indexes can dramatically improve query performance. AND and OR conditions in the WHERE clause of a query can be quickly resolved by performing the corresponding boolean operations directly on the bitmaps before converting the resulting bitmap to rowids. If the resulting number of rows is small, the query can be answered very quickly without resorting to a full table scan of the table.

Bitmap Index Example

The following table shows a portion of a company's customer data.

CUSTOMER #	MARITAL_ STATUS	REGION	GENDER	INCOME_ LEVEL
101	single	east	male	bracket_1
102	married	central	female	bracket_4
103	married	west	female	bracket_2
104	divorced	west	male	bracket_4
105	single	central	female	bracket_2
106	married	central	female	bracket_3

Since MARITAL_STATUS, REGION, GENDER, and INCOME_LEVEL are all low-cardinality columns (there are only three possible values for marital status and region, two possible values for gender, and four for income level) it is appropriate to create bitmap indexes on these columns. A bitmap index should not be created on CUSTOMER# because this is a high-cardinality column. Instead, a unique B-tree index on this column in order would provide the most efficient representation and retrieval.

The following table illustrates the bitmap index for the REGION column in this example. It consists of three separate bitmaps, one for each region.

REGION='east'

REGION='central'

REGION='west'

1

0

0

0

1

0

0

0

1

0

0

1

0

1

0

0

1

0

Each entry (or "bit") in the bitmap corresponds to a single row of the CUSTOMER table. The value of each bit depends upon the values of the corresponding row in the table. For instance, the bitmap REGION='east' contains a one as its first bit: this is because the region is "east" in the first row of the CUSTOMER table. The bitmap REGION='east' has a zero for its other bits because none of the other rows of the table contain "east" as their value for REGION.

An analyst investigating demographic trends of the company's customers might ask, "How many of our married customers live in the central or west regions?" This corresponds to the following SQL query:

SELECT COUNT(*) FROM CUSTOMER WHERE MARITAL_STATUS = 'married' AND REGION IN ('central','west');

Bitmap indexes can process this query with great efficiency by merely counting the number of ones in the resulting bitmap, as illustrated in the following figure. To identify the specific customers who satisfy the criteria, the resulting bitmap would be used to access the table.

Executing a Query Using Bitmap Indexes

When to Use Bitmap Indexing

This section describes three aspects of the indexing scheme you must evaluate when considering whether to use bitmap indexing on a given table: performance, storage, and maintenance.

How to Create a Bitmap Index

To create a bitmap index, use the BITMAP keyword in the CREATE INDEX command:

CREATE BITMAP INDEX ...

All CREATE INDEX parameters except NOSORT are applicable to bitmap indexes. Multi-column (concatenated) bitmap indexes are supported; they can be defined over at most 14 columns. Other SQL statements concerning indexes, such as DROP, ANALYZE, ALTER, and so on, can refer to bitmap indexes without any extra keyword. The command ANALYZE INDEX VALIDATE STRUCTURE, however, is not applicable to bitmap indexes.

Note: The COMPATIBLE initialization parameter must be set to 7.3.2 or higher, for bitmap indexing to be available.

Uniqueness

System index views USER_INDEXES, ALL_INDEXES, and DBA_INDEXES indicate bitmap indexes by the word BITMAP appearing in the UNIQUENESS column. A bitmap index cannot be declared as UNIQUE and is thus considered a special form of NONUNIQUE index in all cases where uniqueness matters.

Using Hints

The INDEX hint works with bitmap indexes in the same way as with traditional indexes. A new hint is provided, INDEX_COMBINE, which has the same format as the INDEX hint:

INDEX_COMBINE(table index1 index2 ...)

If no indexes are given as arguments for this hint, the optimizer will use on the table whatever boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to use some boolean combination of those particular bitmap indexes.

Performance and Storage Tips

To obtain optimal performance and disk space usage with bitmap indexes, note the following considerations:

Large block sizes improve the efficiency of storing, and hence retrieving, bitmap indexes.
In order to make the compressed bitmaps as small as possible, you should declare NOT NULL constraints on all columns that cannot contain null values.
Fixed length datatypes are more amenable to a compact bitmap representation than variable length datatypes.

Initialization Parameters for Bitmap Indexing

The following new initialization parameters have an impact on performance:

CREATE_BITMAP_AREA_SIZE: This parameter determines the amount of memory allocated for bitmap creation. The default value is 8 Mb. A larger value may lead to faster index creation. If cardinality is very small, you can set a small value for this parameter. For example, if cardinality is only 2 then the value can be on the order of kilobytes rather than megabytes. As a general rule, the higher the cardinality, the more memory is needed for optimal performance. This parameter is not dynamically alterable at the session level.

BITMAP_MERGE_AREA_SIZE: This parameter determines the amount of memory used to merge bitmaps retrieved from a range scan of the index. The default value is 1 Mb. A larger value should improve performance because the bitmap segments must be sorted before being merged into a single bitmap. This parameter is not dynamically alterable at the session level.

V733_PLANS_ENABLED determines whether bitmap access paths will be considered for regular indexes on the tables that have at least one bitmap index.

Bitmap Indexes and EXPLAIN PLAN

New index row sources appear in the EXPLAIN PLAN output with the word BITMAP indicating the type. Consider the following sample query and plan, in which the TO ROWIDS option is used to generate the rowids that are necessary for table access.

EXPLAIN PLAN FOR
SELECT * FROM T
WHERE
C1 = 2 AND C2 <> 6 
OR
C3 BETWEEN 10 AND 20;
SELECT STATEMENT 
  TABLE ACCESS                 T     	BY ROWID
    BITMAP CONVERSION                 	TO ROWIDS
      BITMAP OR
        BITMAP MINUS
          BITMAP MINUS
            BITMAP INDEX	     C1_IND	SINGLE VALUE
            BITMAP INDEX	     C2_IND	SINGLE VALUE
          BITMAP INDEX	     C2_IND	SINGLE VALUE
        BITMAP MERGE
          BITMAP INDEX	     C3_IND	RANGE SCAN

Here, the following new row sources are used:

Row Source

Notes

BITMAP CONVERSION

TO ROWIDS converts the bitmap representation to actual rowids that can be used to access the table.
FROM ROWIDS converts the rowids to a bitmap
representation.
COUNT returns the number of rowids if the actual values are not needed.

BITMAP OR

computes the bitwise OR of two bitmaps

BITMAP MINUS

subtracts the bits of one bitmap from another. This row source is used for negated predicates and can only be used if there are some non-negated predicates yielding a bitmap from which the subtraction can take place. In the example above, such a bitmap results from the predicate c1 = 2. From this bitmap, the bits in the bitmap for c2 = 6 are subtracted. Also, the bits in the bitmap for c2 IS NULL are subtracted, explaining why there are two MINUS row sources in the plan. The NULL subtraction is necessary for semantic correctness unless the column has a NOT NULL constraint.

BITMAP INDEX

SINGLE VALUE looks up the bitmap for a single key value in the index.
RANGE SCAN retrieves bitmaps for a key value range.
FULL SCAN: A bitmap index full scan is performed if there is no start or stop key.

BITMAP MERGE

merges several bitmaps resulting from a range scan into one bitmap

Using Bitmap Access Plans on Regular B-tree Indexes

If the initialization parameter V733_PLANS_ENABLED is set to TRUE, and there exists at least one bitmap index on the table, the optimizer will consider using a bitmap access path using regular B-tree indexes for that table. This access path may involve combinations of B-tree and bitmap indexes, but might not involve any bitmap indexes at all. However, the optimizer will not generate a bitmap access path using a single B-tree index unless instructed to do so by a hint.

In order to use bitmap access paths for B-tree indexes, the rowids stored in the indexes must be converted to bitmaps. Once such a conversion has taken place, the various boolean operations available for bitmaps can be used. As an example, consider the following query, where there is a bitmap index on column C1, and regular B-tree indexes on columns C2 and C3.

EXPLAIN PLAN FOR
SELECT COUNT(*) FROM T
WHERE
C1 = 2 AND C2 = 6 
OR
C3 BETWEEN 10 AND 20;
SELECT STATEMENT 
  SORT                     		AGGREGATE
    BITMAP CONVERSION COUNT
      BITMAP OR
        BITMAP AND
          BITMAP INDEX	C1_IND		SINGLE VALUE
          BITMAP CONVERSION		FROM ROWIDS
            INDEX		C2_IND		RANGE SCAN
        BITMAP CONVERSION			FROM ROWIDS
          SORT				ORDER BY
            INDEX		C3_IND		RANGE SCAN

Here, a COUNT option for the BITMAP CONVERSION row source is used to count the number of rows matching the query. There are also conversions FROM ROWIDS in the plan in order to generate bitmaps from the rowids retrieved from the B-tree indexes. The occurrence of the ORDER BY SORT in the plan is due to the fact that the conditions on columns C3 result in more than one list of rowids being returned from the B-tree index. These lists are sorted before they can be converted into a bitmap.

Bitmap Index Restrictions

Bitmap indexes have the following restrictions:

For bitmap indexes with direct load, the UNRECOVERABLE and SORTED_INDEX flags are meaningless.

Performing an ALTER TABLE command that adds or modifies a bitmap-indexed column may cause indexes to be invalidated.

The command ANALYZE INDEX VALIDATE STRUCTURE is not applicable to bitmap indexes.

Bitmap indexes are not supported for Trusted Oracle.

Bitmap indexes are not considered by the rule-based optimizer.

Bitmap indexes cannot be used for referential integrity checking.

For normal indexes, a subquery can be used as an index driver if there is a predicate of the form col = (subquery). Such subqueries cannot be used as keys for a bitmap index.

Prev Next

Library

Product

Contents

Index

REGION='east'	REGION='central'	REGION='west'
1	0	0
0	1	0
0	0	1
0	0	1
0	1	0
0	1	0

Row Source	Notes
BITMAP CONVERSION	TO ROWIDS converts the bitmap representation to actual rowids that can be used to access the table. FROM ROWIDS converts the rowids to a bitmap representation. COUNT returns the number of rowids if the actual values are not needed.
BITMAP OR	computes the bitwise OR of two bitmaps
BITMAP MINUS	subtracts the bits of one bitmap from another. This row source is used for negated predicates and can only be used if there are some non-negated predicates yielding a bitmap from which the subtraction can take place. In the example above, such a bitmap results from the predicate c1 = 2. From this bitmap, the bits in the bitmap for c2 = 6 are subtracted. Also, the bits in the bitmap for c2 IS NULL are subtracted, explaining why there are two MINUS row sources in the plan. The NULL subtraction is necessary for semantic correctness unless the column has a NOT NULL constraint.
BITMAP INDEX	SINGLE VALUE looks up the bitmap for a single key value in the index. RANGE SCAN retrieves bitmaps for a key value range. FULL SCAN: A bitmap index full scan is performed if there is no start or stop key.
BITMAP MERGE	merges several bitmaps resulting from a range scan into one bitmap

Data Access Methods

CUSTOMER #

MARITAL_ STATUS

REGION

GENDER

INCOME_ LEVEL

Row Source

Notes