Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the concept and classification of Oracle index. The article is rich in content and analyzed and described from a professional point of view. I hope you can get something after reading this article.

I. introduction to the index

1.1 Syntax for index creation:

CREATE UNIUQE | BITMAP INDEX.

ON.

(| ASC | DESC

| | ASC | DESC,...) |

TABLESPACE

STORAGE

LOGGING | NOLOGGING

COMPUTE STATISTICS

NOCOMPRESS | COMPRESS

NOSORT | REVERSE

PARTITION | GLOBAL PARTITION

Related instructions

1) UNIQUE | BITMAP: specifies that UNIQUE is the unique value index, BITMAP is the bitmap index, and B-Tree index is omitted.

2) | ASC | DESC: you can jointly index multiple columns. When it is expression, it is "function-based index".

3) TABLESPACE: specify the table space in which the index is stored (it is more efficient when the index and the original table are not in the same table space)

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

5) LOGGING | NOLOGGING: whether to generate redo logs for the index (try to use NOLOGGING for large tables to reduce footprint and improve efficiency)

6) COMPUTE STATISTICS: collect statistics when creating a new index

7) NOCOMPRESS | COMPRESS: whether to use "key compression" (using key compression to remove duplicate values in a key column)

8) NOSORT | REVERSE:NOSORT indicates that the index is created in the same order as in the table, and REVERSE indicates that the index values are stored in reverse order

9) PARTITION | NOPARTITION: indexes created can be partitioned on partitioned and unpartitioned tables

1.2 Index characteristics:

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

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

Third, the connection between the meter and the table can be accelerated, especially in achieving the referential integrity of the data.

Fourth, when using grouping and sorting clauses for data retrieval, the time of grouping and sorting in the query can also be significantly reduced.

Fifth, through the use of index, we can use the optimization hidden device in the process of query to improve the performance of the system.

1.3 insufficient index:

First, it takes time to create and maintain an index, which increases 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 amount of physical space, if you want to establish a clustered index, then the space needed will be more.

Third, when the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the speed of data maintenance.

1.4 the characteristics of the index column should be built:

1) you can speed up the search on columns that often need to be searched.

2) on the column as the primary key, force the uniqueness of the column and organize the arrangement structure of the data in the table

3) on the columns that are often used to join, these columns are mainly foreign keys, which can speed up the connection.

4) create an index on a column that often needs to search by range, because the index is sorted and its specified range is continuous

5) create an index on columns that often need to be sorted, because the index is already sorted, so that the query can take advantage of the sorting of the index to speed up the sorting query time.

6) create indexes on columns that are often used in the WHERE clause to speed up the judgment of conditions.

1.5 characteristics that index columns should not be built:

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, indexing or no indexing does not improve query speed. On the contrary, due to the increase of the index, the maintenance speed of the system is reduced and the space requirement is increased.

Second, the index should not be added to 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, the data rows of the result set account for a large proportion of the data rows in the table in the results of the query, that is, a large proportion of the data rows that need to be searched in the table. Increasing the index does not significantly speed up the retrieval speed.

Third, columns defined as blob data types should not be indexed. This is because these columns either have a large amount of data or have very few values.

Fourth, indexes should not be created when modification performance is much greater than retrieval performance. This is because modification performance and retrieval performance contradict each other. When the index is added, the retrieval 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, indexes should not be created when modification performance is much greater than retrieval performance.

1.6 restricted index

Limiting indexing is one of the mistakes that inexperienced developers often make. There are many traps in SQL that make some indexes unusable. Here are some common questions:

1.6.1 use the non-equal operator (,! =)

The following query performs a full table scan even if it has 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 rather than 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, indexes can be used to avoid full table scans.

1.6.2 using IS NULL or IS NOT NULL

Using IS NULL or IS NOT NULL also restricts the use of indexes. Because the null value is not defined. There is a lot of trouble with using NULL in SQL statements. Therefore, it is recommended that developers set the columns that need to be indexed to NOT NULL when creating the table. If the indexed column has a null value in some rows, this index is not used (unless the index is a bitmap index, which is discussed in more detail later).

