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

Catalogue

I. introduction to the index

1. An index is an independent database object, just like the data table table. In Oracle, the database object object is represented by a segment segment structure. In the data dictionary dba_segment, we can use the name of the index to search for dictionary items equivalent to segment_name.

2. Index is one of the database objects, which is used to speed up data retrieval, similar to the index of books. Indexing in the database can reduce the amount of data that the database program needs to read when querying the results, just like in books, we can use the index to find the information we want without flipping through the whole book.

3. The index is an optional object built on the table, and the key of the index is to replace the default full table scan retrieval method with a set of sorted index keys, so as to improve the retrieval efficiency.

4. The index is logically and physically independent of the relevant tables and data. When an index is created or deleted, it will not affect the basic table.

5. Once the index is established, when performing DML operations on the table (such as inserting, modifying or deleting related operations), oracle will automatically manage the index and delete the index, which will not affect the table

6. The index is transparent to users, and the usage of the sql statement remains the same regardless of whether there is an index on the table or not.

7. The Oracle database automatically creates indexes for the primary key of the table and columns that contain unique constraints.

8. Data tables and indexes can be stored separately. In general, from a performance point of view, we often place the two in different Tablespace, mainly to disperse the physical IO.

Second, the principle of index

1. If there is no index, when searching for a record (such as looking for name='wjq'), you need to search all records, because there is no guarantee that there is only one wjq, you must search all of them.

2. If you build an index on name, oracle will search the whole table once, arrange the name values of each record in ascending order, and then build index entries (name and rowid) and store them in the index segment. If the query name is wjq, you can directly find the corresponding place.

3. After creating an index, it is not necessary to use it. After oracle automatically counts the information of the table, decide whether to use the index or not. When there is little data in the table, the speed of using full table scan is already very fast, so there is no need to use the index.

III. Classification of indexes

3.1 logical classification

Single column or concatenated

Index one or more columns

Unique or nonunique

Unique and non-unique indexes, that is, whether the key values (key) of a column or columns are unique

Function-based

Based on the index of functions, some functions need to be calculated when they are executed. The calculation results of some functions can be saved and indexed in advance to improve efficiency.

Doman

Data outside the index database, using relatively little

3.2 physical classification

B-Tree

B-Tree index is also a traditional common understanding of the index, it can be divided into normal index and reverse key index (the data in the data column is stored in reverse).

Bitmap

Bitmap index

The following focuses on B-Tree indexes, Bitmap indexes, and functional indexes.

1. B-Tree index

A, B-Tree index is the most commonly used index in Oracle; B-tree index is a binary tree (balance tree), the left and right branches are relatively balanced; leaf node (two-way linked list) contains index columns and ROWID values pointing to each matching row in the table.

B. all leaf nodes have the same depth, so the query speed is basically the same regardless of the query conditions.

C, be able to adapt to precise query, fuzzy query and comparative query

Description:

Root is the root node, branch is the branch node, and leaf to the lowest layer is called the leaf node. Each node represents a layer. When looking for some data, read the root node first, then read the branch node, and finally find the leaf node. The leaf node stores the index entry (index entry), and each index entry corresponds to a record.

Components of Index entry:

Indexentry entry header stores some control information.

Key column length the length of a key

Key column value the value of a key

ROWID pointer, which points to a particular data

Create an index

Create a test table and insert 1000 rows of data

SEIANG@seiang11g > create table tb_test1 (id int,sex char (4), name varchar2 (30)) tablespace seiang

Table created.

SEIANG@seiang11g > begin

2 for i in 1..1000 loop

