Oracle Index (RPM)

A Index Introduction

1.1 Creation syntax of the index:

CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>

On <schema>.<table_name>

(<column_name> | <expression> ASC | DESC,

<column_name> | <expression> ASC | DESC,...)

Tablespace <tablespace_name>

STORAGE <storage_settings>

LOGGING | Nologging

COMPUTE STATISTICS

nocompress | Compress<nn>

Nosort | REVERSE

PARTITION | GLOBAL partition<partition_setting>

Related instructions

1) UNIQUE | BITMAP: Specifies that unique is a unique value index, BITMAP is a bitmap index, and is omitted as a B-tree index.

2) <column_name> | <expression> ASC | DESC: Multiple columns can be indexed together, when expression is "function-based index"

3) Tablespace: Specifies the tablespace where the index is stored (the index and the original table are more efficient when not a tablespace)

4) STORAGE: Can further set the table space storage parameters

5) LOGGING | Nologging: Whether to generate redo logs for indexes (use nologging for large tables to reduce space consumption and increase efficiency)

6) COMPUTE STATISTICS: Collect statistics when creating new indexes

7) nocompress | COMPRESS<NN>: Whether to use "key compression" (using key compression to remove duplicate values in a key column)

8) Nosort | Reverse:nosort to create indexes in the same order as the table, REVERSE to store index values in reverse order

9) PARTITION | Nopartition: You can partition created indexes on partitioned tables and non-partitioned tables

1.2 Index Features:

First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table.

Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes.

Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data.

Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries.

By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying.

1.3 Insufficient index:

First, it takes time to create indexes and maintain indexes, and this time increases as the amount of data increases.

Second, the index needs to occupy the physical space, in addition to the data table to occupy the data space, each index also occupies a certain amount of physical space, if you want to establish a clustered index, then the space will be larger.

Thirdly, when the data in the table is added, deleted and modified, the index should be maintained dynamically, thus reducing the maintenance speed of the data.

1.4 The characteristics of indexed columns should be built:

1) You can speed up your search on columns that you often need to search for;

2) on the column that is the primary key, enforce the uniqueness of the column and the arrangement of the data in the organization table;

3) often used in connected columns, these columns are mainly foreign keys, can speed up the connection;

4) Create an index on a column that often needs to be searched by scope, because the index is sorted and its specified range is continuous;

5) Create an index on a column that is often ordered, because the index is sorted so that the query can use the sorting of the index to speed up the sort query time;

6) speed up the judgment of the condition by creating an index on the column that is often used in the WHERE clause.

1.5 The characteristics of indexed columns should not be built:

First, the index should not be created for columns that are seldom used or referenced in queries. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.

Second, you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval.

Third, you should not increase the index for columns that are defined as BLOB data types. This is because the amount of data in these columns is either quite large or has very little value.

The index should not be created when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting. When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.

1.6 Restricting indexes

Restricting indexes is one of the mistakes that some inexperienced developers often make. There are many pitfalls in SQL that make some indexes unusable. Here are some common questions to consider:

1.6.1 using the Not equals operator (<>,! =)

The following query performs a full table scan even if there is an index in the cust_rating column.

Select Cust_id,cust_name from Customers where cust_rating <> ' AA ';

Change the above statement to the following query statement, so that the index will be used when using a rule-based optimizer instead of a cost-based optimizer (smarter).

Select Cust_id,cust_name from Customers where cust_rating < ' AA ' or cust_rating > ' AA ';

Special note: By changing the non-equal operator to an OR condition, the index can be used to avoid a full table scan.

1.6.2 using is null or is not NULL

Using is null or is not NULL also restricts the use of the index. Because null values are not defined. Using NULL in an SQL statement can be a lot of trouble. It is therefore recommended that developers set the columns that need to be indexed to not NULL when the table is being constructed. If the indexed column has a null value in some rows, the index is not used (unless the index is a bitmap index and the bitmap index is discussed later in detail).

1.6.3 using Functions

If you do not use a function-based index, using a function in the WHERE clause of an SQL statement for a column that has an index will cause the optimizer to ignore those indexes. The following query does not use an index (as long as it is not a function-based index)

