Shulou(Shulou.com)06/01 Report--

Oracle Index creation and Management

1. Brief introduction to Oracle index

In the Oracle database, each row of data stored is identified by a rowID. When a large amount of data is stored in the Oracle, it means that there is a large amount of rowID, so if you want to quickly locate the specified rowID, you need to use an index object.

When executing a query with specified conditions on the Oracle table, the conventional method is to take out all the records, then compare each record with the query conditions, and finally return the records that meet the conditions. This is not only time-consuming but also resource-consuming. When you have an index, you only need to find the value of the index field that meets the query criteria in the index, and you can quickly find the corresponding record in the table through the rowID saved in the index.

Users can create multiple types of indexes according to the situation. According to the storage mode of the index, the index is divided into B-tree index, bitmap index, reverse index and function-based index. Note the following when creating an index:

The index should be based on columns frequently referenced, sorted, and grouped by the where clause. If the selected column is not suitable, the query speed will not be improved.

L limit the number of indexes. Indexes only increase the query speed, but slow down the speed of DML operations.

L specifies the usage parameters of the index block space. When indexing based on a table, Oracle adds the corresponding table to the index block. When you add data to an index, Oracle reserves some space on the index block according to the pctfree parameter. If you perform a large number of insert operations on the table in the future, you should set a larger pctfree when indexing

Deploying tables and indexes to the same tablespace simplifies the management of tablespaces; deploying tables and indexes to different tablespaces can improve access performance (Oracle can read data from different hard drives in parallel)

two。 Create an index

When creating the index, Oracle first sorts the fields that will be indexed by the resume, and then stores the sorted field value and the rowID of the corresponding record in the index segment. When querying, query the rowID of the specified condition according to the index, and then extract the data rows according to the rowID.

1) B-tree index

B-tree index is the most commonly used index type (also the default type) in Oracle, which organizes and stores index data in a B-tree structure. By default, the data in the B-tree index is arranged in ascending order. The B-tree index is composed of root block, branch block and leaf block.

Example: create an index index _ test on the deptno column of the emp table

Create index index _ test on emp (deptno)

Pctfree 25

Tablespace users

Where the clause pctfree specifies the free space reserved for future insert operations, and the clause tablespace specifies the tablespace in which the index segment is located

2) Bitmap index

When the need to create an index column contains too few values, such as creating an index on a gender column, the value can only be "male" or "female". There are still too many values extracted by using the B-tree index, which loses the meaning of the index. In this case, a digit index is required.

Example:

Create bitmap index index _ test

On emp (salary)

Tablespace users

Note: the initialization parameter create _ bitmap _ area _ size is used to specify the size of the bitmap area allocated when establishing the bitmap index. The default value is 8 MB. The larger this parameter, the faster the bitmap index is established. Modify the parameter statement to:

Alter system set create _ bitmap _ area _ size = 8388608

Scope = spfile

After modification, you need to restart the database before it can take effect.

3) reverse key index

When using B-tree index on monotonously increasing columns, if the user deletes the data in the table, it will lead to a large number of leaf nodes on one side. Oracle provides another indexing mechanism, and reverse key indexing, which can randomly distribute the added data into the index. The reverse key index is a special B-tree index, and it is very useful to build an index on a sequentially increasing sequence. Reverse key indexes are similar to B-tree indexes in principle and storage structure. When the user inserts the record, the column values are indexed in reverse, and the data is no longer incremental, so the new data is usually distributed more evenly over the range of values than the original ordered tree.

Example:

Create index index _ test

On emp (deptno) reverse

Tablespace users

If a B-tree index has been established on the column, you can change it to a reverse key index:

Alter index index _ test

Rebulid reverse;'

4) function-based indexing

The most common problem that users encounter when using a database is case-sensitive characters. For example, there is a record of MANAGER in the Job field in the emp table. When the user searches in lowercase, the record cannot be found. Instead, the record can only be converted through the function upper and checked using the converted data. But this query is that even if the job column has a normal index, Oracle performs a full table search and evaluates the upper function for each row encountered. In this case, you can use a function-based index, which is usually just a regular B-tree index, but the data it stores is obtained by applying the function to the data in the table, rather than directly storing the data in the table itself.

If you are accustomed to using lowercase strings, you can create the following index:

Create index index _ test

On emp (lower (job))

3. Modify the index

Modifying the index is done using alter index.

After indexing the table, with the continuous replacement, insertion and deletion of the table, more and more storage fragments will be generated in the index China, which will reduce the efficiency of the index. It is possible to rebuild the index and merge the index to remove fragmentation. The merged index only merges the storage fragments of the leaf nodes in the B-tree together, and does not change the logistics organization structure of the index.

Merge Index:

Alter index index _ test

Coalesce deallocate unused

Re-index:

Alter index index _ test rebuild

Tablespace user 1

You can change the type of index, store tablespaces, etc., when rebuilding the index.

4. Delete index

Delete the index using the drop index statement. When the index is too fragmented or is not often used, you can delete the index:

Drop index index _ test

Note: deleting a table will also delete its corresponding index.

5. Display index information

In order to display the information of the index, Oracle provides a series of data dictionary views to enable users to understand all aspects of the index.

1) Show all indexes of the table: show all indexes of emp

Select * from dba _ indexs where owner = 'EMP'

2) Show index columns: display the index columns used by index _ test:

Select * from user _ ind _ columns where index _ name = 'INDEX _ TEST'

3) display the location and size of the index

Select * from user _ segments where segment _ name = 'INDEX _ TEST'

4) display function index

Select * from user _ ind _ expressions where index _ name = 'INDEX _ TEST'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.