1.6.3 using functions

If you do not use function-based indexes, using functions on columns that have indexes in the WHERE clause of the SQL statement 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) = '01MAYMAYMAYMAYMI81'

Change the above statement to the following statement so that you can find it through the index.

Select empno,ename,deptno from emp where hiredate set timing on

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

The index has been created.

Time spent: 00: 02: 03.93

SQL > select count (areacode) from tt

COUNT (AREACODE)

-

7230369

Time spent: 00: 00: 08.31

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

COUNT (AREACODE)

-

7230369

Time spent: 00: 00: 07.37

1.15 Type of index

B-tree index bitmap index HASH indexing table

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

II. 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 wrong choice can cause a deadlock and lead to a sharp decline in database performance or process termination. If you make the right choice, you can make rational use of resources so that processes that have been running for hours or even days can be completed in minutes, which will make you a hero immediately. Each index option is briefly discussed below.

The types of indexes discussed below:

B-tree index (default type)

Bitmap index

HASH index

Index organizes table index

Reverse key (reverse key) index

Function-based index

Partitioned indexes (local and global indexes)

Bitmap join index

2.1 B-tree index (default type)

The B-tree index is a general index in Oracle. It is the default index type when creating an index. A B-tree index can be a single column (simple) index or a combined / composite (multiple column) index. A B-tree index can include up to 32 columns.

In the example in the following figure, the B-tree index is on the last_name column of the employee table. The binary height of the index is 3; next, the Oracle passes through two branch block to reach the leaf block that contains the ROWID. In each branch block, the branch line contains the ID number of the next block in the chain.

The leaf block contains the index value, ROWID, and pointers to the previous and next leaf blocks. Oracle can traverse the binary tree in both directions. The B-tree index holds the ROWID value of each data row that has a value on the index column. Oracle does not index rows on index columns that contain null values. If the index is a combined index of multiple columns and the column contains a null value, the row will be in the index column containing the NULL value and will be treated as empty (as NULL).

Tip: the values of index columns are stored in the index. Therefore, you can create a composite (composite) index that satisfies the query directly without accessing the table. This eliminates the need to retrieve data from the table, thereby reducing the amount of I / O.

B-tree features:

Suitable for a large number of add, delete, modify (OLTP)

Cannot use a query that contains an OR operator

Suitable for columns with high cardinality (unique values are many)

Typical tree structure

Each node is a data block.

Most of them are one, two or three layers in physics and three layers in logic.

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 ideal for decision support systems (Decision Support System,DSS) and data warehouses and should not be used for tables accessed through transaction processing applications. They can access very large tables using columns with less to medium cardinality (the number of different values). Although bitmap indexes can have 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, which is the base column of the bitmap index if the user frequently queries the table based on the value of the Sex column. When a table contains multiple bitmap indexes, you can experience the true power of bitmap indexes. If there are multiple bitmap indexes available, Oracle can merge the result sets from each bitmap index, quickly deleting unnecessary data.

Bitmapt features:

Suitability and decision support system

The cost of doing UPDATE is very high.

Queries that are well suited to the OR operator

Bitmap indexes can only be built when the cardinality is relatively small.

