Oracle Index Summary

Never give up, everything is possible!!!

Only to find a way to succeed, not to find excuses for failure!

Introduction to Oracle Index summary

1. Description

1) An index is one of the database objects used to speed up the retrieval of data, similar to the index of a book. Indexing in a database can reduce the amount of data that needs to be read when the database program queries results, similar to the fact that in a book we use the index to find the information you want without flipping through the entire book.

2) The index is an optional object on the table, and the key to the index is to improve the retrieval efficiency by replacing the default full-table scan retrieval method with a sorted set of index keys.

3) The index is logically and physically independent of the related tables and data, and does not affect the underlying table when creating or deleting an index;

4) Once the index is established, when DML operations are performed on the table (for example, when inserting, modifying, or deleting related operations), Oracle automatically manages the indexes, indexes are deleted, and the tables do not have an impact

5) The index is transparent to the user, regardless of whether there is an index on the table, the usage of the SQL statement is unchanged

6) When Oracle creates a primary key, it automatically creates an index on that column

Indexing principle

1. If there is no index, search for a record (for example, find name= ' wish ') need to search all records, because there is no guarantee that only one wish, must be searched all over

2. If an index is established on name, Oracle will search the whole table one at a time, the name value of each record in ascending order, and then build the index entry (name and ROWID), stored in the index segment, the query name is wish can directly find the corresponding place

3. The index is created and is not necessarily used, after the Oracle automatic statistics information, decide whether to use the index, the table data is very small when using full table scan speed is fast, there is no need to use the index

Index use (Create, modify, delete, view)

1. CREATE INDEX syntax

CREATE[UNIQUE]|[BITMAP]INDEX index_name--Unique represents a single indexOn table_name ([Column1 [asc| DESC],column2--Bitmap, creating a bitmap index[asc| DESC],...]|[Express])[Tablespace Tablespace_name][pctfree N1] -- Specify the index in the data block free space [< Span style= "color: #ff0000;" >storage (INITIAL n2) ][ nologging-- indicates that a DML operation is allowed on the table when the index is created and rebuilt, and should not be used by default [noline][nosort--      

2. Modifying the index

1) Renaming the index

to Bitmap_index;

2) Merge index (the table will be fragmented in the index after a period of time, the index efficiency will be reduced, you can choose to rebuild the index or merge the index, the merge index is better, no additional storage space, the cost is lower)

Coalesce;

3) Rebuilding the index

Method One: Delete the original index and re-establish the index

Way two:

Index INDEX_SNO rebuild;

3. Deleting an index

Index Index_sno;

4. View Index

Select Index_name,Index-type, Tablespace_name, uniquenessFrom All_indexeswhere table_name =tablename ' -- eg: create index index_sno on student (name ); select * from all_indexes where table_name=student           

Index Classification

1. B-Tree index (default index, save the sorted index column and the corresponding ROWID value)

1) Description:

The most commonly used index in 1.oracle; the B-Tree index is a binary tree; leaf nodes (doubly linked list) contain index columns and ROWID values that point to each matching row in the table

2. All leaf nodes have the same depth, so the query speed is basically the same regardless of the query condition

3. Able to adapt to accurate query, fuzzy query and comparison query

2) Classification:

Unique,non-unique (default), REVERSE KEY (the data in the data column is stored in reverse)

3) Create an example

On student ('sno');  

4) Suitable for the use of the scene:

Column cardinality (number of columns with distinct values) suitable for use with B-Number indexes when large

　　

2. Bitmap indexing

1) Description:

　1. When creating a bitmap index, Oracle scans the entire table and establishes a bitmap for each value of the indexed column (a bitmap that uses one bit (bit,0 or 1) for each row in the table to identify whether the row contains the value of the indexed column for that bitmap, or 1, Represents the record containing the bitmap index column value for the corresponding ROWID, and finally the conversion of bit-to-row rowid through the mapping function in the bitmap index

2) Create an example

On student (SNO);

3) Suitable for the scene:

For columns with small cardinality suitable for CV bitmap indexing (e.g. gender)

3. Single-column and composite Indexes (created from multiple columns)

1) Note:

That is, if the index is built on more than one column, the optimizer uses that index only if its first column is referenced by the WHERE clause, that is, the first column to contain at least the combined index

4. Function index

1) Description:

1. When you frequently want to access some functions or expressions, you can store them in the index, so that the next time you access, the value has been calculated, you can speed up the query

2. The function index can use either a B-number index or a bitmap index, or a B-tree index when the function result is indeterminate, and the result is a fixed number of values when using a bitmap index

3. The function index can be cement with Len, trim, substr, upper (each line returns independent results), cannot use such as SUM, Max, MIN, AVG, etc.

2) Example:

Index FBI on  student (Upper(name));  ='WISH';      

Summary of Index establishment principles

1. If there are two or more indexes, one of which has a unique index, and the others are unique, in which case Oracle will completely ignore the non-uniqueness index using a unique index

2. To include at least the first column of the combined index (that is, if the index is based on more than one column, only the first column of it is referenced by the WHERE clause, the optimizer uses that index)

3. The small table does not have the resume index

4. For columns with large cardinality, suitable for a B-tree index, for a column with a small cardinality for the resume bitmap index

5. There are a lot of null values in the column, but you should index when querying non-empty records on that column frequently

6. Columns that frequently make connection queries should create indexes

7. Use the CREATE index to put the most frequently queried columns at the front

8. Long (variable-length string data, maximum 2G) and long RAW (variable-length binary data, maximum 2G) columns cannot be indexed

9. Limit the number of indexes in the table (creating indexes takes time and increases with the amount of data; indexes take up physical space; When data in a table is added, deleted, and modified, the index is maintained dynamically, reducing the data maintenance speed)

Precautions

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

--We create an index on name;CreateIndex Index_nameOn student (‘Name‘); --* from student  Where name like  %wish%  ' ; --select * from student where name like  wish%                

2. Do not use not on the index column, you can substitute the following in other ways: (Oracle encounters not will stop using the index, and full table scan)

Select*From studentwhereNot (score=100select * from student Span style= "color: #0000ff;" >where score 100; -- Replace with select * from student where score >100 or score <100                 

3. Using a null comparison on the index will stop using the index, eg:

Null

Oracle Index Summary