Oracle data lookups and indexing and indexing failures-------help understand indexes to optimize our Oracle database

Classification of indexes Logically: Single Column Single-line index Concatenated multiple rows Index Unique Unique index Nonunique Non-Unique index function-based Function Index Domain Field Index In physics: Partitioned Partitioning Index nonpartitioned Non-partitioned index B-tree: Normal B-Tree Rever Key Reverse Transformation B-Tree Bitmap Bitmap Index Index structure: B-tree: Suitable with a large number of increase, 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; Bitmap: 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; Tree type structure: Index Header Start rowID, end rowID (list the maximum range of indexes first) BITMAP Each bit corresponds to a rowid, its value is 1 or 0, if it is 1, indicating that the bit corresponding to the ROWID has a value; =========================================================== There are several ways to find data in Oracle, to understand how to find data, to help us read the execution plan, and to optimize our Oracle database system by executing the plan. There are mainly the following ways to find data: 1, full table scan and ROWID lookup data 2, find data by index 1.1 Full table Scan (Scans) Sometimes the Oracle database evaluates the optimal execution plan by taking a full table scan when it comes to fetching large amounts of data, because the full table scan is optimal. Generally taken out of the data in the table data 5%--10% will occur table scan, generally in the OLTP system to avoid table scanning. In order to better use table scanning, improve efficiency, there are several zoning methods in Oracle, improve the efficiency of table scanning, so that the table according to the specific business logic to partition, as far as possible partition elimination, reduce the size of the table scan, improve performance. 1.2 ROWID Lookup data (Table Access by ROWID or R Owid lookup) Row rowID is a row of data in the database files, blocks and rows in the specific location of the block, so Oracle is the fastest positioning method. This method only reads one IO at a time and does not involve multiple IO. Currently through index lookup, there are mainly 5 kinds of index lookup methods: 1, index unique lookup (index unique scan) 2, Index range lookup (index range Scan) 3, index full scan (scan) 4, Index fast Scan (index fast full scan) 5, Index jump lookup (index skip scan) 2.1, Index unique lookup (index unique scan) Oracle often implements a unique scan when the query column is a unique or primary KEY constraint (it guarantees that the statement only accesses a single line). This lookup method is highly efficient. 2.2, Index range lookup (index range Scan) Using one index to access multiple rows of data, the typical case of using an index range scan on a unique index is to use the scope operators (such as >, <, <>, >=, <=, between) in the predicate (where constraints) 2.3, index full scan (scan) Full index Scan. The data that is queried is sorted, and the data queried at this time must be obtained directly from the index. At the same time, the order by sort data should be read by index. As follows: Explain plan for select Empno,ename from Big_emp ORDER by Empno,ename 2.4, Index fast Scan (index fast full scan) Very similar to the index full scan, but one notable difference is that it does not sort the queried data, that is, the data is not returned in a sort order, in which case the multiple read function can be used, or the parallel reads can be used in order to maximize throughput and shorten execution time. Index fast global scanning, which occurs without an order by case, is as follows: Explain plan for select Empno,ename from Big_emp 2.5, Index jump lookup (index skip scan) The Where Condition column occurs often in the case of a leading column that is not indexed. As follows: CREATE INDEX i_emp on EMP (empno, ename); Select/* + INDEX_SS (EMP i_emp) */job from emp where ename = ' SMITH '; Summarize: Index full scan (indexing scan): There is a sort that uses the sort function of index. Principle:----to intermediate level---leaf level through root level Index fast full Scan (Index quick Scan): No sort, you can use multiple read mode to improve query speed, because the data requirements are unordered. That is, you can use more than one block of queries, the data taken out is the order in which the data is stored. When the index full scan is performed, Oracle navigates to the root block of the indexes, then to the branch block (if any), and then to the first leaf block, which is then read in the order of the Leaf block's bidirectional linked list. The blocks it reads are sequential and sorted. and the index fast full scan is different, it is from the beginning of the segment, read contains bitmap blocks, root block, all branch, the leaf block, the order of reading is completely physical storage location decision, and take a lot of read, no time to read Db_file_ Multiblock_read_count A =================== There are several cases of general index failure 1, the amount of query data accounted for the proportion of the total amount of larger, go full table scan 2, in the index field with functions such as operation, the index will not be used, unless the field to establish a function of the index 3, compared with the like '%. % ', indexes don't work either Also refer to: http://www.2cto.com/database/201304/205513.html -------- More knowledge of index classification: Http://wenku.baidu.com/view/d67e7822192e45361066f55f%E2%80%8E