Oracle index Introduction

In relational databases, an index is a table-related database structure that enables faster execution of SQL statements corresponding to tables. The index function is equivalent to the book directory. You can quickly find the desired content based on the page number in the directory. For databases, indexing is a required option, but for various large databases, indexing can greatly improve the performance of the database, so that it becomes an indispensable part of the database. Index classification: the logical classification of a single column or concatenated column or multiple columns is cited as www.2cto. the unique and non-unique reference of comunique or nonunique, that is, whether the key value (key) of a column or several columns is unique. Function-based is based on some Function indexes. When you execute some functions, You need to compute them. You can save and index the computing results of some functions in advance to improve efficiency. Doman indexes data outside of the database, using a relatively small number of Physical Classes B-Tree: normal or reverse key B-Tree indexes are also common indexes we understand, it can be divided into normal and reverse indexes. Bitmap: This is introduced by Bitmap. Next we will detail the B-Tree index. B-Tree index is also a common index. B-tree (balance tree) is the balance tree, and the left and right branches are relatively balanced. B-Tree indexRoot is the root node, branch is the branch node, and leaf to the bottom layer is called the leaf node. Each node represents a layer. When you look for a certain data, read the root node first, then read the supporting node, and finally find the leaf node. The leaf node stores the index entry, which corresponds to a record. An integral part of Index entry: Indexentry entryheader stores some control information. Key column length the length of a key Key column value the value of a key ROWID pointer, specifically pointing to a data creation index: www.2cto.com User Logon:
SQL> conn as1/as1
Connected. Create a table:
SQL> create table dex (id int, sex char (1), name char (10 ));
Table created. Insert 1000 data records to the Table
SQL> begin
2 for I in 1 .. 1000
3 loop
4 insert into dex values (I, 'M', 'chongshi ');
5 end loop;
6 commit;
7 end;
8/PL/SQL procedure successfully completed.
View table records
SQL> select * from dex;
ID SE NAME
--------------------------------
... 991 M chongshi
992 M chongshi
993 M chongshi
994 M chongshi
995 M chongshi
996 M chongshi
997 M chongshi
998 M chongshi
999 M chongshi
1000 M chongshi1000 rows selected. Create an index:
SQL> create index dex_idx1 on dex (id );
Index created.
Note: Create an index for the first column (id) of the table. View created tables and Indexes
SQL> select object_name, object_type from user_objects; OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
An index is separated from a table and exists as a single individual. In addition to creating an index based on a single field, you can also create an index based on multiple columns. Oracle requires that you create an index for up to 32 columns. SQL> create index dex_index2 on dex (sex, name );
Index created. SQL> select object_name, object_type from user_objects; OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
DEX TABLE
DEX_IDX1 INDEX
DEX_INDEX2 INDEXwww.2cto.com you need to understand here: write a book and set the directory only after the Chapter Page is set. The same is true for database indexes. You only need to insert data first before creating an index. Then we will insert and delete the database content, and the indexes will also need to change. However, oracle automatically modifies the index. The figure above gives a clearer description of the index structure. Records 0 to 50 data records at the same node, and records 0 to 10 for splitting on the branch node ....... between 42 and 50, the leaf node records the length and value of each data segment and points the pointer to specific data. The leaf segments on the last layer are two-way links. They are ordered links to quickly lock a data range. For example, SQL> select * from dex where id> 23 and id <32; ID SE NAME
--------------------------------
24 M chongshi
25 M chongshi
26 M chongshi
27 M chongshi
28 M chongshi
29 M chongshi
30 M chongshi
31 M chongshi8 rows selected. for example, if you search for a column, you can first find 23rd pieces of data and then 32nd pieces of data by indexing, so that you can quickly lock a search range, if each piece of data needs to be searched from the root node, the efficiency will be very low. Bitmap index the bitmap index is mainly created for a large number of Columns with the same value. Take the country resident login table as an example. Assume that there are four fields: name, gender, age, and ID card number. The age and gender fields generate many identical values, gender only has two values: Age (1 to 120) and age (assuming the maximum age is 120 years. Therefore, no matter whether a table has hundreds of millions of records, there are only two values (male and female), which are differentiated by gender fields ). The bitmap index is an index created based on this feature of the field. Www.2cto. from the comBitmap Index, we can see that a leaf node (identified by different colors) represents a key, start rowid and end rowid specify the search range of this type, A leaf node is marked with a unique bitmap value. Because a value type corresponds to a node, during row query, bitmap indexes directly perform bitwise operations (and or) based on different bitmap values ), to obtain the result set vector (the calculated result ). Example: assume that the data table T has two data columns A and B. The values are as follows. We can see that columns A and B have the same data. Create Bitmap indexes for two data columns A and B: idx_t_bita and idx_t_bitb. The storage logic structure of the two indexes is as follows: Idx_t_bita index structure, which corresponds to the leaf node: Idx_t_bitb index structure, which corresponds to the leaf node: analysis on the query "select * from t where B = 1 and (a = 'L' or a = 'M')": bitmap index usage, it is very different from B * indexes. The use of B * indexes usually starts from the root node and is compared to the nearest qualified leaf node through continuous branch nodes. Scan the result set rowid through continuous Scan on the leaf node. Bitmap indexes work in different ways. Bitwise operations (and or) with different bitmap values can be used to obtain the result set vector (the calculated result ). For instance SQL, you can split it into the following operations: 1. a = 'L' or a = 'M' a = L: vector: 1010a = M: vector: the result of the 0001or operation is the operation of two vectors. The result is 1011. 2. In combination with the vector of B = 1, the intermediate result vector is: 1011B = 1: The result of the vector: 1001and operation, 1001. The first and fourth rows are the query results. 3. Get the result rowid. At present, we know the start rowid and end rowid, as well as the first row and fourth row operation results. You can obtain the rowid of the result set through the trial calculation method. Features of Bitmap indexes: 1. bitmap index storage space saved 2. bitmap indexes are created at a high speed. 3. bitmap index allows null key value 4. bitmap indexes create Bitmap indexes for efficient access to table records:
View table records
SQL> select * from dex;
...................
Www.2cto.com ID SEX NAME
--------------------------------
991 M chongshi
992 M chongshi
993G chongshi
994G chongshi
995G chongshi
996 M chongshi
997G chongshi
998G chongshi
999G chongshi
1000 M chongshi1000 rows selected. For the above table, sex (gender) has only two values, which are most suitable for creating bitmap
Create an index:
SQL> create bitmap index my_bit_idx on dex (sex); Index created. view the created
SQL> select object_name, object_type from user_objects; OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------
MY_BIT_IDX INDEX
Rules for creating indexes: www.2cto. com1. Weigh the relationship between the number of indexes and DML. DML is also used to insert or delete data. Here we need to weigh a problem. The purpose of index creation is to improve the query efficiency. However, too many indexes will affect the speed of data insertion and deletion, because the table data we modified, the index also needs to be modified. Here we need to weigh whether our operation is to query more or modify more. 2. Place the index and the corresponding table in different tablespaces. When reading a table, the table and the index are both performed. If the table and the index are in the same tablespace, resource competition will occur. If the table and the index are in the same tablespace, they can be executed in parallel. 3. It is best to use blocks of the same size. Oracle has five I/O reads by default. If you define six or ten I/O reads, you must read the I/O twice. Preferably an integer multiple of 5 to improve efficiency. 4. If a table is large, it takes a long time to create an index. Because index creation also produces a large amount of redo information, you can set no or no redo information during index creation. As long as the table data exists, the index fails and cannot be created again. Therefore, you do not need to generate redo information. Common index operations change indexes: SQL> alter index employees_last _ name_idx storage (next 400 K maxextents 100); after an index is created, it may feel unreasonable and its parameters can be modified. For more information, see the relevant documentation to adjust the index space: New Space
SQL> alter index orders_region_id_idx allocate extent (size 200 K datafile '/disk6/index01.dbf'); release space
SQL> alter index oraers_id_idx deallocate unused. The preceding two commands are used to complete the ADD and release operations. Oracle can automatically help with the addition of space. Manual addition can improve the performance if you know the database. Www.2cto.com re-create an index: the index is automatically created by oracle. When we perform frequent database operations, the index is also modified. When we delete a record in the database, the corresponding index does not only make a delete mark, but it still occupies space. The space of a block is released only when all the tags of a block are deleted. In this way, the index performance will decrease after a long time. At this time, you can create a new clean index to improve efficiency. SQL> alter index orders_region_id_idx rebuild tablespace index02; the preceding command can be used to create an index. The process of re-Indexing in oracle is as follows: 1. Table locking, no one else can perform any operation on the table after the table is locked. 2. Create a New (clean) Temporary index. 3. Delete the old index 4. Rename the new index as the old index 5. Unlock the table. Moving: in fact, we also use the above command to move indexes to other tablespaces and specify different tablespaces when specifying tablespaces. When a new index is created in another location, the old index is moved. SQL> alter index orders_region_id_idx rebuild tablespace index03; create an index online: as described above, when creating an index, the table is locked and cannot be used. It takes a long time to re-create an index for a large table. In order to meet your needs for table operations, this online re-create index is generated. SQL> alter index orders_id_idx rebuild online; creation process: 1. Lock Table 2. create temporary and empty indexes and IOT tables to store on-going DML. The key value stored in a common table, and the data in the table directly stored in the table referenced by IOT. on-gong DML is the addition, deletion, and modification operations performed by the user. 3. Unlock the table 4. Create a new index from the old index. 5. the IOT table stores the on-going DML information. The content of the IOT table is merged with the newly created index. 6. Lock Table 7. Update the content of the IOT table to the new index again to remove the old index. 8. Rename the new index as the old index name 9. Unlock the table www.2cto.com and integrate the index fragmentation. For example, there is space left in many indexes, you can use a single command to integrate the remaining space. SQL> alter index orders_id_idx coalesce; Delete index: SQL> drop index hr. departments_name_idx; analyze the index check results. As described earlier, the index takes a long time to produce a large amount of fragmentation, garbage information, and waste of space. You can re-create an index to improve the performance. You can use a command to complete the analysis index. The analysis results are stored in the index_stats table.
View the table that stores the analysis data:
SQL> select count (*) from index_stats; COUNT (*)
----------
0
Execute the analysis index command:
SQL> analyze index my_bit_idx validate structure; Index analyzed. Check that index_stats has a data record.
SQL> select count (*) from index_stats; COUNT (*)
----------
1. query the data:
SQL> select height, name, lf_rows, lf_blks, del_lf_rows from index_stats; HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS
-----------------------------------------------------------------------------------------------------
2 MY_BIT_IDX 1000 3 100 www.2cto.com analysis data analysis: (HEIGHT) the reference HEIGHT is 2, (NAME) The index NAME is MY_BIT_IDX, and (LF_ROWS) the reference table contains 1000 rows of data, (LF_BLKS) occupies 3 blocks, and (DEL_LF_ROWS) deletes 100 records. This also verifies the problem mentioned above. The 100 data records deleted are only marked as deleted, because the total number of data records is still 1000, occupying 3 blocks, therefore, if each block contains more than 333 records and the deleted data contains more than 333 records, the total number of data records is reduced when a block is cleared.