Two common oracle Indexes

Two common oracle index B * tree indexes
The bottom layer of a tree is called a leaf node, which contains each index key and a rowid. The indexed leaf nodes constitute a two-way linked list. Once you find that you want to "start" from the leaf node, it is easy to perform an orderly scan. Compression key index: each key entry is divided into two parts: "prefix" and "suffix". The prefix is created on the first few columns of the tandem index, and the suffix is on the last few columns of the index key, is the unique part of the index entry where the prefix is located. Www.2cto.com if you have a group of columns, some of which are sorted in ascending order (ASC), and some others are sorted in descending order (DESC), you can specify to use a descending index.
Indexes are used to access rows in a table: Read indexes are used to access rows in a table. At this time, you want to access a few rows in the table (only a small percentage of 1% ~ Between 20% ). The index is used to answer a query: The index contains enough information to answer the entire query. I do not need to access the table at all. In this case, the index is used as a "thinner" table. Clustering factor: the number of logical I/O operations performed on the table when the entire table is read through the index. CLUSTERING_FACTOR column in The USER_INDEXES view: if this value is close to the number of blocks, it indicates that the table is quite orderly and has a good structure. In this case, the index entries in the same leaf block may point to rows in the same data block.
Q if this value is close to the number of rows, the order of the table may be very random. In this case, the index entries on the same leaf block are unlikely to point to rows on the same data block. When Oracle performs a range scan on the index structure, if the next row in the index is located on the same database block in the previous row, no more I/O will be executed to obtain the table block from the buffer cache. The www.2cto.com index is not always an appropriate access method. The Optimizer may choose not to use indexes. For a table, only one index can have a suitable clustering factor! The rows in the table may be sorted in one way. If you think that the physical clustering of data is very important, you can consider using an IOT, B * tree clustering, or consider clustering when continuously recreating a table. One factor is how much data (as a percentage of data) needs to be accessed through an index, and the other is how data is distributed. Bitmap indexes use an index key to store pointers to multiple rows.

It is used to divide the number of different items in a row set by the number of rows, which should be a small number (close to 0 ). Author's common