Oracle Database - Indexes

An Index is a schema object

It contains an entry for each value that appears in the indexed columns of the table.
It provides direct, fast access to rows.
Oracle Databases supports several types of index.
When we create unique or primary key it also maintain as unique index In Oracle we can maintain unlimited number of index on a table.
In Oracle 12c we can maintain multiple index in single columns

Type of indexes

There are several types of index structures available to depending on the need.

Bitmap index and B-tree index
Partitioned Index and not-Partitioned
index Locally Partitioned Index and Globally Partitioned Index.
Unique index and non-unique index.
Composite index and non-composite index.

Index Basically two types depend upon the structure. They are

B-tree Index
Bitmap Index

B-tree Index

These indexes are the standard index type(default).
They are excellent for primary key and highly selective indexes.
Use as concatenated indexes, b-tree indexes can retrieve data sorted by the indexed columns
It Suitable for high cardinality columns T
he data have less duplication or unique.
Updates on key columns is unexpensive.

Syntax
Create index indexname on tablename (columnname);
oracle index

Before Creating B-tree Index:

For example we have an scott user with emp table.

	SQL> conn scott/tiger
	Connected.

	SQL> show user
	USER is "SCOTT"

	select * from emp;

	 EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
	----	------	-----		-------	-------	   -----    ------   ----------
	  7369 SMITH	CLERK		7902 17-DEC-80	    800 		   20
	  7499 ALLEN	SALESMAN	7698 20-FEB-81	   1600        300	   30
	  7521 WARD	SALESMAN	7698 22-FEB-81	   1250        500	   30
	  7566 JONES	MANAGER		7839 02-APR-81	   2975 		   20
	  7654 MARTIN	SALESMAN	7698 28-SEP-81	   1250       1400	   30
	  7698 BLAKE	MANAGER		7839 01-MAY-81	   2850 		   30
	  7782 CLARK	MANAGER		7839 09-JUN-81	   2450 		   10
	  7788 SCOTT	ANALYST		7566 19-APR-87	   3000 		   20
	  7839 KING	PRESIDENT	x    17-NOV-81	   5000 		   10
	  7844 TURNER	SALESMAN	7698 08-SEP-81	   1500 	 0	   30
	  7876 ADAMS	CLERK		7788 23-MAY-87	   1100 		   20
	  7900 JAMES	CLERK		7698 03-DEC-81	    950 		   30
	  7902 FORD	ANALYST		7566 03-DEC-81	   3000 		   20
	  7934 MILLER	CLERK		7782 23-JAN-82	   1300 		   10

	14 rows selected.

	SQL> desc user_indexes
	 Name	      Null?	Type

	SQL> select index_name,index_type,status,table_name from user_indexes;

	SQL> select ename from emp where ename='SMITH';

	ENAME
	----------
	SMITH

	SQL> explain plan for select ename from emp where ename='SMITH';
	Explained.

	SQL> select * from table(dbms_xplan.display);

	PLAN_TABLE_OUTPUT
	--------------------------------------------------------------------------
	Plan hash value: 3956160932
	--------------------------------------------------------------------------
	| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
	--------------------------------------------------------------------------
	|   0 | SELECT STATEMENT  |	 |     1 |     7 |     3   (0)| 00:00:01 |
	|*  1 |  TABLE ACCESS FULL| EMP  |     1 |     7 |     3   (0)| 00:00:01 |
	--------------------------------------------------------------------------

	Predicate Information (identified by operation id):
	---------------------------------------------------
	   1 - filter("ENAME"='SMITH')

	Note:
	-----
	   - dynamic statistics used: dynamic sampling (level=2)

	17 rows selected.

After Creating a BTREE INDEX:

	SQL> create index idx on emp(ename);
	Index created.

	SQL> select index_name,index_type,status,table_name from user_indexes;

	INDEX_NAME	     INDEX_TYPE 	  STATUS   TABLE_NAME
	-------------------- -------------------- -------- -----
	PK_DEPT 	     NORMAL		  VALID    DEPT
	PK_EMP		     NORMAL		  VALID    EMP
	IDX		     NORMAL		  VALID    EMP

	SQL> select ename from emp where ename='SMITH';

	ENAME
	----------
	SMITH

	SQL> explain plan for select ename from emp where ename='SMITH';
	Explained.

	SQL> select * from table(dbms_xplan.display);

	PLAN_TABLE_OUTPUT
	------------------------------------------------------------------------
	Plan hash value: 2353645359
	-------------------------------------------------------------------------
	| Id  | Operation	 | Name | Rows	| Bytes | Cost (%CPU)| Time	|
	-------------------------------------------------------------------------
	|   0 | SELECT STATEMENT |	|     1 |     7 |     1   (0)| 00:00:01 |
	|*  1 |  INDEX RANGE SCAN| IDX	|     1 |     7 |     1   (0)| 00:00:01 |
	-------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	---------------------------------------------------
	   1 - access("ENAME"='SMITH')
	Note
	-----
	   - dynamic statistics used: dynamic sampling (level=2)
	17 rows selected.

