Oracle Database Index

Oracle Database Index 1 index basic concept index is a data object used to accelerate data access. Rational Use of indexes can greatly reduce the number of I/O operations, thus improving data access performance. Single-Column index: an index created on a column. For example, [SQL] -- Oracle creates a B-tree index by default. create index name: on Table Name (column name) composite index: an index created on two or more columns. Multiple indexes can exist on the same table, but the combinations of these indexes must be different. For example, [SQL] create index emp_idx1 on emp (ename, job); create index emp_idx2 on emp (job, ename); index usage principle: Creating an index on a large table makes sense; in the where clause (where clause usually places fields with large entropy values behind them, and the query condition of the SQL statement is from right to left) or the column frequently used in the connection condition to create an index; the index level should not exceed four layers. Index disadvantages: index creation requires the system to occupy about 1.2 times of the hard disk and memory space of the table to store the index. When updating data, the index must be updated at the same time, to maintain data and index consistency. Therefore, improper indexes will reduce system performance, because the index needs to be updated at an additional time during data insertion, modification, and deletion. For example, it is inappropriate to create an index for the following fields: few or never reference fields; logical fields, such as male or female (yes or no. In short, index creation improves query efficiency at the expense of a certain amount of system resources (additional storage and additional Index Update Time for deletion and modification operations, DBAs need to carefully consider which fields to create indexes and which indexes to create. 2. The function of indexing supports fast query. For databases that store large amounts of data, the linear search efficiency is very low. The indexing technology enables the database to support secondary Linear Time searches to improve query performance. Indexes are all data structures that improve query performance. Currently, many data structures can be used to increase the query speed. In fact, a large part of the research in the computer science field is the research and analysis of index data structures. The Research on index data structure requires consideration of the compromise between query performance, index size and Index Update performance. Many indexes provide the query performance of log time complexity (O (logn (N). In some cases, O (1) time complexity can be obtained. Database constraint indexes can also be used to implement database constraints, such as UNIQUE, EXCLUSION, primary key, and foreign key. The UNIQUE index limits the columns referenced by it. The value of this column is UNIQUE. The database system usually creates an index on the column of the primary key (primary key) by default. 3. The index architecture in Sybase is divided into clustered indexes (clustered index) and nonclustered indexes (non-clustered index ). Clustered index is a special index that sorts the physical storage of records in a table. Therefore, each table can have only one clustered index. The leaf node of the clustered index contains the data page. In non-clustered indexes, the logical order of indexes is different from that of physical storage. Leaf nodes without clustered indexes do not contain data pages, but contain index rows. Oracle does not have these two indexes. Technically speaking, Oracle's IOT (index organized table) can implement clustered indexes, but this is generally not recommended. Oracle provides a structure called cluster, but it is irrelevant to the two indexes. Cluster is a method to store multiple tables in the same block. Normally, a block contains the data of one table. In a cluster, data from multiple tables is shared with the same block. 4 Oracle indexes are stored in B-tree indexes, reverse indexes, and bitmap indexes. B-tree indexes are created on columns with few duplicate values, bitmap indexes are created on columns with many duplicate values and relatively fixed values. Based on the number of index columns, it can be divided into single-column indexes and compound indexes. Based on the uniqueness of index column values, it can be divided into unique indexes and non-unique indexes. In addition, there are function indexes, global indexes, and partition indexes. Oracle Database provides the following types of indexes: B-tree indexes (B-tree indexes are created by default in Oracle) B-tree clustered indexes (B-tree cluster indexes) hash cluster indexes (Reverse key indexes) Bitmap indexes (Bitmap indexes) Bitmap join indexes) oracle also supports function-based indexes and domain indexes of an application ). 4.1 function-based INDEX the function-based index SQL statement is as follows: [SQL] -- Basic Structure CREATE [UNIQUE] INDEX index_name ON table_name (function1, function2 ,. function_n) [compute statistics]; -- Example: create index supplier_idx ON supplier (UPPER (supplier_name); compute statistics indicates whether to collect index statistics. 4.2 currently, bitmap indexes are mainly B-Tree indexes that store the RowID of key values and key values in the index structure. Bitmap indexes are mainly created for a large number of Columns with the same value, such as category, operator, and department ID. Bitmap indexes only store the start and end rowids and bitmaps of key values, which occupy a very small amount of space. Bitmap indexes are very suitable for Data Warehouse applications because the data warehouse processes a large number of ad hoc queries with almost no concurrent transactions. Bitmap indexes provide the following advantages: it shortens the time for ad hoc queries and saves storage space compared with other indexing technologies. High Performance and efficient parallel DML and loading Bitmap indexes are not suitable for OLTP applications, OLTP applications need to process a large number of concurrent transactions for modifying data. Bitmap indexes are more suitable for decision-making support systems based on data warehouses, because the decision-making support system mainly queries data rather than modifying data. Bitmap index pages are NOT suitable for comparing OR greater than columns, but for equality queries, especially for the AND, OR, and not operations. B-tree indexes are more suitable for comparing values greater than AND less. [SQL] create bitmap index name ON table name (column name); 5 Oracle INDEX implementation Oracle database uses B-tree structure to organize indexes to accelerate data access. If no index exists, the entire database can be scanned sequentially. For tables with n rows, the average number of scanned rows is n/2. If these records are organized into a B-tree Structure Based on one of their columns, a leaf node represents a piece of data, the average query time for a record from a table with n rows is log (n ). This is the basic principle of Oracle database indexing. The branch node of B-tree contains the index of its subnode. The lowest layer node (leaf node) contains the index data value and its corresponding rowid, which is used to locate the corresponding data table row.