Oracle basics-index and oracle basic index

Oracle basics-index and oracle basic index

1. What is indexing?

A: indexes are used in databases to improve query efficiency.

Supplement: too many indexes can greatly reduce the efficiency of DML statements. The indexing principle is to create fields that are frequently used as query conditions.

Index, which is not suitable for creating indexes on fields with a large number of duplicate information.

Note: (the index is invalid in the following cases)

1) if you use <> compare, the index is invalid. We recommend that you use <or>

2) The prefix fuzzy match % is invalid and replaced by instr.

3) when using a function (for example, create index ind... On Table Name (to_char (birth, '') to create such an index usage Function

The query statement is case sensitive)

4) use a non-matching data type

5) if the field contains NULL indexes, the index creation field should be not null.

Syntax:

-- Create an index:

Create index INDEX_ENAME on emp (ENAME)

 

-- If you can perform DML operations on the table during index creation, add online to the command line.

Create index INDEX_SNAME ON DB_STUDENT (SNAME) [ONLINE]

 

-- Joint indexes can be created for fields frequently used as joint queries:

Create index INDEX_EMP_SAL_JOB on emp (SAL, JOB)

 

-- Delete an index:

Drop index INDEX_HIREDATE

 

2. Clustered index:

Clustering is a collection of checks placed in the same place. Clustering indexes are generally built on associated tables to improve query efficiency based on associated fields.

Steps:

1) create a cluster first

2) Submit the joined fields to the cluster for management while creating the table.

3) create an index on the Cluster

Syntax:

-- Create a cluster
Create cluster cluster_deptno (deptno number (3 ));

 

-- Place the specified field in the cluster when creating the table
Create table dept
(
Deptno number (3 ),
Dname varchar2 (32)
)
Cluster cluster_deptno (deptno );

Alter table dept add constraint pk_dept_deptno primary key (deptno );

Create table emp
(
Empno number (5) primary key,
Ename varchar2 (32 ),
Age number (3 ),
Sal number (5 ),
Deptno number (3) references dept (deptno)
)

 

-- Foreign keys must be created before Clustering
Cluster cluster_deptno (deptno)

 

-- Create an index on a cluster (that is, create a Cluster Index)
Create index cindex_deptno on cluster cluster_deptno;

 

-- Delete a cluster

Drop cluster cluster_deptno

3. Reverse indexing: usually based on fields that generate field information from sequences.
Create index INDEX_STU_SNO on DB_STUDENT (sno) reverse;

To be continued ~