Bitmap Index

An bitmap index entry uses a bitmap to point to multiple rows.
It useful low cardinality columns
The data have high duplication.
Updates to key columns very expensive

Syntax :

	Create bitmap index indexname on tablename(columnname);

Bitmap Index An bitmap index entry uses a bitmap to point to multiple rows.

	SQL> select job from emp where job='CLERK';

	JOB
	---------
	CLERK
	CLERK
	CLERK
	CLERK

	SQL> explain plan for select  job from emp where job='CLERK';
	Explained.

	SQL> select * from table(dbms_xplan.display);

	PLAN_TABLE_OUTPUT
	-------------------------------------------------------------------------
	Plan hash value: 3956160932
	--------------------------------------------------------------------------
	| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
	--------------------------------------------------------------------------
	|   0 | SELECT STATEMENT  |	 |     4 |    24 |     3   (0)| 00:00:01 |
	|*  1 |  TABLE ACCESS FULL| EMP  |     4 |    24 |     3   (0)| 00:00:01 |
	--------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	--------------------------------------------------
	   1 - filter("JOB"='CLERK')
	Note
	-----
	   - dynamic statistics used: dynamic sampling (level=2)
	17 rows selected.

Creating bitmap index:

	SQL> create bitmap index bi on emp(job);
	Index created.

	SQL> select index_name,index_type,status,table_name from
	user_indexes;

	INDEX_NAME	     INDEX_TYPE 	  STATUS   TABLE_NAME
	-------------------- -------------------- -------- --------
	PK_DEPT 	     NORMAL		  VALID    DEPT
	PK_EMP               NORMAL		  VALID    EMP
	BI		     BITMAP		  VALID    EMP
	IDX		     NORMAL		  VALID    EMP

	SQL> explain plan for select  job from emp where job='CLERK';
	Explained.

	SQL> select * from table(dbms_xplan.display);

	PLAN_TABLE_OUTPUT
	-----------------------------------------------------------------------------------
	Plan hash value: 4150301131
	-------------------------------------------------------------------------------------
	| Id  | Operation		     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
	-------------------------------------------------------------------------------------
	|   0 | SELECT STATEMENT	     |	    |	  4 |	 24 |	  1   (0)| 00:00:01 |
	|   1 |  BITMAP CONVERSION TO ROWIDS |	    |	  4 |	 24 |	  1   (0)| 00:00:01 |
	|*  2 |   BITMAP INDEX FAST FULL SCAN| BI   |	    |	    |		 |	    |
	-------------------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	---------------------------------------------------
	   2 - filter("JOB"='CLERK')
	Note
	-----
	   - dynamic statistics used: dynamic sampling (level=2)
	18 rows selected.

Function Based Index :

	This Index includes columns that are either transformed by a function.

	SQL> select ename,length(ename) from emp;

	ENAME		LENGTH(ENAME)
	------     	-------------
	SMITH		5
	ALLEN		5
	WARD		4
	JONES		5
	MARTIN		6
	BLAKE		5
	CLARK		5
	SCOTT		5
	KING		4
	TURNER		6
	ADAMS		5
	JAMES		5
	FORD		4
	MILLER		6

	14 rows selected.

	SQL> select ename,length(ename) from emp
	where length(ename)=6;

	ENAME		LENGTH(ENAME)
	--------	------------
	MARTIN		6
	TURNER		6
	MILLER		6

	SQL> explain plan for select  ename,length(ename)
	from emp  where length(ename)=6;

	Explained.

	SQL> select * from table(dbms_xplan.display);

	PLAN_TABLE_OUTPUT
	-------------------------------------------------------------------------
	Plan hash value: 3956160932
	--------------------------------------------------------------------------
	| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
	--------------------------------------------------------------------------
	|   0 | SELECT STATEMENT  |	 |     3 |    21 |     3   (0)| 00:00:01 |
	|*  1 |  TABLE ACCESS FULL| EMP  |     3 |    21 |     3   (0)| 00:00:01 |
	--------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	--------------------------------------------------
	   1 - filter(LENGTH("ENAME")=6)
	Note
	-----
	   - dynamic statistics used: dynamic sampling (level=2)
	17 rows selected.

	SQL> create index fi_idx2 on emp(length(ename));
	Index created.

	SQL> select index_name,index_type,status,table_name
	from user_indexes where index_name='FI_IDX2';

	INDEX_NAME	     INDEX_TYPE 		    STATUS   TABLE_NAME
	-------------------- ------------------------------ -------- -------
	FI_IDX2 	     FUNCTION-BASED NORMAL	    VALID    EMP

	SQL> explain plan for select  ename,length(ename)
	from emp  where length(ename)=6;
	Explained.

	SQL> select * from table(dbms_xplan.display);

	PLAN_TABLE_OUTPUT
	---------------------------------------------------------------------------------------------
	Plan hash value: 729672940
	-----------------------------------------------------------------------------------------------
	| Id  | Operation			    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
	-----------------------------------------------------------------------------------------------
	|   0 | SELECT STATEMENT		    |	      |     3 |    60 |     2	(0)| 00:00:01 |
	|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP     |     3 |    60 |     2	(0)| 00:00:01 |
	|*  2 |   INDEX RANGE SCAN		    | FI_IDX2 |     3 |       |     1	(0)| 00:00:01 |
	-----------------------------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	---------------------------------------------------
	   2 - access(LENGTH("ENAME")=6)
	Note
	-----
	   - dynamic statistics used: dynamic sampling (level=2)

	18 rows selected.