Select Empno,ename,deptno from emp where trunc (hiredate) = ' 01-may-81 ';

Change the above statement to the following statement so that it can be searched by the index.

Select Empno,ename,deptno from emp where hiredate< (to_date (' 01-may-81 ') +0.9999);

1.6.4 comparing data types that do not match

is also one of the more difficult to find performance issues. Note The following query example, Account_number is a VARCHAR2 type and has an index on the Account_number field.

The following statement performs a full table scan:

Select Bank_name,address,city,state,zip from banks where account_number = 990354;

Oracle can automatically change the WHERE clause to To_number (account_number) = 990354, which restricts the use of the index, and the following query can use the index:

Select Bank_name,address,city,state,zip from banks where account_number = ' 990354 ';

Special note: Comparisons between mismatched data types will allow Oracle to automatically limit the use of indexes, even if the query executes explain plan does not let you know why you did a "full table scan".

1.7 Query Index

Query the Dba_indexes view to get a list of all the indexes in the table, and note that the index of the schema (schema) can only be retrieved by User_indexes method. Access the User_ind_columns view to get a specific column that is indexed in a given table.

1.8 Combined Index

When an index contains multiple indexed columns, the index is called a combination (concatented) index. The query can use the index only under limited conditions before Oracle9i introduces an indexed access method for a skip scan. For example: The table EMP has a composite index key that contains empno, ename, and Deptno. Before oracle9i, you cannot use this index key for a range scan unless you specify a value for the first column (EMPNO) in the where sentence.

Special note: Before oracle9i, you can use a combined index only if you are using the index's leading index!

1.9 ORACLE ROWID

With the rowid of each row, the index Oracle provides the ability to access a single line of data. rowID is actually a line map that points directly to a single line. If you want to check for duplicate values or other references to the ROWID itself, you can use and specify ROWID columns in any table.

1.10 Selectivity

Using the User_indexes view, a distinct_keys column is displayed in the view. By comparing the number of unique keys and the number of rows in the table, you can determine the selectivity of the index. The higher the selectivity, the less data is returned by the index.

1.11 Cluster factor (clustering Factor)

Clustering factor is located in the User_indexes view. The column reflects whether the data is orderly relative to the indexed columns. If the value of the clustering factor column is close to the number of leaf blocks in the index (leaf block), the data in the table is more orderly. If its value is close to the number of rows in the table, the data in the table is not very orderly.

1.12 Two (binary height)

The two-yuan height of the index plays a key role in the amount of I/O required to return ROWID to the user process. After analyzing an index, you can view its two-yuan height by querying the b-level column of dba_indexes. The binary height varies mainly with the size of the table and the degree of narrowing of the range of values in the column being indexed. If there are a large number of deleted rows on the index, its two-yuan height will also increase. Updating an index column is also similar to a delete operation because it increases the number of deleted keys. Rebuilding the index may reduce the height of two yuan.

1.13 Fast Global Scan

This option can be used with fast global scan (quick full scan) from Oracle7.3. This option allows Oracle to perform a global index scan operation. A fast global scan reads all the leaf blocks on the B-tree index. The Db_file_multiblock_read_count parameter in the initialization file can control the number of blocks that are read at the same time.

1.14-Hop Scan

Starting with Oracle9i, the index skip Scan feature allows the optimizer to use a composite index, even if the leading column of the index does not appear in the WHERE clause. An index hop scan is much faster than a full index scan.

Here's a comparison of their differences:

Sql> Set Timing on

Sql> CREATE index Tt_index on TT (Teamid,areacode);

The index has been created.

Time used: 00:02:03.93

Sql> Select COUNT (AreaCode) from TT;

COUNT (AreaCode)

---------------

7230369

Time used: 00:00:08.31

Sql> Select/*+ index (TT tt_index) */count (AreaCode) from TT;

COUNT (AreaCode)

---------------

7230369

Time used: 00:00:07.37

1.15 Types of indexes

B-Tree index bitmap Index hash index index layout table

Inverse key Index A function-based index partition index local and global indexes

Two Index classification

