Oracle Base Index

First, the index

Indexes are a quick way to access data to improve database performance. Indexing makes it possible for a database program to find the data it needs without having to scan the entire table, like a book's directory, to quickly find the information it needs, without having to read the entire book.

　　(i) Classification of indexes

Logical classification: Single-column index, combined index, unique index, non-unique index, function index.

Physical classification: Index, non-partitioned index, B-tree index, forward index, reverse index, bitmap index.

　　

　　(ii) Disadvantages of the index:

1, the index occupies the table space, creating too many indexes may cause index redundancy.

2. Indexes can affect the performance of INSERT, UPDATE, DELETE statements.

　　

　　(iii) Principles for the use of indexes:

1. Load the data and then build the index.

2. columns that are searched frequently can be indexed.

3. Create an index (primary foreign key) on the join attribute.

4. Sort the grouped columns frequently.

5. Delete indexes that are not used frequently.

6, specify the parameters of the index block, if in the future will perform a large number of insert operations on the table, set a larger ptcfree when indexing.

7. Specify the table space where the index resides, and placing tables and indexes on different table spaces can improve performance.

8. For large indexes, the exam uses the nologging clause to create a large index.

Do not create an index in the following column:

1, contains only a few different worthy columns.

2. The table contains only a few records.

　　　　

Second, the use of the index:

　　(i) Create an index

Grammar:

CREATE [UNIQUE] INDEX index_name on table_name (column_list)

[Tablespace Tablespace_name]

Description

Unique: Specifies that the values in the indexed column must be unique
Index_name: Index Name
TABLE_NAME: Specifies the table to be indexed
Col_name: The column to be indexed, which can be multiple columns, which is called a multi-column index
Table_space_name: Table space for index storage

Example 1:

if we routinely follow empno to query an employee's information on an EMP table, SQL> Select *  fromEmpwhereEmpno=7788; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7788SCOTT ANALYST7566 1987-4- +     3000.00                -we should build an index on the empno column. Create IndexIndx_on_empno onEMP (empno) tablespace users;

Example 2:

If we frequently query employee information for a department with a salary greater than 1000, then we can build on the job and Sal columns, so this is called a composite index: SQL> Select *  fromEMP2  whereJob='salesman'  andSal> +; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7499ALLEN salesman7698 1981-2- -     1600.00    300.00      - 7521WARD salesman7698 1981-2- A     1250.00    500.00      - 7654MARTIN salesman7698 1981-9- -     1250.00   1400.00      - 7844TURNER salesman7698 1981-9-8      1500.00      0.00      -Create IndexIndx_on_job_sal onEMP (job,sal) tablespace users;

Example 3:

We can also create function-based indexes, but before we do, we have to set the initialization parameters: Conn System /  as sysdba; Alter set query_rewrite_enabled=true; Create Index Indx_lower_job  on EMP (lower(Job)) tablespace users;

　(ii) Modification of the index

just like modifying a table, we can modify it after creating an index 1: allocating and releasing index spaceAlter Indexindx_on_empnoallocate Extent (size 1m); Instance 2: Free the extra index spaceAlter IndexIndx_on_empnodeallocateUnused; Example 3: Rebuilding an indexAlter Indexindx_on_job_sal Rebuild; Example 4: Rebuilding an index online, using rebuild, if another user is performing DML operations on a table, the rebuild fails, and the following statement can be used to successfully rebuildAlter Indexindx_on_job_sal rebuild Online; Instance 5: Merge index when there is space remaining in the adjacent index block, it is merged into one index block by merging the indexAlter IndexIndx_on_job_salCOALESCE; example 6: Renaming an indexAlter IndexIndx_on_job_sal Rename toIndx_on_jobandsal;

　　(iii) Deleting an index

if the index is no longer needed, leaving it in the database will consume resources and we can delete it Drop index indx_on_job_sal;

　　(iv) View index

we can see the index information about a table from the unser_indexes view, let me take a look at the unser_indexes View information: SQL > desc user_indexes;

Oracle Base Index