Unique Index :

	To maintain data integrity by ensuring that no duplicates data in a table
	SQL> create table unique_tesdb(id number,name varchar2(20));
	Table created.

	SQL> create unique index uq on unique_tesdb(id);
	Index created.

	SQL> insert into unique_tesdb values(1,'center');
	1 row created.

	SQL> insert into unique_tesdb values(1,'center');

	insert into unique_tesdb values(1,'center')
	*
	ERROR at line 1:
	ORA-00001: unique constraint (SCOTT.UQ) violated


	SQL> select index_name,index_type,status,table_name
	from user_indexes where index_name='UQ';

	INDEX_NAME	INDEX_TYPE	STATUS	TABLE_NAME
	-----------	----------	-------- -----------
	UQ		NORMAL		VALID	UNIQUE_TESDB

Composite Index :

	- A composite index is comprised of two or more columns.

	- Composite indexes should be avoided as they are large
	in size and can be have a performance overhead.

	- A composite index contains more than one key column.

	- Composite indexes can provide additional advantages over
	single-column indexes

	SQL> select deptno,job from emp where deptno=20 and job='CLERK';
		DEPTNO JOB
	---------- ---------
		20 CLERK
		20 CLERK

	SQL> explain plan for select deptno,job from emp
	where deptno=20 and job='CLERK';

	Explained.

	SQL> select * from table(dbms_xplan.display);

	PLAN_TABLE_OUTPUT
	--------------------------------------------------------------------------
	Plan hash value: 3956160932
	--------------------------------------------------------------------------
	| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
	--------------------------------------------------------------------------
	|   0 | SELECT STATEMENT  |	 |     2 |    38 |     3   (0)| 00:00:01 |
	|*  1 |  TABLE ACCESS FULL| EMP  |     2 |    38 |     3   (0)| 00:00:01 |
	-------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	---------------------------------------------------
	   1 - filter("DEPTNO"=20 AND "JOB"='CLERK')

	Note :
	-----
	   - dynamic statistics used: dynamic sampling (level=2)
	17 rows selected.

	SQL> create index cidx on emp (deptno,job);
	Index created.

	SQL> explain plan for select deptno,job from emp where deptno=20 and job='CLERK';
	Explained.

	SQL> select * from table(dbms_xplan.display);

	PLAN_TABLE_OUTPUT
	-------------------------------------------------------------------------
	Plan hash value: 3105059857
	-------------------------------------------------------------------------
	| Id  | Operation	 | Name | Rows	| Bytes | Cost (%CPU)| Time	|
	-------------------------------------------------------------------------
	|   0 | SELECT STATEMENT |	|     2 |    38 |     1   (0)| 00:00:01 |
	|*  1 |  INDEX RANGE SCAN| CIDX |     2 |    38 |     1   (0)| 00:00:01 |
	-------------------------------------------------------------------------
	Predicate Information (identified by operation id):
	---------------------------------------------------
	   1 - access("DEPTNO"=20 AND "JOB"='CLERK')

	Note :

	   - dynamic statistics used: dynamic sampling (level=2)

	17 rows selected.

	SQL>  select index_name,column_name,table_name from user_ind_columns where index_name='CIDX';

	INDEX_NAME	COLUMN_NAME	TABLE_NAME
	----------	------------	----------
	CIDX		DEPTNO		EMP
	CIDX		JOB		EMP

« Previous Next Topic » (Oracle Database - Partitions)