Tip: bitmap indexes are required for columns with a low cardinality. One such example is the gender column, which has two possible values: male or female (base 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 bitmap indexes.

When most entries do not add new values to bitmaps, bitmap indexes are generally better at loading tables (insert operations) in batch (single-user) operations than B-trees. Bitmap indexes should not be used when multiple sessions insert rows into a table at the same time, as is the case in most transaction applications.

Example

Let's take a look at a sample table, PARTICIPANT, which contains survey data from individuals. Columns Age_Code, Income_Level, Education_Level, and Marital_Status all include their respective bitmap indexes. The following figure shows the data balance in each histogram and the execution path to the query that accesses each bitmap index. The execution path in the figure shows how many bitmap indexes have been merged, which shows a significant improvement in performance.

As shown in the figure above, the optimizer in turn uses four separate bitmap indexes whose columns are referenced in the WHERE clause. Each bitmap record pointer, such as 0 or 1, indicates which rows in the table contain known values in the bitmap. With this information, Oracle performs a BITMAP AND operation to find out which rows will be returned from all four bitmaps. The value is then converted to a ROWID value, and the query continues to complete the rest of the processing. Note that all four columns have a very low cardinality, and using the index can return matching rows very quickly.

Tip: performance can be significantly improved by merging multiple bitmap indexes in a single query. Bitmap indexes use fixed-length data types rather than variable-length data types. Larger blocks also improve the storage and read 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; 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.

Bitmap indexing is not recommended 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 that contain a range of ROWID, so Oracle must lock all ranges of ROWID for a given value. This lock type can cause deadlocks in some DML statements. SELECT statements are not affected by this locking problem.

Usage restrictions for bitmap indexes:

The rule-based optimizer does not consider bitmap indexes.

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

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 indexes in a heavy OLTP environment

2.3 HASH Index

You must use a HASH cluster to use HASH indexes. When you set up a cluster or HASH cluster, you define a cluster key. This key tells Oracle how to store tables on the cluster. When storing data, all rows related to this cluster key are stored on a database block. If the data is all stored on the same database block and the HASH index is used as an exact match in the WHERE clause, the Oracle can access the data by executing a HASH function and an iGano-- and by using a B-tree index with a binary height of 4, you need to use four Icano when retrieving the data. As shown in the following figure, 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 rows based on the HASH function.

An HASH index may be the fastest way to access data in a database, but it also has its own disadvantages. 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 you create a HASH cluster. Numbers that underestimate the different values of cluster keys may cause cluster conflicts (the key values of two clusters have the same hash value). Such conflicts are very resource-consuming. Conflicts can result in buffer overflows used to store additional rows, which in turn results in additional Icano. 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 are not sure how much space is needed to maintain all the rows on a cluster key, it may result in a waste of space. If additional space is not allocated for the future growth of the cluster, the HASH cluster may not be the best choice. If applications often perform full table scans on clustered tables, HASH clustering may not be the best choice either. Full table scans can be very resource-intensive due to the need to allocate the remaining space of the cluster for future growth.

Be careful before implementing a HASH cluster. You need to take a comprehensive look at the application to make sure that you already know a lot about tables and data before implementing this option. In general, HASH is very effective for static data that contains ordered values.

Tip: HASH indexes are useful in situations where there are constraints (you need to specify a definite value instead of a range of values).

2.4 Index organization table

The index organizes the table to change the storage structure of the table to a B-tree structure and sort by the primary key of the table. 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, the ROWID is not associated to the rows of the table.

For some statements involving exact matching and range search, the index 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 ordered. Because the values of the key columns are not duplicated in both the table and the index, the space required for storage is reduced.

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

Tip: you can create a secondary index on an index organization table.

2.5 reverse key index

When loading some ordered data, the index is sure to encounter some bottlenecks associated with Imax O. During data loading, some indexes and disks are bound to be used much more frequently than others. To solve this problem, index tablespaces can be stored on a disk architecture that physically divides files on multiple disks.

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

Tip: if your disk capacity is limited and you have to perform a lot of orderly loading, you can use the reverse key index.

You cannot use a reverse key index with a bitmap index or an index organization table. Because you cannot reverse key processing for bitmap indexes and index organization tables.

2.6 function-based indexing

You can create a function-based index in a table. Without a function-based index, any query that performs a function on a column cannot use the index of that column. For example, the following query cannot use an index on a 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 indexes that allow index access to columns or data that support function-based access. You can create an index on the column expression UPPER (job) instead of building an index directly on a JOB column, such as:

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

Although function-based indexes are useful, there are some issues that must be considered before building them:

Can you limit the functions used on this column? If so, can you limit all functions executed on this column?

Is there enough storage space for additional indexes?

How does the increase in the number of indexes on each column affect the performance of DML statements executed against that table?

Function-based indexes are very useful, but you must be careful when implementing them. The more indexes you create on the table, the more time it takes to execute the 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 is simply dividing an index into multiple fragments. By dividing an index into multiple fragments, you can access smaller fragments (and faster) and store them on different disk drives (avoiding the Iwhite O problem). Both B-tree and bitmap indexes can be partitioned, while HASH indexes cannot be partitioned. There are several ways to partition: the table is partitioned and the index is not partitioned; the table is not partitioned and the index is partitioned; both the table and the index are partitioned. No matter which approach you take, you must use a cost-based optimizer. Partitions offer more possibilities to improve performance and maintainability

There are two types of partitioned indexes: local partitioned index and global partitioned index. Each type has two subtypes, with a prefix index and an unprefixed index. Indexes on each column of a table can have a combination of various types of indexes. If a bitmap index is used, it must be a local index. The main reason for partitioning the index is that it can reduce the size of the index to be read, and putting the partition in different table spaces can improve the availability and reliability of the partition.

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

2.7.1. Local partitioned index (commonly used index)

Local indexes can be partitioned using the same partitioning keys and scope bounds as tables. The partition of each local index contains only the key and ROWID of the table partition with which it is associated. The local index can be a B-tree or a bitmap index. 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 individual partitions can be added, intercepted, deleted, split, offline, etc., 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

Prefixed indexes contain keys from partitioning keys and use them as a precursor to the index. For example, let's review the participant table again. After creating the table, use the survey_id and survey_date columns for range partitioning, and then create a prefixed local index on the survey_id column, as shown in the following figure. All partitions of the index are equally partitioned, that is, the partitions of the index are created using the same range bounds of the table.

Tip: local prefixed indexes allow Oracle to quickly weed out unnecessary partitions. That is, partitions that do not contain any values in the WHERE conditional clause will not be accessed, which also improves the performance of the statement.

2.7.1.2 indexing without prefix

An index without a prefix does not have the leading column of the partitioning key as the leading column of the index. If you use the same partitioned table with the same partitioning keys (survey_id and survey_date), the index built on the survey_date column is a local unprefixed index, as shown in the following figure. You can create a local unprefixed index on any column of a table, but each partition of the index contains only the key value of the corresponding partition of the table.

If an unprefixed index is to be made unique, the index must contain a subset of the partitioning 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 a prefixed index).