Oracle provides a number of indexing options. Knowing which option to use under a given condition is important for the performance of an application. A bad choice can cause a deadlock and result in a sharp drop in database performance or a process termination. If you make the right choice, you can use resources wisely so that processes that have been running for hours or even days are completed in minutes, which makes you a hero at once. The following is a simple discussion of each index option.

The following types of indexes are discussed:

B-Tree index (default type)

Bitmap index

Hash index

Index Organization Table Index

Invert key (reverse key) index

Function-based indexing

Partitioned indexes (local and global indexes)

Bitmap Connection Index

2.1 B-Tree index (default type)

The B-Tree index is a common index in Oracle. It is the default index type when the index is created. A B-Tree index can be a (simple) index of a column, or it can be an index of a composite/composite (multiple columns). A B-tree index can include up to 32 columns.

In the example, the B-Tree index is on the last_name column of the employee table. The index has a two-yuan height of 3; Next, Oracle passes through two branch blocks (branch block) to reach the leaf block containing the rowid. In each branch block, the branch row contains the ID number of the next block in the chain.

Leaf blocks contain index values, ROWID, and pointers to the previous and next leaf blocks. Oracle can traverse this binary tree in two directions. The B-Tree index holds the ROWID value for each data row that has a value on the index column. Oracle does not index rows that contain null values on an indexed column. If the index is a combined index of more than one column, and the column contains a null value, the row is in an indexed column that contains a null value and will be processed to null (treated as NULL).

Tip: The values of the indexed columns are stored in the index. Therefore, you can create a composite (composite) index that can directly satisfy the query without having to access the table. This reduces the amount of I/O by not retrieving data from the table.

B-tree Features:

Suitable with a large number of increases, deletions, and changes (OLTP)

Cannot use a query that contains an OR operator;

Suitable for high cardinality columns (multiple unique values)

Typical tree-like structure;

Each node is a data block;

Most are physically one, two or three layers, logically three layers;

The leaf block data is sorted, incrementing from left to right;

The scope of the index is placed in the branch block and the root block;

2.2-bit Graph index

Bitmap indexes are well suited for decision support systems (decision supports SYSTEM,DSS) and data warehouses, and they should not be used for tables accessed through transactional applications. They can access very large tables using a column that is less than the medium cardinality (the number of different values). Although the bitmap index can be up to 30 columns, they are usually used only for a small number of columns.

For example, your table might contain a column called Sex, which has two possible values: male and female. This cardinality is only 2 if the user frequently queries the table based on the value of the sex column, which is the base column of the bitmap index. When more than one bitmap index is contained within a table, you can appreciate the true power of the index of the map. If there are multiple bitmap indexes available, Oracle can combine result sets from each bitmap index to quickly delete unnecessary data.

BITMAPT Features:

Suitability and decision support system;

The cost of doing the update is very high;

Very suitable for queries of the OR operator;

A bitmap index can be built when the base ratio is low;

Tip: You need to use a bitmap index for columns with lower cardinality. The sex column is an example of two possible values: male or female (base is only 2). Bitmaps are very fast for low cardinality (a small number of different values), because the size of the index is much smaller relative to the B-tree index. Because these indexes are low-cardinality B-tree indexes, they are very small, so you can frequently retrieve more than half of the rows in a table, and still use bitmap indexes.

When most entries do not add a new value to the bitmap, the bitmap index is usually better than B-tree to load the table (insert operation) in a batch (single user) operation. The bitmap index should not be used when multiple sessions are inserting rows into a table at the same time, which can occur in most transactional applications.

Example

Here's a sample table participant, which contains survey data from individuals. The columns Age_code, Income_level, Education_level, and marital_status all include their respective bitmap indexes. Shows the data balance in each histogram, and the execution path to the query that accesses each bitmap index. The execution path in the diagram shows how many single-bit graph indexes have been merged, and you can see that performance has been significantly improved.

As shown, the optimizer uses 4 separate bitmap indexes, which are referenced in the WHERE clause. Each bitmap record pointer, such as 0 or 1, is used to indicate which rows in the table contain the known values in the bitmap. With this information, Oracle performs a bitmap and operation to find which rows will be returned from all 4 bitmaps. The value is then converted to the ROWID value, and the query continues to complete the remaining processing work. Note that all 4 columns have a very low cardinality, and the index can be used to return a matching row very quickly.