3 insert into tb_test1 values (iMagnetics, Manners, wjq' | | I)

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SEIANG@seiang11g >

SEIANG@seiang11g > select count (*) from tb_test1

COUNT (*)

-

one thousand

SEIANG@seiang11g >

SEIANG@seiang11g > select * from tb_test1 where rownumcreate index idx_test1_id on tb_test1 (id) tablespace wjq_index

Index created.

SEIANG@seiang11g > select object_name,object_type from user_objects where object_name in ('TB_TEST1','IDX_TEST1_ID')

OBJECT_NAME OBJECT_TYPE

IDX_TEST1_ID INDEX

TB_TEST1 TABLE

The index is separated from the table and exists as a separate 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 the maximum number of indexes created cannot exceed 32 columns.

SEIANG@seiang11g > create index idx_test1_sex_name on tb_test1 (sex,name) tablespace wjq_index

Index created.

SEIANG@seiang11g >

SEIANG@seiang11g > select object_name,object_type from user_objects where object_name in ('TB_TEST1','IDX_TEST1_ID','IDX_TEST1_SEX_NAME')

OBJECT_NAME OBJECT_TYPE

IDX_TEST1_ID INDEX

IDX_TEST1_SEX_NAME INDEX

TB_TEST1 TABLE

It needs to be understood here:

Write a book, only after the chapter page is set, and then set up the catalog; the same is true for the database index, only insert the data first, and then establish the index. Then we insert and delete the contents of the database later, and the index needs to be changed accordingly. However, the modification of the index is done automatically by oracle.

The above picture can describe the structure of the index more clearly.

The root node records the location of 0 to 50 pieces of data, and the branch node splits to record 0 to 10. From 42 to 50, the leaf node records the length and value of each data, and points to the specific data by the pointer. The leaf sections of the last layer are bi-directional links, which are linked in an orderly manner, so that a data range can be locked quickly.

For example:

SEIANG@seiang11g > select * from tb_test1 where id > 23 and id23 AND "ID" create bitmap index bitmap_idx_test1_sex on tb_test1 (sex) tablespace wjq_index

Index created.

SEIANG@seiang11g > select object_name,object_type from user_objects where object_name in ('TB_TEST1','BITMAP_IDX_TEST1_SEX')

OBJECT_NAME OBJECT_TYPE

BITMAP_IDX_TEST1_SEX INDEX

TB_TEST1 TABLE

SEIANG@seiang11g > select * from tb_test1 where sex='M'

1000 rows selected.

Execution Plan

Plan hash value: 2608569169

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1000 | 36000 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | TB_TEST1 | 1000 | 36000 | 2 (0) | 00:00:01 |

| | 2 | BITMAP CONVERSION TO ROWIDS | | |

| | * 3 | BITMAP INDEX SINGLE VALUE | BITMAP_IDX_TEST1_SEX |

-

Predicate Information (identified by operation id):

3-access ("SEX" ='M')

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

5 recursive calls

0 db block gets

82 consistent gets

0 physical reads

0 redo size

33757 bytes sent via SQL*Net to client

1249 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1000 rows processed

-- View all indexes and types created on the table tb_test1

SEIANG@seiang11g > select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS

2 from user_ind_columns a,user_indexes b

3 where a.INDEX_NAME=b.INDEX_NAME and a.TABLENAMEST1'

INDEX_NAME INDEX_TYPE TABLE_NAME COLUMN_NAME STATUS

IDX_TEST1_ID NORMAL TB_TEST1 ID VALID

IDX_TEST1_SEX_NAME NORMAL TB_TEST1 SEX VALID

IDX_TEST1_SEX_NAME NORMAL TB_TEST1 NAME VALID

BITMAP_IDX_TEST1_SEX BITMAP TB_TEST1 SEX VALID

3. Functional index

A. when you often want to access some functions or expressions, you can store them in the index so that the value has been calculated the next time you visit, which can speed up the query.

B. The function index can use either B-Tree index or bitmap index; B-tree index can be used when the function result is uncertain, and bitmap index can be used when the result is fixed.

C. Len, trim, substr and upper can be used in functional indexes (each row returns independent results), but not sum, max, min, avg, etc.

D, functional index one thing to pay special attention to, when using the functional index, the corresponding expression in the SQL statement must be exactly the same as the expression that creates the functional index (of course, spaces, keyword case can be ignored), if not completely consistent, then the functional index can not be used.

Create a functional index

Create a test table tb_test2 and insert the corresponding data at the same time

SEIANG@seiang11g > create table tb_test2 as select * from dba_objects where owner in ('SYS','BI','SCOTT','PUBLIC','SYSTEM')

Table created.

SEIANG@seiang11g >

SEIANG@seiang11g > select owner,count (*) from tb_test2 group by owner

OWNER COUNT (*)

PUBLIC 34002

SYSTEM 618

SCOTT 25

BI 8

SYS 37803

SEIANG@seiang11g >

SEIANG@seiang11g > select count (*) from tb_test2

COUNT (*)

-

72456

-- create a normal B-Tree index for owner columns

SEIANG@seiang11g > create index idx_test2_owner on tb_test2 (owner)

Index created.

-- use index columns to make a general query for the column value of BI. As expected, index scanning is used here.

SEIANG@seiang11g > select * from tb_test2 where owner='BI'

8 rows selected.

Execution Plan

Plan hash value: 1141247240

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 8 | 1656 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | TB_TEST2 | 8 | 1656 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | IDX_TEST2_OWNER | 8 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access ("OWNER" = 'BI')

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

23 recursive calls

0 db block gets

99 consistent gets

1 physical reads

0 redo size

2238 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8 rows processed

-- clear the buffer_cache buffer to avoid affecting the observation of physical reads in subsequent operations.

SEIANG@seiang11g > alter system flush buffer_cache

System altered.

Use the UPPER function for conditional filtering and observe the execution plan. Through the execution plan, it is clear that index scanning is not used, resulting in a large number of physical read operations.

SEIANG@seiang11g > select * from tb_test2 where upper (owner) = 'BI'

8 rows selected.

Execution Plan

Plan hash value: 2703936182

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 12 | 2484 | 290 (1) | 00:00:04 |

| | * 1 | TABLE ACCESS FULL | TB_TEST2 | 12 | 2484 | 290 (1) | 00:00:04 |

Predicate Information (identified by operation id):

1-filter (UPPER ("OWNER") = 'BI')

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

5 recursive calls

0 db block gets

1106 consistent gets

1039 physical reads

0 redo size

1854 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8 rows processed

As you can see from the above example, even if the conditional column is indexed, when the function is used on the index column for conditional matching, the execution plan will not select an index scan.

-- create a functional index on an index column

SEIANG@seiang11g > create index func_idx_test2_owner on tb_test2 (UPPER (owner))

Index created.

-- View and verify the established function index

It should be noted that because this index is based on functions, the columns column cannot display the real column names, which can be viewed in the user_ind_expressions view.

SEIANG@seiang11g > select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS

2 from user_ind_columns a,user_indexes b

3 where a.INDEX_NAME=b.INDEX_NAME and a. TABLENAMEST2'

INDEX_NAME INDEX_TYPE TABLE_NAME COLUMN_NAME STATUS

IDX_TEST2_OWNER NORMAL TB_TEST2 OWNER VALID

FUNC_IDX_TEST2_OWNER FUNCTION-BASED NORMAL TB_TEST2 SYS_NC00016 $VALID

SEIANG@seiang11g > select * from user_ind_expressions where INDEX_NAME='FUNC_IDX_TEST2_OWNER'

INDEX_NAME TABLE_NAME COLUMN_EXPRESSION COLUMN_POSITION

FUNC_IDX_TEST2_OWNER TB_TEST2 UPPER ("OWNER") 1

-- once again, the UPPER function is used for conditional query, and the execution plan uses index scanning, resulting in a significant reduction in physical reads.

SEIANG@seiang11g > alter system flush buffer_cache

System altered.

SEIANG@seiang11g > select * from tb_test2 where upper (owner) = 'BI'

8 rows selected.

Execution Plan

Plan hash value: 3617808431

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 8 | 1792 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | TB_TEST2 | 8 | 1792 | 2 (0) | 00:00:01 |

| | * 2 | INDEX RANGE SCAN | FUNC_IDX_TEST2_OWNER | 8 | | 1 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-access (UPPER ("OWNER") = 'BI')

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

10 recursive calls

0 db block gets

74 consistent gets

296 physical reads

0 redo size

1854 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

8 rows processed

As you can see from the above example, due to the establishment of the functional index, the index scan is reselected to the execution plan, and the physical read (physical reads) is significantly reduced.

IV. common operation of index 4.1 creating index

CREATE [UNIQUE] | [BITMAP] INDEX index_name-- unique represents a unique index

ON table_name ([column1 [ASC | DESC], column2-- bitmap) to create a bitmap index

[ASC | DESC],...] | [express])

