indexes are database objects that can be used to improve the performance of some queries by creating indexes. when you create a primary key or a unique constraint condition, the server automatically creates the index at the same time.
Indexes have the following characteristics:? is a Scenario object? Can Oracle Server use pointers to speed up row retrieval? Data can be quickly found by using fast path access methods to reduce diskinput/output (I/O)? Not related to indexed tables? Automatic use and maintenance of Oracle Server by Oracle Server index is a scenario object that can speed up row retrieval with pointers. You can create an index explicitly,You can also create indexes automatically. If an index is not established on the column, the entire table is scanned. Use an index to directly and quickly access rows in a table. The effect is to quickly find data by using the index path to reduceI/O to less disks. Indexes are automatically used and maintained by Oracle Server. After you create an index, you do not need the user to directlyAny action has been taken. An index is an optional structure associated with a table. Creating indexes can improve the performance of updating and retrieving data. The Oracle index provides a direct access path to the data rows. You can create indexes on one or more columns of a table. After the index is created, the Oracle server automatically maintains and uses the index. updates to the table data (such as adding new rows, updating rows, or deleting rows) are automatically propagated to all relevant indexes, which are completely transparent to the user. indexes are logically and practically independent of indexed tables。 This means that you can create or delete an index at any time,without having any effect on the base table or other indexes. Note: When you delete a table, the corresponding index is also deleted.
You can use several types of index structures as needed:? The B-tree index takes the form of a binary tree, which is the default index type. In the bitmap index, each unique value that is indexed has a bitmap,each position represents a row, which may or may not containincluded) index value. For low cardinality columns, this is the best structure. The following are the most common index forms:? B-Tree? The key values of the bitmap B-tree index are stored in the balance tree (b-tree) to provide a fast binary search. In the bitmap index, each unique key value to be indexed has a bitmap. In each figure, for the index to be builteach row in the table is reserved for one bit. This makes it quick to find unique values, which means that indexed columnsthe base is low. The gender indicator is an example of such an index. This indicator contains only the values "M" and "F",Therefore, you only need to search for two bitmaps. For example, if you use a bitmap index on a phone_number column, you need toto manage and search too many bitmaps, this situation is very inefficient. Therefore, use a bitmap index for low cardinality columns.
structure of the B-tree indexThe top level of the index is the root, which contains entries that point to the next level of the index. The next level is the branch block, which also points to theindex The next-level block. At the lowest level is the leaf node, which contains index entries that point to the table rows. Yushang inThe two directions are interrelated, which makes it easy to scan the index in ascending or descending order of key values. format of index leaf entriesIndex entries include the following components:? The entry header, used to store the number of columns and lock information.? Key column Length-value pairs, which define the column size in the key, followed by the column value (the number of pairs is the indexthe maximum number of columns). ? The rowid of the row that contains the key value. attributes of index leaf entriesIn the B-tree index of a non-partitioned table:? When multiple rows have the same key value, if the index is not compressed, the key value appears duplicated. When a row contains all the keys that are listed as NULL, the row does not have a corresponding index entry. Therefore, when the whereclause specifies NULL, it always causes a full table scan. ? Because all rows belong to the same segment, use restricted rowID to point to the table row.performance of DML operations on indexesWhen a DML operation is performed on a table, the Oracle server maintains all indexes. The following is a description of the DML command performed on the indexThe resulting effect:? Performing an insert operation causes the index entry to be inserted in the corresponding block. Deleting a row only causes the index entry to be tombstoned. Deleted before all entries in the block are deletedthe space occupied by the row is not available for new entries. ? Updating a key column causes the index to be tombstoned and inserted. The Pctfree setting has no effect on the index,except when it is created. Even if the index block has less space than the pctfree specified, you can also make the index blockAdd a new entry.
A bitmap index has advantages over a B-tree index in the following specific scenarios:? When a table has millions of rows and the cardinality of the key column is low, the unique value of the column is minimal. For example, for passportsthe gender and marital status columns in the Record table, the bitmap index may be preferable than the B-tree index. ? Often when querying with multiple where condition combinations involving or operators.? When there is a read-only activity or less update activity on the key column.structure of the bitmap indexBitmap indexes can also be organized in the form of B-trees, but leaf nodes store bitmaps for each key value, rather thanrowid list. Each bit in the bitmap corresponds to a possible rowid, and if the bit is set, it indicates athe row that corresponds to the ROWID contains the key value. , the leaf node of the bitmap index contains:? Entry header, which contains the number of columns and locking information? A key value consisting of the length-value pairs of each key column. In this example, the key contains only one column, the first oneThe key value is blue. ? Start rowID, which in this example specifies block Number 10, line number 0, and file Number 3.? End rowID, in this example it specifies block number 12, line number 8, and file Number 3.? A bitmap segment that consists of bit strings. (if the corresponding row contains a key value, the bit is set; if the corresponding row does not wrapcontains a key value, the bit is not set. Oracle servers use patented compression technology to store bitmap segments. )Start rowID is the first line rowid the bitmap segment points to, that is, the first bit of the bitmap corresponds to theROWID, the second bit of a bitmap corresponds to the next line in the block. End rowID is a pointer that is pointed to by a bitThe last row in the table covered by the segment. Bitmap indexing uses a restricted rowID. using bitmap indexingB-trees are used to locate leaf nodes that contain bitmap segments that specify key values. Start rowID and bitmap segments for positioningThe row that contains the key value. After you change the key columns in the table, you must also change the bitmap. This causes the associated bitmap segment to be locked. Because the lock isis obtained on the entire bitmap segment, so that before the end of the first transaction, the rows covered by the bitmap cannot beto update the service processing.
? A unique index ensures that each index value is unique.? Indexes can store their key values in ascending or descending order. The reverse key index stores its key-value bytes in reverse order. A composite index is a multi-column-based index. A function-based index is an index that is based on the return value of a function. The duplicate key value in the compressed index has been deleted. To improve retrieval efficiency, storing keys in descending order in the index can be more beneficial. This is based on the most common way of accessing datacome to the conclusion. the reverse key index stores the bytes of the indexed value in reverse order. This can reduce the amount of activity for a particular hotspot in the index. Ifwhen multiple users are processing data in the same order, the prefix of the key value (currently being processed) at any given momentpart is very close. Therefore, a large number of activities occur in the area of the indexed structure. The key value for the reverse byte styleafter indexing, the reverse key index distributes these activities throughout the index structure. indexes created by multiple-column combinations are called composite indexes. For example, you can create an index based on a person's first and last name: Create INDEX Name_ix on employees (last_name, first_name);use a function-based index to create an index on a function return value。 This function can be a built-in SQL function, externalThe PL/SQL function provided by the Department, or a function written by the user. As a result, the server performs a search based on an index expression, you do not have to call the function for each key value. The following example creates an index of the returned tree volume, and the tree volume is determined by the function rootThe tree species, height, and tree circumference (these are the columns in the Trees table) are calculated according to each species:CREATE INDEX Tree_vol_ix on TREES (Volume (species,height,circumference)); Then, the WHERE clause contains the expression volume (species,height,circumference) of anyany query can take advantage of this index, which improves execution speed because the volume of each tree has been calculated. Like PuFunction-based indexes are automatically maintained as well as through indexes.using a compressed index reduces the amount of disk space that is consumed during execution. The specified disk is empty due to the deletion of duplicate key valuesBurden can accommodate more index entries, so more entries can be read from disk in the same time period. Must beperforms compression and decompression, respectively, when writing indexes and reading indexes.
? Auto Create:If primary KEY is defined in the table definition orUnique constraint, a unique index is created automatically. ? Manually created:Users can speed up by creating non-unique indexes on columnsline access speed. You can create two types of indexes.? Unique index:If the column you define in the table has a primary KEY or a unique constraint,Oracle Server automatically creates an index of this type. The name of the index and the name of the constraint are the same. ? Non-unique index:This type of index can be created by the user. For example, you can use a join in a queryCreate a Foreign KEY column index to improve the retrieval speed. Note: You can create a unique index manually, but it is recommended that you create a unique constraint so that a unique index can be created implicitly.
? To create an index on one or more columns:
CREATE UNIUQE | BITMAP INDEX <schema>.<index_name>
on <schema>.<table_name>
(<column_name> | <expression> ASC | DESC,
<column_name> | <expression> ASC | DESC,...)
tablespace <tablespace_name>
STORAGE <storage_settings>
LOGGING | Nologging
COMPUTE STATISTICS
nocompress | Compress<nn>
Nosort | REVERSE
PARTITION | GLOBAL partition<partition_setting>
Related instructions
1)UNIQUE | BITMAP: Specifies unique index for unique values,BITMAP is a bitmap index, omitted as B-tree index.
2)<column_name> | <expression> ASC | DESC: Multiple columns can be indexed together, when expression is " Function-based index "
3)tablespace: Specifies the table space where the index is stored( the index and the original table are more efficient when they are not in a table space )
4)STORAGE: You can further set storage parameters for table spaces
5)LOGGING | Nologging: Whether to generate redo logs for indexes ( use nologging for large tables to reduce space consumption and increase efficiency )
6)COMPUTE STATISTICS: Collect statistics When creating new indexes
7)nocompress | Compress<nn>: Whether to use " key compression " ( using key compression to remove duplicate values in a key column )
8)Nosort | REVERSE: Nosort to create indexes in the same order as the table, REVERSE to Store index values in reverse order
9)PARTITION | Nopartition: Can be in partition tablepartitions the created index on the non-partitioned table
Specifies that the column values for single or multiple columns (the index is based on) must be unique when unique. Specifying bitmap indicates that a bitmap is used to create an index for each different key, instead of indexing each row individually. when a bitmap index is established, the rowids associated with the key value is stored as a bitmap.
? Improve query access to last_name columns in the Employees tableSpeed:CREATE INDEX Emp_last_name_idxOn employees (last_name); You can also click Indexes under the schema (schema) heading of the Administration page" link to view the Indexes (index) page. You can view indexed properties, or use the Actionsmenu to view the relevance of the index. An index can be explicitly or implicitly based on constraints that are set on the table. Defining a primary key is an implicitly-indexedexample, in order to enforce uniqueness on a column, a unique index is automatically created.
- Index Creation Guidelines
The index should be created in the following cases:A column contains a range of values that contain a large number of null values in a WHERE clause or join condition where one or more lists are used frequently, but most queries are expected to retrieve rows that are less than 2% to 4% of the number of rows in the tabledo not create an index under the following circumstances:These columns are not frequently used as a condition table in a query, or are expected to retrieve more rows than 2% to 4% tables that are updated frequently in an expression that has references to indexed columnsIt's not always better to index more .It is not the more table indexes that query faster. After each DML operation is committed on a table with an index, it means that it must bethe corresponding index must be updated. The more indexes that are associated with a table, the more the Oracle Server is updating after DML operations are performedthe more work you do with all indexes. under what circumstances to create an indexTherefore, the index should be created only in the following cases:? column contains a wide range of values? column contains a large number of null values? Use one or more columns frequently in a WHERE clause or join condition? The table is large, but it is expected that most queries will retrieve rows that are less than 2% to 4% of the number of rows. Note that if you want to enforce uniqueness, you should define a unique constraint in the table definition. You can now automaticallycreates a unique index.
? Use the DROP INDEX command to remove an index from the data dictionary: Drop index index;? Remove the EMP_LAST_NAME_IDX index from the data dictionary: Drop index em p_last_name_idx;? To delete an index, you must be the owner of the index or have a drop anyIndex permission. The index cannot be modified. To change the index,You must first delete it and recreate it. After you issue the DROP INDEX statement, you can delete the index definition from the data dictionary. To delete an index, it must be an indexowner or has the drop any INDEX permission. In this syntax, index is the name of the index. Note: If you delete a table,indexes and constraints are automatically deleted, but views and sequences are preserved.
Source: http://blog.csdn.net/rlhua/article/details/12780775