Tip: for a unique unprefixed index, it must contain a subset of partitioning keys.

2.7.2. Global partition index

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

2.7.2.1 Index with prefix

Typically, the global prefix index is not partitioned by peer in the underlying table. There are no factors that limit peer partitioning of an index, but Oracle does not make full use of peer partitioning when generating query plans or performing partition maintenance operations. If the 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 delete unnecessary partitions, as shown in the following figure. Each of the three index partitions in the figure contains index entries that point to rows in multiple table partitions.

Partitioned, globally prefixed index

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

2.7.2.2 indexing without prefix

Oracle does not support global indexes without prefixes.

2.8 Bitmap join Index

A bitmap join index is a bitmap index based on a join of two tables, which is used in a data warehouse environment to improve the performance of queries that join dimension tables and fact tables. When creating a bitmap join index, the standard method is to join dimension tables and fact tables that are commonly used in the index. When the user combines the query fact table with the dimension table in a query, the join does not need to be performed because there are already available join results in the bitmap join index. Further improve performance by compressing the bitmap join ROWID in the index and reduce the number of iCandle Os required to access the data.

When you create a bitmap join 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 the WHERE clause, and references two separate tables. An index column is usually a description column in a dimension table-- that is, if the dimension is CUSTOMER and its primary key is CUSTOMER_ID, then columns such as Customer_Name are usually indexed. 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 that specifies the value of the Customer_Name column, the optimizer can use the bitmap join index to quickly return rows that match the join and Customer_Name conditions.

The use of bitmap join indexes is generally limited:

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

2) the column used to join must be a primary key or unique constraint in the dimension table; if it is a compound primary key, each column in the join must be used.

3) Bitmap join indexes cannot be created on index organization tables, and the restrictions that apply to regular bitmap indexes also apply to bitmap join indexes.

The above is the concept and classification of the Oracle index shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.