[TABLESPACE tablespace_name]

[PCTFREE N1]-- specifies that the index is free in the block

[STORAGE (INITIAL N2)]

[NOLOGGING]-- indicates that DML operations on tables are allowed when creating and rebuilding indexes, and should not be used by default

[NOLINE]

[NOSORT];-- indicates that the index is not sorted when it is created. The default is not applicable. If the data is already arranged in the order of the index, you can use the

4.2 modify the index

(1) rename the index

Alter index [index_name] rename to bitmap_index

(2) change the index

Alter index [index_name] storage (next 400K maxextents 100)

After the index is created, it feels unreasonable, and its parameters can also be modified. View the relevant documents for details

(3) adjust the space of the index

-- newly added space

Alter index [index_name] allocate extent (size 200K datafile'/ disk6/index01.dbf')

-- Free space

Alter index [index_name] deallocate unused

In the process of using the index, there may be insufficient space or waste of space, which requires the addition or release of space. The above two commands complete the add and release operations. The new oracle on space can help automatically, and manually increasing it can improve performance if you know about the database.

(4) recreate the index

The index is automatically completed by oracle, and when we operate frequently on the database, the index will be modified accordingly. When we delete a record in the database, the corresponding index does not just make a delete mark in the corresponding index, but it still occupies space. The space of the entire block will not be released unless all the tags in a block are deleted. Over time, the performance of the index degrades. At this time, a clean index can be re-established to improve efficiency.

