All Indexes and its syntax with Examples

All Indexes and its syntax with Examples

(See the Video on My Channel – Oracle Shooter)

https://www.youtube.com/oracleshooter

--====================================================

Index Synatx:- 

--====================================================

CREATE <Index_Type >  Index <INDEX_NAME>  ON   <TABLE_NAME ( COLUMN_NAME)> 

--====================================================

--B-Tree / Noraml / Simple  Index

--====================================================

select * from XXEMP_TL

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7369	SMITH	CLERK	7902	17-Dec-80	800		20	1234543256	M
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30	2123212541	M
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30	9898989898	M
7566	JONES	MANAGER	7839	02-Apr-81	2975		20	8989898989	M
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30	7878787878	M
7698	BLAKE	MANAGER	7839	01-May-81	2850		30	6767676767	M
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10	5656565656	M
7788	SCOTT	ANALYST	7566	09-Dec-82	3000		20	4545454545	M
7839	KING	PRESIDENT		17-Nov-81	5000		10	3434343434	M
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30	2323232323	M
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20	1212121212	M
7900	JAMES	CLERK	7698	03-Dec-81	950		30	9090909090	M
7902	FORD	ANALYST	7566	03-Dec-81	3000		20	101010101	M
7934	MILLER	CLERK	7782	23-Jan-82	1300		10	202020202	M
7999	Rani	SALESMAN	7698	23-Sep-88	1900	10	10	303030303	F

Create Index XX_BTREE_EMPNO_INDX ON XXEMP_TL(EMPNO)

Example :- 

select EMPNO from  XXEMP_TL

select * from XXEMP_TL where EMPNO = 7499

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30	2123212541	M

--====================================================

--Unique Index

--====================================================

Create UNIQUE Index XX_UNIQUE_MOBILENO_INDX ON XXEMP_TL(MOBILENO)

Example:- 

select * from XXEMP_TL where MOBILENO = 2123212541

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30	2123212541	M

--====================================================

--Composite Index

--====================================================

Create Index XX_COMP_INDX ON XXEMP_TL(JOB, DEPTNO)

Example:-

select * from XXEMP_TL where JOB IN('SALESMAN','CLERK') AND DEPTNO = 10

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7934	MILLER	CLERK	7782	23-Jan-82	1300		10	202020202	M
7999	Rani	SALESMAN	7698	23-Sep-88	1900	10	10	303030303	F

--====================================================

--Reverse Key Index

--====================================================

Create Index XX_SAL_INDX_R ON XXEMP_TL(SAL) REVERSE

Example :-

select * from XXEMP_TL where SAL = 1500

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30	2323232323	M

Create UNIQUE Index XX_COMM_INDX_UR ON XXEMP_TL(COMM) REVERSE

Example :-

select * from XXEMP_TL where COMM IN (300,500)

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30	2123212541	M
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30	9898989898	M

--====================================================

--Function Based Index

--====================================================

Create Index XX_FUNC_ENAME_INDX ON XXEMP_TL(UPPER(ENAME))

Example :-

select * from XXEMP_TL where UPPER(ENAME) = 'RANI'

Create Index XX_FUNC_HDATE_INDX ON XXEMP_TL(TO_CHAR(HIREDATE,'YYYY'))

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7999	Rani	SALESMAN	7698	23-Sep-88	1900	10	10	303030303	F

Example :-

select * from XXEMP_TL where TO_CHAR(HIREDATE,'YYYY') = 1981

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30	2123212541	M
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30	9898989898	M
7566	JONES	MANAGER	7839	02-Apr-81	2975		20	8989898989	M
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30	7878787878	M
7698	BLAKE	MANAGER	7839	01-May-81	2850		30	6767676767	M
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10	5656565656	M
7839	KING	PRESIDENT		17-Nov-81	5000		10	3434343434	M
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30	2323232323	M
7876	ADAMS	CLERK	7788	12-Jan-83	1100		20	1212121212	M
7900	JAMES	CLERK	7698	03-Dec-81	950		30	9090909090	M
7902	FORD	ANALYST	7566	03-Dec-81	3000		20	101010101	M

--====================================================

--Bitmap Index

--====================================================

Create BITMAP Index XX_BITMAP_DEPTNO_INDX ON XXEMP_TL(DEPTNO)

Example :-

select * from XXEMP_TL where DEPTNO = 10

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7782	CLARK	MANAGER	7839	09-Jun-81	2450		10	5656565656	M
7839	KING	PRESIDENT		17-Nov-81	5000		10	3434343434	M
7934	MILLER	CLERK	7782	23-Jan-82	1300		10	202020202	M
7999	Rani	SALESMAN	7698	23-Sep-88	1900	10	10	303030303	F

Create BITMAP Index XX_BITMAP_GENDER_INDX ON XXEMP_TL(GENDER)

Example :-

select * from XXEMP_TL where GENDER = 'F'

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
7999	Rani	SALESMAN	7698	23-Sep-88	1900	10	10	303030303	F

--====================================================

Know about your indexes

--====================================================

select * from USER_INDEXES where table_name = 'XXEMP_TL'

INDEX_NAME	INDEX_TYPE	TABLE_NAME	TABLE_TYPE
XX_BTREE_EMPNO_INDX	NORMAL	XXEMP_TL	TABLE
XX_UNIQUE_MOBILENO_INDX	NORMAL	XXEMP_TL	TABLE
XX_COMP_INDX	NORMAL	XXEMP_TL	TABLE
XX_SAL_INDX_R	NORMAL/REV	XXEMP_TL	TABLE
XX_COMM_INDX_UR	NORMAL/REV	XXEMP_TL	TABLE
XX_FUNC_ENAME_INDX	FUNCTION-BASED NORMAL	XXEMP_TL	TABLE
XX_FUNC_HDATE_INDX	FUNCTION-BASED NORMAL	XXEMP_TL	TABLE
XX_BITMAP_DEPTNO_INDX	BITMAP	XXEMP_TL	TABLE
XX_BITMAP_GENDER_INDX	BITMAP	XXEMP_TL	TABLE

select * from USER_IND_COLUMNS  where table_name = 'XXEMP_TL'

INDEX_NAME	TABLE_NAME	COLUMN_NAME
XX_BTREE_EMPNO_INDX	XXEMP_TL	EMPNO
XX_UNIQUE_MOBILENO_INDX	XXEMP_TL	MOBILENO
XX_COMP_INDX	XXEMP_TL	JOB
XX_COMP_INDX	XXEMP_TL	DEPTNO
XX_SAL_INDX_R	XXEMP_TL	SAL
XX_COMM_INDX_UR	XXEMP_TL	COMM
XX_FUNC_ENAME_INDX	XXEMP_TL	SYS_NC00011$
XX_FUNC_HDATE_INDX	XXEMP_TL	SYS_NC00012$
XX_BITMAP_DEPTNO_INDX	XXEMP_TL	DEPTNO
XX_BITMAP_GENDER_INDX	XXEMP_TL	GENDER

select * from USER_IND_STATISTICS where table_name = 'XXEMP_TL'

select * from USER_IND_EXPRESSIONS where table_name = 'XXEMP_TL'

INDEX_NAME	TABLE_NAME	COLUMN_EXPRESSION
XX_FUNC_ENAME_INDX	XXEMP_TL	UPPER("ENAME")
XX_FUNC_HDATE_INDX	XXEMP_TL	TO_CHAR("HIREDATE",'YYYY')

for better understanding please go to the youtube channel..

https://www.youtube.com/oracleshooter