Tip: After merging multiple bitmap indexes in a single query, you can make a significant performance improvement. Bitmap indexes use fixed-length data types that are better than variable-length data types. Larger blocks also improve the storage and reading performance of bitmap indexes.

The following query can display the index type.

Sql> Select Index_name, index_type from User_indexes;

Index_name Index_type

------------------------------ ----------------------

Tt_index NORMAL

IX_CUSTADDR_TP NORMAL

The B-Tree index is listed as normal, whereas the bitmap index has a type value of bitmap.

Tip: If you want to query the bitmap index list, you can query the Index_type column in the user _indexes view.

We recommend that you do not use bitmap indexing in some online transaction processing (OLTP) applications. The index value of the B-tree index contains ROWID so that Oracle can lock the index at the row level. The bitmap index is stored as a compressed index value, which contains a range of ROWID, so Oracle must lock all ranges of rowid for a given value. This type of locking may cause a deadlock in some DML statements. The SELECT statement is not affected by this locking issue.

Usage restrictions for bitmap indexes:

The rule-based optimizer does not consider bitmap indexing.

When you execute an ALTER TABLE statement and modify a column that contains a bitmap index, the bitmap index is invalidated.

The bitmap index does not contain any column data and cannot be used for any type of integrity check.

Bitmap indexes cannot be declared as unique indexes.

The maximum length of a bitmap index is 30.

Tip: Don't use bitmap indexing in a heavy OLTP environment

2.3 Hash Index

Hash clusters must be used to use hash indexes. When a cluster or hash cluster is established, a cluster key is defined. This key tells Oracle how to store tables on a cluster. When the data is stored, all the rows associated with the cluster key are stored on a database block. If the data is stored on the same database block, and the hash index is the exact match in the WHERE clause, Oracle can access the data by executing a hash function and I/O-while accessing the data by using a B-tree index with a two-yuan height of 4. You need to use 4 I/O when retrieving data. As shown, the query is an equivalent query that matches the hash column and the exact value. Oracle can quickly use this value to determine the physical storage location of a row based on the hash function.

A hash index can be the quickest way to access data in a database, but it also has its drawbacks. The number of different values on the cluster key must be known before the hash cluster is created. You need to specify this value when creating a hash cluster. A number that underestimates the different values of cluster keys can cause cluster conflicts (the key values of two clusters have the same hash value). This conflict is very resource-intensive. Collisions cause a buffer overflow to store additional rows and then cause additional I/O. If the number of different hash values has been underestimated, you must change this value after rebuilding the cluster.

The ALTER CLUSTER command cannot change the number of hash keys. Hash clusters may also waste space. If you cannot determine how much space is required to maintain all the rows on a cluster key, you can create a waste of space. If additional space cannot be allocated for future growth of the cluster, the hash cluster may not be the best choice. Hash clusters may not be the best choice if the application frequently makes full table scans on the cluster tables. Full table scans can be very resource-intensive due to the need to allocate the remaining amount of space in the cluster for future growth.

Be careful before you implement a hash cluster. You need to look at the application thoroughly to ensure that you already know a lot of information about tables and data before you implement this option. In general, hashing is very effective for some static data that contains ordered values.

Tip: A hash index is useful in situations where there are restrictions that require specifying a certain value rather than a range of values.

2.4 Index Organization table

The Index organization table changes the table's storage structure to a B-tree structure, sorted by the table's primary key. This special table, like other types of tables, can execute all DML and DDL statements on the table. Because of the special structure of the table, ROWID is not associated with the rows of the table.

For some statements involving exact match and range search, the indexed organization table provides a fast, key-based data access mechanism. The performance of update and DELETE statements based on primary key values is also improved because the rows are physically orderly. Because the values of the key columns are not duplicated in the table and index, the amount of space required for storage decreases.

If you do not frequently query data based on primary key columns, you need to create a level two index on other columns in the indexed organization table. Applications that do not frequently query tables based on primary keys do not understand the full benefits of using index-organized tables. For tables that are always accessed by an exact match or a range scan of the primary key, you need to consider using an indexed organization table.

