Compare the index creation statements on SQL Server, Oracle, and DB2!

An index is a database object used to accelerate database queries.

The principle is to reduce the IO operations of the query to accelerate.

Index types:

• Clustered index: sort and store the data rows according to the key values of the data rows in the table or view. • Non-clustered index: it has a structure independent of data rows. • unique index: Make sure that the index key does not contain duplicate values.

 

Indexes created on SQL SERVER: • Create (nonclustered) index ind_emp on EMP (empno); by default, non-clustered indexes are created. • Exec sp_helpindex EMP; used to view the created index, which can be used by the query itself. • Create index ind_emp1 on EMP (empno, ename); you can create a composite index on multiple columns.

 

• Unique non-clustered index: • create unique index ind_sal on EMP (SAL); • re-create index: • alter index ind_sal on EMP rebuild;

•Drop index ind_emp on EMP; Delete the index.

• Create clustered index ind_emp on EMP (empno); Create 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); Create an index on DB2. • Non-unique index: Create index ind_empno on EMP (empno); • Describe indexes for table EMP; view the created index. • Unique index: create unique index ind_empno on EMP (empno); • pure index is a special index on DB2 (equivalent to the index organization table on Oracle ): compared with General indexes. The following table contains two fields. Field 1 is the unique primary key and field 2 is the data. In actual queries, select empno, ename from EMP where empno = 1122;

Create unique index idx_ename on EMP (empno) include (ename ). The preceding statement creates a unique index on empno and selects the data containing the ename. The additional data will be stored together 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; -- A table can have only one clustered index. • Create unique index ind_sal on u_emp (SAL) cluster; create a clustered index. Create 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 ); bitmap index • SQL> Create index ind5 on EMP upper (job); function indexes will be available later on the cluster and IOT (index organization table) in Oracle!