Alter index [index_name] rebuild tablespace [tablespace_name]

With the above command, you can recreate the process of creating an index and oracle re-indexing:

1. Lock the table. After locking the table, no one else can do anything on the table.

2. Create a new (clean) temporary index.

3. Delete the old index

Rename the new index to the name of the old index

5. Unlock the table.

(5) move the index

In fact, we also use the above command to move indexes to other tablespaces, specifying different tablespaces when specifying tablespaces. The new index is created in another location, and killing the old one is tantamount to moving.

Alter index [index_name] rebuild tablespace [tablespace_name]

(6) recreate the index online:

As mentioned above, when creating an index, the table is locked and cannot be used. For a large table, it takes a long time to recreate the index, which is generated by online re-creation of the index in order to meet the needs of users for table operations.

Alter index [index_name] rebuild online

Creation process:

1. Lock the watch

2. Create temporary and empty indexes and IOT tables to exist on-going DML. The key value stored in the ordinary table, the data in the table directly stored in the table cited by IOT; on-gong DML is the operation of adding, deleting and modifying done by the user.

3. Unlock the table

4. Create a new index from the old index.

5. On-going DML information is stored in the IOT table, and the contents of the IOT table are merged with the newly created index.

6. Lock the watch

7. Update the contents of the IOT table to the new index again and kill the old index.

Rename the new index to the name of the old index

9. Unlock the table

(7) merge indexes

The table will be fragmented in the index after it has been used for a period of time, and the index efficiency will be reduced. You can choose to rebuild the index or merge the index. It is better to merge the index without additional storage space.)

As shown in the figure above, there is remaining space in many indexes, and the remaining space can be integrated with a single command.

Alter index [index_name] coalesce

(8) View the index

Select a.INDEX_NAME INDEX_NAME,b.INDEX_TYPE INDEX_TYPE,a.TABLE_NAME TABLE_NAME,COLUMN_NAME,STATUS from user_ind_columns a minute username, where a.INDEX_NAME=b.INDEX_NAME and a. TABLENAMERS XXX'

-- View the detailed definition of the function index

Select * from user_ind_expressions where INDEX_NAME='FUNC_IDX_TEST2_OWNER'

(9) Analytical index

Check that the cited results are effective. As mentioned earlier, indexing over a long period of time will produce a lot of debris, spam and wasted remaining space. The cited performance can be improved by recreating the index.

The analysis index can be completed with a command, and the results of the analysis are stored in the index_ stats table.

SEIANG@seiang11g > select count (*) from index_stats

COUNT (*)

-

0

-- Delete 200 rows of data

SEIANG@seiang11g > delete tb_test1 where id > 800,

200 rows deleted.

-- Index analysis

SEIANG@seiang11g > analyze index IDX_TEST1_ID validate structure

Index analyzed.

SEIANG@seiang11g > select count (*) from index_stats

COUNT (*)

-

one

SEIANG@seiang11g >

SEIANG@seiang11g > select height,name,lf_rows,lf_blks,del_lf_rows from index_stats

HEIGHT NAME LF_ROWS LF_BLKS DEL_LF_ROWS

2 IDX_TEST1_ID 1000 3 200

Description:

(HEIGHT) the cited height is 2, (NAME) the index is named IDX_TEST1_ID, (LF_ROWS) the referenced table has 1000 rows of data, (LF_BLKS) occupies 3 blocks, and (DEL_LF_ROWS) deletes 1000 records.

