Comparison of SQL SERVER,ORACLE,DB2 index creation statements

Source to: http://jvortex.blog.163.com/blog/static/16961890020122141010878/

We know that indexes are database objects that are used to speed up database queries. The principle is to reduce the IO operation of the query, so as to achieve the purpose of acceleration. In this paper, we mainly summarize the index statement on SQL SERVER,ORACLE,DB2, and then let's take a look at this part.

Types of indexes:

Clustered index: These data rows are sorted and stored in a table or view based on the key values of the data rows.

Nonclustered Indexes : Have a structure that is independent of the data rows.

Unique index : Ensure that the index key does not contain duplicate values.

indexes established on SQL Server:

Create (nonclustered) index ind_emp on EMP (EMPNO); The default is to create a nonclustered index.

EXEC sp_helpindex EMP; Used to view the index that is established, the query uses the index that it can use.

Create index IND_EMP1 on EMP (empno,ename); You can create composite indexes on multiple columns.

Unique Nonclustered indexes:

Create Unique index ind_sal on EMP (SAL);

To rebuild the index:

Alter index ind_sal on EMP rebuild;

Drop index ind_emp on EMP; Deletes an index.

Create CLUSTERED index ind_emp on EMP (EMPNO); Build a clustered index.

Create index ind_emp on EMP (empno,ename);

Note: The same column can be indexed multiple times.

Unique Clustered index:

Create Unique clustered index ind_sal on f_emp (SAL);

To build an index on DB2:

Non-unique index: CREATE index ind_empno on EMP (EMPNO);

Describe indexes for table emp; View the index that was created.

Unique index: Create unique index ind_empno on EMP (EMPNO);

A pure index is a special index on DB2 (equivalent to an indexed organization table on Oracle): relative to a generic index. There are two fields in the table below, where field 1 is the unique primary key, field 2 is data, and the actual query is often the select Empno,ename from emp where empno=1122; CREATE UNIQUE INDEX idx_ename on EMP (empno) INCLUDE (ename). The meaning of the above statement is to create a unique index on EMPNO, select the data containing the ename, and the additional data will be stored with the key.

Drop index ind_emp;

Create index ind_emp on EMP (empno) cluster;

Create index ind_emp on EMP (empno,ename);

Unique Clustered index:

Drop Index ind_emp;--There can be only one clustered index on a table.

Create Unique index ind_sal on u_emp (SAL) cluster; Build a clustered index.

To build an index on Oracle:

Sql> CREATE INDEX IND1 on EMP (MGR); Btree index.

Sql> CREATE INDEX Ind2 on EMP (DEPTNO) reverse; Reverse index.

Sql> CREATE INDEX Ind3 on EMP (hiredate desc); Descending index.

Sql> Create bitmap index ind4 on emp (SAL); The bitmap index.

Sql> CREATE INDEX IND5 on EMP Upper (Job); The function index.

On the SQL SERVER,ORACLE,DB2 index statement on the summary of the introduction here, I hope this introduction can be a harvest for you!

Comparison of SQL SERVER,ORACLE,DB2 index creation statements