Oracle Index Detailed

Oracle Index Details (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 a program. A bad choice can cause a deadlock and result in a sharp drop in database performance or a process termination. If you make the right choice, you can use resources reasonably, so that processes that have been running for hours or even days are completed in minutes, greatly improving the efficiency of data manipulation statements.

Reading "2017-12-26" "22:35:36":

• • B-Tree index (default type)
    
  • Bitmap index
  • HASH Index
    
  • Index Organization Table Index
  • Invert key (reverse key) index
  • Function-based indexing
  • Partitioned indexes (local and global indexes)
  • Bitmap Connection Index

One, B tree index

The B-tree index is a common index in Oracle. It is the default index type when the index is created. A B-tree index can be a (simple) index of a column, or it can be an index of a composite/composite (multiple columns). A B-tree index can include up to 32 columns. The values of the indexed columns are stored in the index. Therefore, you can create a composite (composite) index that can directly satisfy the query without having to access the table. This reduces the amount of I/O by not retrieving data from the table.

Characteristics:

1. Suitable with a large number of increases, deletions, and changes (OLTP);
2. Cannot use a query that contains an OR operator;
3. Suitable for high cardinality columns (multiple unique values);
4. Typical tree-like structure;
5. Each node is a data block, mostly physical layer, two layers or three layers of uncertainty, logically three layers;
6. The leaf block data is sorted, incrementing from left to right;
7. The scope of the index is placed in the branch block and the root block;

Second, bitmap index

Bitmap indexes are well suited for decision support systems (decision supports SYSTEM,DSS) and data warehouses, and they should not be used for tables accessed through transactional applications. They can access very large tables using a column that is less than the medium cardinality (the number of different values). Although the bitmap index can be up to 30 columns, they are usually used only for a small number of columns. Bitmap indexes, such as gender, are required for columns with lower cardinality. When you combine multiple bitmap indexes in a single query, you can make performance improvements significantly. Bitmap indexes use fixed-length data types that are better than variable-length data types. Larger blocks also improve the storage and reading performance of bitmap indexes.

Characteristics:

1. Suitability and decision support system;
2. The cost of doing the UPDATE is very high;
3. Very suitable for queries of the OR operator;
4. A bitmap index can be built when the base ratio is low;

Third, the HASH index

Hash clusters must be used to use hash indexes. When a cluster or HASH cluster is established, a cluster key is defined. This key tells Oracle how to store tables on a cluster. When the data is stored, all the rows associated with the cluster key are stored on a database block. A HASH index is useful in situations where there are restrictions that require specifying a certain value rather than a range of values.

Characteristics:

1. A HASH index may be the quickest way to access data in a database, but it also has its drawbacks, and underestimating the number of different values of cluster keys can cause cluster conflicts;
2. If the additional space cannot be allocated for the future growth of the cluster, the HASH cluster may not be the best choice;
3. HASH clusters may not be the best choice if the application often makes full table scans on the cluster tables;
4. In general, hashing is very effective for some static data that contains ordered values;

Iv. Index Organization table

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

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

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

Five, reverse key index

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

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

Skills:

1. You can use a Reverse key index if you have a limited disk capacity and you want to perform a large number of sequential loads.
2. You cannot use a Reverse key index in conjunction with a bitmap index or an indexed organization table. Because the bitmap index and Index organization table cannot be reversed key processing.

Vi. Function-based indexing

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

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

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

1 Select * from emp where job = ' MGR ';

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

1 create index emp$upper_job on EMP (UPPER(JOB));

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

1. Can I restrict the functions that are used on this column? If so, can you limit all functions that are performed on this column?
2. Is there enough storage space to handle additional indexes?
3. What is the effect of increasing the number of indexes on each column on the performance of DML statements executed against that table?

Function-based indexing is useful, but you must be careful when you implement it. The more indexes you create on a table, the more time it takes for the INSERT, UPDATE, and DELETE statements to execute. Note: For function-based indexes used by the optimizer, the initial parameter Query_rewrite _ ENABLED must be set to TRUE.

Example:

1 Select Count(*) fromSamplewhereRatio (BALANCE,LIMIT)>.5;2Time20.1minutes3 4 Create IndexRatio_idx1 onsample (ratio (balance, limit));5 6 Select Count(*) fromSamplewhereRatio (BALANCE,LIMIT)>.5;7Time7Seconds

Oracle Index Detailed