Oracle Index Detailed __oracle

http://blog.csdn.net/tianlesoftware/article/details/5347098

A Index Introduction

1.1 Creation syntax for indexes:

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 unique unique value index, BITMAP as bitmap index, omitted as B-tree index.
2) <column_name> | <expression> ASC | DESC: Multiple columns can be indexed together, when expression is a "function based index"
3) Tablespace: Specifies the table space in which the index is stored (more efficient when the index and the original table are not in one table space)
4) STORAGE: Can further set the storage parameters of the table space
5) LOGGING | Nologging: Whether to generate redo logs for indexes (use nologging for large tables to reduce space usage and increase efficiency)
6) COMPUTE STATISTICS: Collect statistics when creating new indexes
7) nocompress | COMPRESS<NN>: Whether to use key compression (use key compression to delete duplicate values that appear in a key column)
8) Nosort | Reverse:nosort to create an index in the same order as in the table, REVERSE to store index values in reverse order
9) PARTITION | Nopartition: You can partition the indexes you create on partitioned and unpartitioned tables

1.2 Index Features:

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

Second, you can greatly speed up the retrieval of data, which is the main reason to create indexes.

Third, you can speed up the connection between tables and tables, especially in terms of realizing the referential integrity of the data.

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

In the process of querying, the optimization of the hidden device can be used to improve the performance of the system by using the index.

1.3 Insufficient index:

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

Second, the index needs to occupy the physical space, in addition to the data table occupies the data space, each index also occupies a certain physical space, if you want to establish a clustered index, then need more space.

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

1.4 Features of indexed columns should be built:

1 The speed of searching can be speeded up on the column that needs to be searched frequently;

2 enforces the uniqueness of the column and arranges the structure of the data in the organization table on the column as the primary key;

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

4 Create indexes on columns that often need to be searched according to scope, because the index is already sorted and its specified range is contiguous;

5 Create indexes on columns that often need to be sorted, because the indexes are sorted so that the query can use the sort of index to speed up the sorting query time;

6. Create indexes on columns that are frequently used in the WHERE clause to speed up the judgment of the condition.

1.5 should not build the characteristics of indexed columns:

First, you should not create indexes for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, they are indexed or indexed and do not increase the query speed. On the contrary, because of the addition of indexes, it reduces the maintenance speed of the system and increases the space requirement.

Second, you should not add indexes to columns that have very few data values. This is because, because of the low values of these columns, such as the gender column of the personnel table, the result set's data rows account for 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. Adding indexes does not significantly speed up the retrieval.

Third, columns that are defined as BLOB data types should not be added to the index. This is because these columns have either a large amount of data or a very small number of values.

Four, you should not create an index when the modification performance is far greater than the retrieval performance. This is because the modification performance and retrieval performance are contradictory. When indexing is added, retrieval performance is improved, but modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, indexes should not be created when the modification performance is far greater than the retrieval performance.

1.6 Limit Index
Restricting indexing is one of the common mistakes that inexperienced developers make. There are many traps in SQL that make some indexes unusable. Some common issues are discussed below:
1.6.1 use not equal to operator (<>,!=)
The following query still 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 is used when adopting a rule-based optimizer instead of a cost-based optimizer (more intelligent).
Select Cust_id,cust_name from Customers where cust_rating < ' AA ' or cust_rating > ' AA ';
Special Note: You can use indexes to avoid full table scans by changing the not-equal operator to an OR condition.
1.6.2 use is null or is not NULL
Using is null or is not NULL also restricts the use of indexes. Because null values are not defined. Using NULL in SQL statements can be a lot of trouble. Therefore, developers are advised to set the columns that need to be indexed to not NULL when the table is being built. 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 in detail later).
1.6.3 using Functions
If you do not use a function based index, the optimizer ignores these indexes when you use functions in the WHERE clause of the SQL statement for columns that are indexed. 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 you can find it by index.
Select Empno,ename,deptno from emp where hiredate< (to_date (' 01-may-81 ') +0.9999);

1.6.4 Compare mismatched data types
is also one of the more difficult performance issues to discover. 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 turn the WHERE clause into to_number (account_number) = 990354, which limits the use of the index and can be used by changing to the following query:
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 restrict the use of indexes, even if the query execution explain plan does not let you understand why a "full table scan" was done.

1.7 Query Index
The query dba_indexes view gets a list of all the indexes in the table, noting that the index of the schema (schema) can only be retrieved through the User_indexes method. Access the User_ind_columns view to get a specific column indexed in a given table.

