Oracle index details

 

 

I. Index Introduction

1.1 index creation Syntax:

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>

 

Description

1) UNIQUE | BITMAP: specify UNIQUE as the UNIQUE value index, and BITMAP as the BITMAP index, which is omitted as the B-Tree index.

2) <column_name> | <expression> ASC | DESC: you can perform a Union Index on multiple columns. When expression is used, it is a "function-based index"

3) TABLESPACE: Specify the TABLESPACE to store the index (the index and the original table are not in the same TABLESPACE, And the validity rate is higher)

4) STORAGE: You can further set the storage parameters of the tablespace.

5) LOGGING | NOLOGGING: whether to generate redo logs for indexes (use NOLOGGING to reduce the occupied space and improve the efficiency of large tables as much as possible)

6) compute statistics: Collects STATISTICS when creating a new index.

7) NOCOMPRESS | COMPRESS <nn>: whether to use "Key compression" (you can use key compression to delete duplicate values in a key column)

8) NOSORT | REVERSE: NOSORT indicates creating an index in the same order as the table, and REVERSE indicates storing the index value in REVERSE order.

9) PARTITION | NOPARTITION: You can PARTITION the created index in a partitioned table or a non-partitioned table.

 

 

1.2 index features:

First, you can create a unique index to ensure the uniqueness of each row of data in the database table.

Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.

Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity.

Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.

Fifth, by using indexes, you can use the optimizer during the query process to improve system performance.

 

 

1.3 insufficient indexes:

First, it takes time to create and maintain indexes. This time increases with the increase of data volume.

Second, indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.

Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed.

 

 

1.4 features of index columns:

1) you can speed up the search on columns that frequently need to be searched;

2) force the uniqueness of the column as the primary key and the data arrangement structure in the organization table;

3) These columns are frequently used in connection columns. These columns are mainly foreign keys, which can speed up the connection;

4) Create an index on a column that often needs to be searched by range. Because the index has been sorted, the specified range is continuous;

5) Create an index on the columns that frequently need to be sorted. Because the index has been sorted, you can use the index sorting to speed up the sorting query time;

6) Create an index on the columns in the WHERE clause frequently to speed up condition judgment.

 

 

1.5 The characteristics of the index column should not be:

First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.

Second, indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed.

Third, indexes should not be added for columns defined as blob data. This is because the data volume of these columns is either large or small.

Fourth, when the modification performance is far greater than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance is much higher than the retrieval performance, you should not create an index.

 

 

1.6 restrict Indexes

Limiting indexes is one of the common mistakes that some inexperienced developers make. There are many traps in SQL that make some indexes unusable. Some common problems are discussed below:

1.6.1 use non-equals operators (<> ,! =)

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

Select cust_Id, cust_name from MERs where cust_rating <> 'a ';

Change the preceding statement to the following query statement. In this way, indexes are used when a rule-based optimizer is used instead of a cost-based optimizer (more intelligent.

Select cust_Id, cust_name from MERs where cust_rating <'A' or cust_rating> 'a ';

Note: You can use indexes by changing the non-equals operator to the OR condition to avoid full table scanning.

1.6.2 use is null or IS NOT NULL

Using is null or is not null also limits the use of indexes. The NULL value is not defined. Using null in SQL statements can cause a lot of trouble. Therefore, we recommend that you set the column to not null when creating a table. If the indexed column has a NULL value in some rows, this index will not be used (unless the index is a bitmap index, which will be discussed in detail later ).

1.6.3 use functions

If function-based indexes are not used, the optimizer will ignore these indexes when using functions for indexed columns in the WHERE clause of SQL statements. 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 preceding statement to the following statement, so that you can search by index.

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

1.6.4 unmatched Data Types

It is also one of the more difficult to find performance problems. Note that in the following query example, account_number is of the 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 limits the use of the index. You can change it to the following query to use the index:

Select bank_name, address, city, state, zip from banks where account_number = '20140901 ';

Note: Oracle automatically limits the use of indexes when comparing unmatched data types, even if you execute an Explain Plan for this query, you cannot understand why a full table scan is performed ".

 

 

1.7 query Indexes

Query the DBA_INDEXES view to obtain the list of all indexes in the table. Note that you can only retrieve the schema indexes using the USER_INDEXES method. Access the USER_IND_COLUMNS view to obtain a specific column indexed in a given table.

 

1.8 Composite Index

When an index contains multiple indexed columns, it is called a concatented index. Before Oracle9i introduces the Skip scan index access method, the query can only use this index with limited conditions. For example, the table emp has a composite index key, which contains empno, ename, and deptno. Before Oracle9i, unless a value is specified for the first column (empno) in the where clause, this index key cannot be used for a range scan.

Note: Before Oracle9i, a composite index can be used only when the index's leading index is used!

 

1.9 ORACLE ROWID

With the ROWID of each row, Oracle indexes provide the ability to access a single row of data. ROWID is actually a line chart pointing directly to a separate line. To check duplicate values or other references to ROWID, you can use and specify the rowid column in any table.

 

1.10 Selectivity

The USER_INDEXES view displays a distinct_keys column. Compare the number of unique keys and the number of rows in the table to determine the index selectivity. The higher the selectivity, the less data the index returns.

 

1.11 cluster Factor)