Tip: You can create a level two index on the Index organization table.

2.5 Invert key Index

When loading some ordered data, the index is bound to encounter some I/O related bottlenecks. During data loading, certain parts of the index and disk will certainly be used much more frequently than other parts. To solve this problem, you can store the index table space on a disk architecture that can physically partition the files on multiple disks.

To solve this problem, Oracle also provides a way to reverse the key index. If the data is stored in a reverse key index, the value of the data is reversed from the previously stored value. In this way, data 1234, 1235, and 1236 are stored as 4321, 5321, and 6321. The result is that the index updates different index blocks for each newly inserted row.

Tip: If you have a limited disk capacity and you want to perform a large number of sequential loads, you can use the Reverse key index.

You cannot use a Reverse key index in conjunction with a bitmap index or an indexed organization table. Because the bitmap index and Index organization table cannot be reversed key processing.

2.6 Function-based indexes

You can create a function-based index in a table. If there is no function-based index, any query that executes a function on a column cannot use the column's index. For example, the following query cannot use the index on the job column unless it is a function-based index:

SELECT * from emp where UPPER (Job) = ' MGR ';

The following query uses the index on the job column, but it will not return a row with a MGR or MGR value for the job column:

SELECT * from emp where job = ' MGR ';

You can create indexes that allow index access to support function-based columns or data. Instead of indexing directly on the job column, you can create an index on the column expression upper (job), such as:

Create INDEX Emp$upper_job on EMP (UPPER (JOB));

Although function-based indexes are useful, you must consider the following questions before you create them:

Can I restrict the functions that are used on this column? If so, can you restrict all functions that are executed on this column?

Is there enough storage space to handle additional indexes?

What is the effect of increasing the number of indexes on each column on the performance of DML statements executed against that table?

Function-based indexing is useful, but you must be careful when you implement it. The more indexes you create on a table, the more time it takes for the INSERT, update, and DELETE statements to execute.

Note: For a function-based index used by the optimizer, the initial parameter query _rewrite _ enabled must be set to true.

Example:

Select COUNT (*) from sample where ratio (Balance,limit) >.5;

Elapsed time:20.1 minutes

CREATE index ratio_idx1 on sample (ratio (balance, limit));

Select COUNT (*) from sample where ratio (Balance,limit) >.5;

Elapsed time:7 seconds!!!

2.7 Partition Index

A partitioned index simply divides an index into multiple fragments. By dividing an index into multiple fragments, you can access smaller fragments (and faster), and you can store the fragments on separate disk drives (avoiding I/O problems). Both the B-tree and the bitmap index can be partitioned, and the hash index cannot be partitioned. There are several partitioning methods: tables are partitioned, indexes are not partitioned, tables are not partitioned, indexes are partitioned, and tables and indexes are partitioned. Whichever method you take, you must use a cost-based optimizer. Partitioning provides more possibilities to improve performance and maintainability

There are two types of partition indexes: Local partition index and global Partition index. Each type has two subtypes, prefixed index and no prefix index. The indexes on each column of a table can have combinations of various types of indexes. If a bitmap index is used, it must be a local index. The primary reason for partitioning the index is to reduce the size of the index that is required to read, and to put the partition in a different tablespace to improve the availability and reliability of the partition.

Oracle also supports parallel queries and parallel DML when using partitioned tables and indexes. This allows you to execute multiple processes at the same time, speeding up the processing of this statement.

2.7.1. Local partition index (commonly used index)

You can use the same partition key and scope bounds as the table to partition the local index. The partition for each local index contains only the keys and rowid of the table partition it is associated with. A local index can be a B-tree or a bitmap index. If it is a B-tree index, it can be a unique or not unique index.

This type of index supports partition independence, which means that for a separate partition, you can add, intercept, delete, split, go offline, and so on, without having to delete or rebuild the index at the same time. Oracle automatically maintains these local indexes. Local index partitions can also be rebuilt separately, while other partitions are not affected.

2.7.1.1 Index with prefix

The prefixed index contains the keys from the partition keys and takes them as the leading index. For example, let's review the participant table again. After you create the table, use the two columns of survey_id and survey_date for scope partitioning, and then create a local index with a prefix on the survey_id column, as shown in. All partitions of this index are equally divided, meaning that the partitioning of the index is created using the same range bounds of the table.