1.8 Combination Index
When an index contains more than one indexed column, the index is called a combined (concatented) index. Before Oracle9i introduces a jump-Scan index access method, the query can only use the index under limited conditions. For example: The table EMP has a composite index key that contains empno, ename, and Deptno. You cannot use this key to perform a range scan unless you specify a value for the first column (EMPNO) in the where sentence before oracle9i.
Special note: Before oracle9i, you can use a combined index only if you are using a leading index.

1.9 ORACLE ROWID
With the rowid of each row, the index Oracle provides the ability to access the Single-line data. rowID is actually a line diagram that directly points 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 the index returns.

1.11 Cluster factor (clustering Factor)
Clustering factor is located in the User_indexes view. This column reflects whether the data is ordered in relation to the indexed columns. If the value of the clustering factor column is close to the number of leaf blocks (leaf block) in the index, 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 yuan height (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 parsing an index, you can view its two-dollar height by querying the dba_indexes b-level column. The binary height varies mainly with the size of the table and the narrow extent of the values in the indexed column. If there is a large number of deleted rows on the index, its two yuan height will also increase. Updating an indexed column is similar to a delete operation because it increases the number of deleted keys. Rebuilding the index can reduce the height by two yuan.

1.13 Fast Global Scanning
You can use the Fast global scan (fast full Scan) option after Oracle7.3. This option allows Oracle to perform a global index scan operation. Fast global scan reads all leaf blocks on a B-tree index. The Db_file_multiblock_read_count parameter in the initialization file can control the number of blocks that are read concurrently.

1.14 Jump-Scan
Starting with Oracle9i, the index jump scan feature allows the optimizer to use a combined index, even if the leading column of the index does not appear in the WHERE clause. Index jump scans are much faster than full index scans.

The following 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 indexed bitmap Index Hash Index Table

Reverse key index local and global indexes based on function-indexed partitioning index

Two Index classification

The

Oracle provides a large 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 cause a sharp drop in database performance or a process termination. And if you make the right choice, you can use resources reasonably, so that processes that have been running for hours or even days will be completed in a few minutes, making you a hero immediately. The following is a simple discussion of each index option. The type of index discussed below

:
B-Tree index (default type)
Bitmap index
Hash index
Index Organization table index
Invert key (reverse key) index
based on function Index
Partitioned index (local and global index)
Bitmap Connection Index

2.1  B-Tree index (default type)
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 combination/compound (multiple columns). A B-tree index can contain up to 32 columns.
in the example in the following illustration, the B-Tree index is located on the last_name column of the employee table. The two-yuan height of this index is 3; Next, Oracle passes through two blocks of twigs (branch block) and arrives with rowID leaves. In each tree block, the tree row contains the ID number of the next block in the chain. The
Leaf block contains index values, ROWID, and pointers to the previous and last leaf blocks. Oracle can traverse this binary tree in two directions. The B-Tree index holds the ROWID value of each data row that has a value on the indexed 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 (as NULL).
                         

Tip: The values of indexed columns are stored in the index. As a result, you can establish a composite (composite) index that can directly satisfy a query without accessing the table. This removes the need to retrieve data from the table, thereby reducing the I/O amount.

B-tree Features:
Suitable with a large number of add, delete, change (OLTP)
Cannot use a query that contains an OR operator;
Columns for high cardinality (with more unique values)
Typical tree-like structure;
Each node is a block of data;
Most of them are physical one layer, two or three layers of uncertainty, logically three layers;
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 ideal for decision support systems (Decision Support SYSTEM,DSS) and data warehouses that should not be used for tables accessed through transactional applications. They can access very large tables using columns that are less than the medium cardinality (number of different values). Although bitmap indexes can reach 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, and if the user frequently queries the table based on the value of the sex column, this is the base column for the bitmap index. When you have more than one bitmap index in a table, you can appreciate the true power of the indexed map. If there are multiple bitmap indexes available, Oracle can merge the result sets from each bitmap index to quickly remove unnecessary data.

BITMAPT Features:
Suitable and decision support system;
The cost of doing the update is very high;
Very suitable for queries of or operators;
Bitmap index can be built when the base ratio is less;

Tip: You need to use a bitmap index for columns with a lower cardinality. The gender column is one such example, which has two possible values: male or female (cardinality is only 2). Bitmaps are very fast for low cardinality (a small number of different values) columns because the size of the index is much smaller than the B-tree index. Because these indexes are low cardinality B-tree indexes, they are very small, so you can often retrieve more than half of the rows in the table and still use a bitmap index.
When most entries do not add new values to the bitmap, the bitmap index usually does better than the B-tree when loading the table (insert operation) in a batch (single-user) operation. A bitmap index should not be used when more than one session inserts rows into a table, which occurs in most transaction processing 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 own bitmap indexes. The following illustration 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-digit indexes have been merged to see a significant improvement in performance.




As shown in the figure above, the optimizer uses 4 separate bitmap indexes in turn, and the columns of those indexes are referenced in the WHERE clause. Each bitmap record pointer (for example, 0 or 1) is used to indicate which rows in the table contain 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 very low cardinality, and using indexes can return matching rows very quickly.

Tip: After merging multiple bitmap indexes in a single query, you can significantly improve performance. Bitmap indexes use fixed-length data types to be better than variable-length data types. Larger-size blocks also increase the storage and read performance of bitmap indexes.

The following query displays 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, while the type value of the bitmap index is bitmap.

Tip: If you want to query the bitmap index list, you can query the Index_type column in the user _indexes view.
It is recommended that you do not use bitmap indexes 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 deadlocks in some DML statements. The SELECT statement is not affected by this locking problem.
Limit the use of bitmap indexes:

The Rule-based optimizer does not consider bitmap indexes.
The bitmap index is invalidated when the ALTER TABLE statement is executed and the column containing the bitmap index is modified.
A bitmap index does not contain any column data and is not used for any type of integrity check.
A bitmap index cannot be declared as a unique index.
The maximum length of a bitmap index is 30.

Tip: Do not use bitmap indexing in heavy OLTP environments

2.3 Hash Index
You must use a hash cluster to use a hash index. 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 data is stored, all rows associated with this cluster key are stored on a database block. If the data is stored on the same database block, and the hash index is an exact match in the WHERE clause, Oracle can access the data by executing a hash function and I/O, and accessing the data by using a B-tree index of two-yuan height 4. You need to use 4 I/O when retrieving data. As the following illustration shows, 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 may be the quickest way to access data in a database, but it also has its own 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. Underestimating the number of different values of a cluster key can cause a cluster conflict (the key values for two clusters have the same hash value). This conflict is very resource-intensive. A conflict creates a buffer overflow for storing extra rows, and then creates 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 can also waste space. If you cannot determine how much space is needed to maintain all the rows on a cluster key, you can create a waste of space. If additional space is not allocated for future growth of the cluster, the hash cluster may not be the best choice. A hash cluster may not be the best option if the application often makes a full table scan on the cluster table. Full-table scans can be very resource-intensive because of the need to allocate the rest of the cluster's space for future growth.
Be careful before implementing a hash cluster. You need to observe the application in a comprehensive way, ensuring that you know a great deal about tables and data before implementing this option. Typically, hashes are very effective for some static data that contains ordered values.

Tip: A hash index is useful in situations where there are restrictions (you need to specify 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 primary key of the table. This particular table, like other types of tables, can execute all DML and DDL statements on a table. Because of the special structure of the table, ROWID is not associated with the rows of the table.
For some statements involving exact matching and range searching, the index organization table provides a fast data access mechanism based on keys. The performance of update and DELETE statements based on primary key values is also improved because the rows are physically ordered. Because the values of the key columns are not duplicated in both the table and the index, the space required for the storage is reduced.
If you do not query data frequently against the primary key columns, you need to create a level two index on the other columns in the Index organization table. Applications that do not frequently query tables based on primary keys do not understand the full benefits of using indexes to organize tables. For tables that are always accessed through an exact match or range scan of a primary key, you need to consider using an index to organize your tables.

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

2.5 Inversion Key Index
When some ordered data is loaded, the index is bound to encounter some bottlenecks associated with I/O. During data loading, certain portions of the index and disk are certainly more frequently used than other parts. To solve this problem, the index table space can be stored in a disk architecture that can physically partition files across multiple disks.
To address this problem, Oracle also provides a way to reverse key indexing. If the data is stored in Reverse key index, the value of the data will be the opposite of the original stored values. Thus, 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 lot of sequential loading, you can use the Reverse key index.
You cannot use a Reverse key index in conjunction with a bitmap index or an index organization table. Because the bitmap index and Index organization table can not be reversed key processing.


2.6 Function-based indexing
You can create a function-based index in a table. If you do not have a function based index, any query that executes a function on a column cannot use the index of that column. For example, the following query cannot use an 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 rows with MGR or MGR values for the job column:
SELECT * from emp where job = ' MGR ';

You can create such an index to allow indexed access to support a function-based column or data. You can create an index on the column expression upper (job) instead of indexing directly on the job column, such as:
Create INDEX Emp$upper_job on EMP (UPPER (JOB));

Although function based indexes are useful, you must consider some of the following questions before you can build them:
Can you limit the functions that are used on this column? If you can, can you limit all the functions that are executed on this column?
Is there enough storage space to handle the extra index?
The number of indexes added on each column affects the performance of the DML statements that are executed against the table.

A function-based index is useful, but you must be careful when implementing it. The more indexes you create on a table, the more time it takes to execute the INSERT, UPDATE, and DELETE statements.

Note: For the 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!!! The

2.7  partition Index
Partitioning index is simply dividing an index into multiple fragments. By dividing an index into pieces, you can access smaller pieces (and faster), and you can store them on separate disk drives (to avoid I/O problems). Both a B-tree and a bitmap index can be partitioned, and a hash index cannot be partitioned. There are several partitioning methods: tables are partitioned and indexes are not partitioned, tables are not partitioned and indexes are partitioned, and tables and indexes are partitioned. Whichever approach you take, you must use a cost-based optimizer. Partitioning provides more possibilities for improving performance and maintainability
There are two types of partitioned indexes: local partitioned indexes and global partitioning indexes. Each type has two subtypes, with a prefix index and an no prefix index. Indexes on each column of a table can have combinations of various types of indexes. If you use a bitmap index, you must be a local index. The main reason for partitioning the index is to reduce the size of the indexes that you need to read, and to place the partitions in different tablespaces to increase the availability and reliability of the partitions.
When using partitioned tables and indexes, Oracle also supports parallel queries and concurrent DML. This allows multiple processes to be executed at the same time, speeding up processing of the statement.
2.7.1. Local partition index (commonly used index)
You can partition the local index using the same partitioning key and range bounds as the table. The partition for each local index contains only the keys and rowid of the table partition it is associated with. The local index can be either a B-tree or a bitmap index. If it is a B-tree index, it can be a unique or unique index.
This type of index supports partition independence, which means that for a separate partition, you can add, intercept, delete, split, and go offline, without deleting or rebuilding the index at the same time. These local indexes are automatically maintained by Oracle. Local index partitions can also be rebuilt separately, while other partitions will not be affected.

2.7.1.1 Index with prefix
The prefixed index contains the keys from the partitioning key 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 survey_id and survey_date to make the range partition, and then create a local index with a prefix on the survey_id column, as shown in the following illustration. All partitions of this index are equally divided, meaning that the partitions of the index are created using the same range boundaries of the table.

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

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

If you want to set the index of the prefix to a unique index, the index must contain a subset of the partitioning keys. In this example, we must combine the columns containing survey and/or survey_id (as long as 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 partitioning keys.

2.7.2. Global Partitioning Index
The global Partitioning index contains keys from multiple table partitions in one index partition. A partition key for a global partitioning index is a different or specified range of values in the partitioned table. When you create a global partitioned index, you must define the range and value of the partitioning key. The global index can only be a B-tree index. Oracle does not maintain the global partitioning index by default. If a partition is intercepted, incremented, split, deleted, and so on, the global Partitioning index must be rebuilt, unless the update global indexes clause of the ALTER TABLE command is specified when the table is modified.

2.7.2.1 Index with prefix
Generally, a globally prefixed index does not have a peer partition in the underlying table. There are few factors that can limit the peer partitions of an index, but Oracle does not make the most of the peer partitions when generating query plans or performing partition maintenance operations. If the index is peer 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 the following illustration. In the 3 indexed partitions of the diagram, each partition contains index entries that point to rows in multiple table partitions.




A partitioned, globally prefixed index

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

Index of 2.7.2.2 Prefix
Oracle does not support the global index without prefix.

2.8-bit Diagram Connection index
A bitmap connection index is a bitmap index based on a two-table connection that improves the performance of queries that connect dimension tables and fact tables by using such indexes in a data warehouse environment. When creating a bitmap connection index, the standard method is to connect the commonly used dimension tables and fact tables 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 are already available connection results in the bitmap connection index. Further improve performance by compressing the ROWID in the bitmap connection index and reducing 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 of a bitmap connection is special, which contains the FROM clause and the WHERE clause, and references two separate tables. An indexed column is usually a descriptive column in a dimension table-that is, if the dimension is customer and its primary key is customer_id, then a column such as customer_name is usually indexed. If the fact table name is sales, you can use the following command to create the index:
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 with the WHERE clause that specifies the Customer_name column value, the optimizer can use the bitmap connection index to quickly return the 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; if it is a composite primary key, you must use each column in the connection.

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

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

------------------------------------------------------------------------------
Blog:http://blog.csdn.net/tianlesoftware
Online resources: Http://tianlesoftware.download.csdn.net
Related Video: http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1 Group: 62697716 (full); DBA2 Group: 62697977 (Full)
DBA3 Group: 63306533; Chat Group: 40132017