The Clustering Factor is located in the USER_INDEXES view. This column indicates whether the data is in order with respect to the column with the created index. If the value of the Clustering Factor column is close to the number of leaf blocks in the index, the data in the table is more ordered. If its value is close to the number of rows in the table, the data in the table is not very orderly.

 

1.12 Binary height)

The binary height of the index plays a key role in the amount of I/O required when the ROWID is returned to the user process. After an index is analyzed, You can query the B-level column of DBA_INDEXES to view its binary height. The binary height mainly varies with the table size and the narrow range of the values in the indexed columns. If there are a large number of deleted rows on the index, its binary height will also increase. An index column is also similar to a delete operation because it increases the number of deleted keys. Re-indexing may reduce the binary height.

 

1.13 quick global scan

You can use the Fast Full Scan option after oracle7.3. This option allows Oracle to perform a global index scan operation. Quick global scan reads all leaf blocks on the B-tree index. The DB_FILE_MULTIBLOCK_READ_COUNT parameter in the initialization file can control the number of blocks simultaneously read.

 

1.14 skip Scanning

Starting from Oracle9i, the index skip scan feature allows the optimizer to use a composite index, even if the index's leading column does not appear in the WHERE clause. Index skip scanning is much faster than full index scanning.

 

The following compares their differences:

SQL> set timing on

SQL> create index TT_index on TT (teamid, areacode );

The index has been created.

Time in use: 00: 02: 03.93

SQL> select count (areacode) from tt;

COUNT (AREACODE)

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

7230369

Used time: 00: 00: 08.31

SQL> select/* + index (tt TT_index) */count (areacode) from tt;

COUNT (AREACODE)

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

7230369

Used time: 00: 00: 07.37

 

1.15 index type

B-tree index bitmap Index HASH Index orchestration table

Reverse key index function-based index partition index local and global index

 

 

 

 

Ii. Index classification

Oracle provides a large number of index options. Knowing which option to use under a given condition is very important to the performance of an application. A wrong choice may lead to deadlocks, resulting in a sharp drop in database performance or Process Termination. If you make the right choice, you can reasonably use the resources, so that those processes that have been running for several hours or even a few days can be completed in a few minutes, so that you will immediately become a hero. Next we will briefly discuss each index option.

 

The index types discussed below:

B-tree index (default type)

Bitmap Index

HASH Index

Index organization table Index

Reverse key Index

Function-based index

Partition index (local and global index)

Bitmap join Index

 

2.1 B-tree index (default type)

The B-tree index is a common index in Oracle. When creating an index, it is the default index type. The B-tree index can be a (simple) or composite (multiple columns) index. The B-tree index can contain up to 32 columns.

In the example, the B-tree index is located in the last_name column of the employee table. The binary height of this index is 3. Next, Oracle will pass through two branch blocks to reach the leaf blocks containing ROWID. Each branchblock contains the ID of the next block in the chain.

The leaf block contains the index value, ROWID, and pointer to the forward and next leaf blocks. Oracle can traverse this binary tree in two directions. The B-tree index stores the ROWID value of each data row with values in the index column. Oracle does not index rows with NULL values in the index column. If the index is a combination of multiple columns, and the column contains NULL values, this row will be in the index column containing NULL values, it will be processed as NULL (treated as NULL ).

 

Tip: the value of the index column is stored in the index. Therefore, you can create a composite index that can directly meet queries without accessing the table. This removes the need to retrieve data from the table, thus reducing the I/O volume.

 