Tip: A local prefix index allows Oracle to quickly remove unnecessary partitions. That is, a partition that does not contain any value in the WHERE Condition clause will not be accessed, which also improves the performance of the statement.

2.7.1.2 Index of the prefix

The prefix index does not have the leading column of the partition key as the leading column of the index. If you use the same partition table with the same partition key (SURVEY_ID and Survey_date), the index built on the survey_date column is a local prefix index, as shown in. You can create a local prefix index on any column of the table, but each partition of the index contains only the key values for the corresponding partition of the table.

If you want to set an index that is not prefixed to a unique index, the index must contain a subset of the partition keys. In this example, we must combine the columns that contain survey and/or survey_id (as long as the survey_id is not the first column of the index, it is a prefixed index).

Tip: For a unique prefix index, it must contain a subset of the partition keys.

2.7.2. Global Partition Index

The global Partitioned index contains keys from multiple table partitions in an index partition. A partition key for a global partition index is a value that differs from or specifies a range in the partition table. When you create a global partitioned index, you must define the range and value of the partition key. The global index can only be a B-tree index. Oracle does not maintain the global partition index by default. If a partition is intercepted, added, split, deleted, and so on, the global partition index must be rebuilt unless you specify the UPDATE global indexes clause of the ALTER TABLE command when modifying the table.

2.7.2.1 Index with prefix

Generally, the globally prefixed index does not have a peer partition in the underlying table. There is no reason to limit the peer partitioning of an index, but Oracle does not take full advantage of peer partitioning when generating query plans or performing partition maintenance operations. If the index is partitioned, it must be created as a local index, so that Oracle can maintain the index and use it to remove unnecessary partitions, as shown in. In the 3 index partitions of the graph, each partition contains index entries that point to rows in multiple table partitions.

Partitioned, globally prefixed index

Tip: If a global index is partitioned by a peer, it must be created as a local index so that Oracle can maintain the index and use it to remove unnecessary partitions.

2.7.2.2 Index of the prefix

Oracle does not support non-prefixed global indexes.

2.8-bit Graph connection index

The bitmap join index is a bitmap index of connections based on two tables, which is used in a data warehouse environment to improve the performance of queries that connect dimension tables and fact tables. When creating a bitmap connection index, the standard method is to connect the dimension tables and fact tables that are commonly used in the index. When users combine query fact tables and dimension tables in a single query, they do not need to perform a connection because there is already a connection result available in the bitmap connection index. rowID further improves performance by compressing bitmap connections in the index, and reduces the amount of I/O required to access the data.

When creating a bitmap connection index, specify the two tables involved. The corresponding syntax should follow the following pattern:

Create bitmap index fact_dim_col_idx on FACT (DIM. Descr_col) from FACT, DIM

where FACT. Joincol = DIM. Joincol;

The syntax for bitmap joins is special, which contains the FROM clause and a WHERE clause, and references two separate tables. An indexed column is typically a description column in a dimension table-that is, if the dimension is a customer and its primary key is customer_id, then the column is usually indexed customer_name. If the fact table is named Sales, you can create an index using the following command:

Create Bitmap Index Sales_cust_name_idx

On SALES (CUSTOMER. customer_name) from SALES, CUSTOMER

where SALES. Customer_id=customer. customer_id;

If the user then queries the sales and customer tables using the WHERE clause of the specified customer_name column value, the optimizer can use the bitmap join index to quickly return rows that match the join condition and the customer_name condition.

The use of bitmap connection indexes is generally limited:

1) Only the columns in the dimension table can be indexed.

2) The column used for the connection must be a primary KEY or a unique constraint in the dimension table, or, in the case of a composite primary key, each column in the connection must be used.

3) You cannot create a bitmap join index on an indexed organization table, and the restrictions that apply to regular bitmap indexes also apply to bitmap connection indexes.

Note: This article is organized from Oracle Database 10g performance Tuning and optimization

------------------------------------------------------------------------------

Blog:blog.csdn.net/tianlesoftware

Oracle Index (RPM)