The problem mentioned above is also verified here. The 200 deleted pieces of data are only marked for deletion, because the total number of data entries is still 1000, occupying 3 blocks, so each block is larger than 333. only when the deleted data is more than 333records, the total number of data entries will be reduced when a block is cleared.

(10) Monitoring index

Whether after production or in the development test, we have added some indexes to the data table. Usually we can not monitor the execution plan of each statement in real time, so in oracle, we can use the monitoring usage keyword and v$object_usage view to achieve this function, find some infrequent indexes and locate the optimization target.

-- enable monitoring and collect monitoring results.

SEIANG@seiang11g > alter index IDX_TEST1_ID monitoring usage

Index altered.

SEIANG@seiang11g > select * from v$object_usage where index_name='IDX_TEST1_ID'

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

-

IDX_TEST1_ID TB_TEST1 YES NO 11/09/2017 14:18:02

SEIANG@seiang11g > select * from tb_test1 where idalter index IDX_TEST1_ID nomonitoring usage

Index altered.

SEIANG@seiang11g > select * from v$object_usage where index_name='IDX_TEST1_ID'

INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING

-

IDX_TEST1_ID TB_TEST1 NO YES 11/09/2017 14:18:02 11/09/2017 14:21:34

(11) Delete the index

Drop index [index_name]

Extended supplement: commonly used oracle indexed views

The more important oracle indexed views are as follows:

Dba_indexes

User_indexes

Dba_ind_columns

User_indexes

Dba_expressions

User_expressions

Description:

Dba_indexes and user_indexes views mainly cover the parameters, status, and associated table information of the index, but do not contain specific column information.

Dba_ind_columns and user_ind_columns views, mainly related to the information of specific index columns.

Dba_expressions and user_expressions views, mainly for function indexes, can view specific function information.

V. Summary of the principles of index establishment

1. Weigh the relationship between the number of indexes and DML, DML is the operation of inserting and deleting data. Here we need to weigh a problem, the purpose of indexing is to improve query efficiency, but too many indexes will affect the speed of inserting and deleting data, because we modify the table data, the index will also be modified. Here we need to weigh whether we have more queries or more modifications.

2. If there are two or more indexes, one of which is unique and the other is non-unique, oracle will use the unique index and completely ignore the non-unique index

3. Put the index and the corresponding table in a different table space. When reading a table, the table and the index occur at the same time. If the table competes with the index and in one tablespace, it can be executed in parallel when left empty between the two tables. The main purpose of this is to disperse the physical IO.

4. It is best to use the same size as a block. Oracle defaults to five blocks, which is read once. If you define 6 or 10 blocks, you need to read them twice. It is better to be an integer multiple of 5 to improve efficiency.

5. If a table is very large, it takes a long time to build an index, because indexing will also generate a lot of redo information, so you can set up no or less redo information when creating the index. As long as the table data exists, the index can be rebuilt if it fails, so there is no need to generate redo information.

6. When building the index, you should create it according to the specific business SQL, especially the where condition and the order of the where condition, and try to put the filtering in a wide range behind, because the SQL execution is from back to front. (Xiao Li carries a kitchen knife)

7. Include at least the first column of the composite index (that is, if the index is built on multiple columns, the optimizer will use the index only if its first column is referenced by the where clause)

8. No resume index for small table

9. B-tree index is suitable for columns with large cardinality and resume bitmap index for columns with small cardinality.

10. There are many null values in the column, but indexes should be established when querying non-empty records on the column.

11. Columns that frequently make join queries should create indexes

12. When using create index, put the most frequently queried columns first

13. Limit the number of indexes in the table (it takes time to create the index and increases with the increase of the amount of data; the index takes up physical space; when the data in the table is added, deleted and modified, the index is also maintained dynamically, reducing the speed of data maintenance)

VI. The situation in which the SQL statement does not walk the index

1. When a wildcard appears at the beginning of a search term, oracle cannot use the index

-- We create an index on name

Create index index_name on student ('name')

-- oracle does not apply to name indexes in the following ways

Select * from student where name like'% wjq%'

-- if the wildcard appears elsewhere in the string, the optimizer can take advantage of the index, as follows:

Select * from student where name like 'wjq%'

2. Do not use not on index columns. Other ways can be used instead: (oracle stops using indexes when it encounters not and uses full table scans)

Select * from student where not (score=100)

Select * from student where score 100

-- replace with

Select * from student where score > 100 or score

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.