B-tree features:

Suitable for large numbers of OLTP)

Queries that contain the OR operator cannot be used;

Suitable for columns with a high base (multiple unique values)

Typical tree structure;

Each node is a data block;

Most of them are physical, two or three layers, but logical;

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

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

 

2.2 bitmap Index

Bitmap indexes are very suitable for Decision Support systems (DSS) and Data Warehouses. They should not be used for tables accessed by transaction processing applications. They can access very large tables using columns with less to medium base (number of different values. Although Bitmap indexes can contain up to 30 columns, they are usually used only for a small number of columns.

For example, your table may contain a column named Sex, which has two possible values: male and female. This base number is only 2. If you frequently query the table based on the Sex column value, this is the base column of the bitmap index. When a table contains multiple Bitmap indexes, you can understand the real power of the Map Index. If multiple available Bitmap indexes exist, Oracle can merge the result sets obtained from each bitmap index to quickly delete unnecessary data.

 

Features of Bitmapt:

Applicable and decision support systems;

The UPDATE cost is very high;

It is very suitable for OR operator queries;

Bitmap indexes can be created only when the base number is small;

 

Tip: Bitmap indexes are required for columns with a lower base. The Gender column is an example of two possible values: male and female (the base is only 2 ). Bitmap is very fast for low-base columns (a small number of different values) because the index size is much smaller than that of B-tree indexes. Because these indexes are low-Base B-tree indexes, they are very small. Therefore, you can often retrieve more than half of the rows in a table and still use Bitmap indexes.

When most entries do not add new values to the bitmap, the bitmap index generally performs better in loading tables (insert operations) in batch (single-user) operations than in Tree B. When multiple sessions Insert rows to the table at the same time, bitmap indexes should not be used. This happens in most transaction processing applications.

 

Example

The following is an example of a table named particle ant, which contains survey data from individuals. The Age_Code, Income_Level, Education_Level, and Marital_Status columns all contain Bitmap indexes. Shows the data balance in each histogram and the execution path for queries accessing each bitmap index. The execution path in the figure shows how many Bitmap indexes are merged. It can be seen that the performance has been significantly improved.

 

 

 

As shown in, the optimizer uses four separate Bitmap indexes, and these index columns 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 the bitmap and operation to find the rows that will be returned from all the four-digit charts. The value is then converted to the ROWID value, and the query continues to complete the remaining processing. Note that all four columns have a very low base. Using indexes, you can quickly return matched rows.

 

Tip: merging multiple Bitmap indexes in a query can significantly improve the performance. Bitmap indexes use a fixed-length data type, which is better than a variable-length data type. Larger blocks also improve the storage and reading performance of Bitmap indexes.

 

The following query shows 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 BITMAP index type value is BITMAP.

 

Tip: 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 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. Bitmap indexes are stored as compressed index values, which contain a certain range of rowids. Therefore, Oracle must lock rowids in all ranges for a given value. This type of lock may cause deadlocks in some DML statements. The SELECT statement is not affected by this locking problem.

Restrictions on Bitmap indexes:

The rule-based optimizer does not consider Bitmap indexes.

If you execute the alter table statement and modify a column that contains a bitmap index, the bitmap index will become invalid.

Bitmap indexes do not contain any column data and cannot be used for integrity checks of any type.

Bitmap indexes cannot be declared as unique indexes.

The maximum bitmap index length is 30.

 

Tip: Do not use Bitmap indexes in heavy OLTP Environments

 

2.3 HASH Index

To use HASH indexes, you must use a HASH cluster. A cluster key is defined when a cluster or HASH cluster is created. This key tells Oracle how to store tables in clusters. When data is stored, all rows related to the cluster key are stored in a database block. If the data is stored on the same database block and the HASH index is used as the exact match in the WHERE clause, oracle can access data by executing a HASH function and I/O-and access data by using a B-tree index with a binary height of 4, you need to use 4 I/O for data retrieval. As shown in, the query is an equivalent query used to match the HASH column and the exact value. Oracle can quickly use this value to determine the physical storage location of rows based on the HASH function.

The HASH index may be the fastest way to access data in the database, but it also has its own shortcomings. The number of different values on the cluster key must be known before the HASH cluster is created. You must specify this value when creating a HASH cluster. If the number of different values of the cluster key is underestimated, the cluster may conflict (the key values of the two clusters have the same HASH value ). Such conflicts consume a lot of resources. Conflicts will cause buffer overflow for storing extra rows, and then cause additional I/O. If the number of HASH values has been underestimated, you must change the value after rebuilding the cluster.

The alter cluster command cannot change the number of HASH keys. The HASH cluster may also waste space. If you cannot determine how much space is required to maintain all rows on a cluster key, the space may be wasted. If you cannot allocate additional space for the future growth of the cluster, the HASH cluster may not be the best choice. If applications often perform full table scans on cluster tables, the HASH cluster may not be the best choice. Due to the need to allocate the remaining space of the cluster for future growth, full table scan may consume a lot of resources.

Be careful before implementing the HASH cluster. You need to fully observe the application to ensure that you have learned a lot about tables and data before implementing this option. Generally, HASH is very effective for some static data containing ordered values.

 

Tip: the HASH index is useful when it has restrictions (a definite value needs to be specified instead of a value range.

 

2.4 index the Organizational table

The index organization table will change the storage structure of the table to the B tree structure and sort the table by the primary key. Similar to other types of tables, this type of special table can execute all DML and DDL statements on the table. Because of the special structure of the table, ROWID is not associated with the table's rows.

For some statements involving exact match and range search, the index organization table provides a key-Based Fast data access mechanism. The performance of primary key value-based UPDATE and DELETE statements is also improved because rows are physically ordered. Because the key column values are not repeated in both the table and index, the storage space is also reduced.

If data is not frequently queried based on the primary key column, you need to create a secondary index on other columns in the index organization table. Applications that do not frequently query tables based on primary keys do not understand all the advantages of using indexes to organize tables. For tables that are always accessed through precise matching of primary keys or range scanning, you need to consider using indexes to organize tables.

 

Tip: You can create a secondary index on the index organization table.

 

2.5 reverse key index

When loading ordered data, the index will certainly encounter some I/O-related bottlenecks. During data loading, some indexes and disks must be used more frequently than others. To solve this problem, you can store the index tablespace in a disk architecture that physically splits files into multiple disks.

To solve this problem, Oracle also provides a way to reverse the key index. If the data is stored with a reverse key index, the values of the data are the opposite of those originally stored values. 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 your disk capacity is limited and you need to perform a large number of sequential loads, you can use the reverse key index.

You cannot use reverse key indexes with bitmap indexes or index organization tables. Because Bitmap indexes and index organization tables cannot be reversed.

 

 

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 the function on the column cannot use the index of this column. 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 of the JOB column, but it does not return the rows in the JOB column with Mgr or mgr values:

Select * from emp where job = 'mgr ';

 

You can create such indexes to allow indexes to access columns or data that support functions. You can create an index for the column expression UPPER (job) instead of directly creating an index on the JOB column, for example:

Create index EMP $ UPPER_JOB on emp (UPPER (job ));

 

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

Can I restrict the functions used in this column? If yes, can all the functions executed on this column be restricted?

Is there enough storage space for additional indexes?

What is the impact on the performance of DML statements executed on this table when the number of indexes added to each column is greater?

Function-based indexes are very useful, but you must be careful when implementing them. The more indexes a table creates, the more time it takes to execute INSERT, UPDATE, and DELETE statements.

 

Note: For function-based indexes 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 segments. By dividing an index into multiple segments, you can access smaller segments (faster) and store these segments on different disk drives (to avoid I/O problems ). Both Tree B and bitmap indexes can be partitioned, but HASH indexes cannot. There are several partition methods: The table is partitioned, the index is not partitioned, the table is not partitioned, and the index is partitioned. Both the table and the index are partitioned. Either method requires a cost-based optimizer. Partitions provide more possibilities for improving performance and maintainability.

There are two types of partition indexes: Local partition index and global partition index. Each type has two subtypes: prefix index and no prefix index. The indexes on each column of a table can contain a combination of various types of indexes. If a bitmap index is used, it must be a local index. The primary cause of index partitioning is to reduce the size of the index to be read. In addition, placing partitions in different tablespaces can improve the availability and reliability of the partitions.

When Using Partitioned Tables and indexes, Oracle also supports parallel query and parallel DML. In this way, you can execute multiple processes at the same time to speed up processing this statement.

2.7.1. Local partition index (usually used index)

You can use the same partition key and range as the table to partition the local index. The partition of each local index only contains the key and ROWID of the table partition associated with it. Local indexes can be B-tree or Bitmap indexes. If it is a B-tree index, it can be a unique or non-unique index.

This type of index supports partition independence, which means that you can add, intercept, delete, split, and offline data for individual partitions without deleting or recreating indexes at the same time. Oracle automatically maintains these local indexes. Local index partitions can also be rebuilt separately, but other partitions will not be affected.

 

2.7.1.1 index with a prefix

An index with a prefix contains keys from the partition key and uses them as the leading index. For example, let's re-review the table 'delete. After creating the table, use the survey_id and survey_date columns to partition the range, and then create a local index with a prefix on the survey_id column, as shown in. All the partitions of this index are equally divided, that is, all the partitions of the index are created using the same range of the table.

 

 

Tip: Local prefix indexes allow Oracle to quickly remove unnecessary partitions. That is to say, partitions that do not contain any value in the WHERE Condition Clause will not be accessed, which also improves the statement performance.

 

2.7.1.2 index without a prefix

The index without a prefix does not use the leading column of the partition key as the leading column of the index. If you use a table with the same partition key (survey_id and survey_date), the index created on the survey_date column is a local index without a prefix, as shown in. You can create a local index without a prefix on any column of the table, but each partition of the index only contains the key value of the corresponding partition of the table.

If you want to set an index without a prefix as a unique index, this index must contain a subset of the partition key. 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 an index with a prefix ).

 

 

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

 

2.7.2. Global partition Index

A global partition index contains keys from multiple table partitions in an index partition. The partition key of a global partition index is a value of different or specified range in the partition table. When creating a global partition index, you must define the range and value of the partition key. The global index can only be a B-tree index. By default, Oracle does not maintain global partition indexes. If a partition is intercepted, added, split, or deleted, the GLOBAL partition 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 a prefix

Generally, the global index with a prefix is not partitioned by a peer partition in the underlying table. There is no limit on the peer partitions of an index. However, when Oracle generates a query plan or performs partition maintenance, it does not take full advantage of the Peer partitions. If an index is a peer-to-peer partition, you must create it as a local index, so that Oracle can maintain this index and use it to delete unnecessary partitions, as shown in. In the three index partitions of the graph, each partition contains an index entry pointing to the row in multiple table partitions.

Partitioned and globally prefixed indexes

 

 

Tip: if a global index will be a peer-to-peer partition, you must create it as a local index, so that Oracle can maintain this index and use it to delete unnecessary partitions.

 

2.7.2.2 index without a prefix

Oracle does not support global indexes without a prefix.

 

2.8 bitmap join Index

Bitmap join index is a bitmap index connected to two tables. This index is used in the data warehouse environment to improve the query performance of connecting dimension tables and fact tables. When creating a bitmap connection index, the standard method is to connect dimension tables and fact tables commonly used in the index. When you query fact tables and dimension tables in a single query, you do not need to perform connections because there are available connection results in the bitmap connection index. The compressed bitmap is used to connect ROWID in the index to further improve performance and reduce the number of I/O required for data access.

 

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 bitmap join syntax is special. It contains the FROM clause and the WHERE clause and references two separate tables. An index column is usually a description column in a dimension table. If the dimension is CUSTOMER and its primary key is mermer_id, a column such as Customer_Name is usually indexed. If the fact table name is SALES, you can use the following command to create an index:

Create bitmap index SALES_CUST_NAME_IDX

On SALES (CUSTOMER. Customer_Name) from SALES, CUSTOMER

Where SALES. Customer_ID = CUSTOMER. Customer_ID;

 

If you use the WHERE clause that specifies the Customer_Name column value to query the SALES and CUSTOMER tables, the optimizer can use bitmap to connect the index to quickly return rows that match the connection condition and Customer_Name condition.

 

The usage of Bitmap-connected indexes is generally restricted:

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

2) The columns used for connection must be the primary key or unique constraint in the dimension table. If the column is a composite primary key, each column in the connection must be used.

3) bitmap join indexes cannot be created for the index organization table, and the limitations applicable to conventional Bitmap indexes are also applicable to bitmap join indexes.

 

Note: This article is based on Oracle Database 10g performance adjustment and optimization.

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

Blog: blog.csdn.net/tianlesoftware