Function Based Function

Function Based Index for better understanding please go to the youtube channel..

https://www.youtube.com/oracleshooter

ROWID	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
AAns3GAHTAAOcczAAA	7369	Smith	CLERK	7902	17-Dec-80	800		20	1234543256	M
AAns3GAHTAAOcczAAB	7499	Allen	SALESMAN	7698	20-Feb-81	1600	300	30	2123212541	M
AAns3GAHTAAOcczAAC	7521	Ward	SALESMAN	7698	22-Feb-81	1250	500	30	9898989898	M
AAns3GAHTAAOcczAAD	7566	Jones	MANAGER	7839	02-Apr-81	2975		20	8989898989	M
AAns3GAHTAAOcczAAE	7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30	7878787878	M
AAns3GAHTAAOcczAAF	7698	BLAKE	MANAGER	7839	01-May-81	2850		30	6767676767	M
AAns3GAHTAAOcczAAG	7782	CLARK	MANAGER	7839	09-Jun-81	2450		10	5656565656	M
AAns3GAHTAAOcczAAH	7788	Scott	ANALYST	7566	09-Dec-82	3000		20	4545454545	M
AAns3GAHTAAOcczAAI	7839	KING	PRESIDENT		17-Nov-81	5000		10	3434343434	M
AAns3GAHTAAOcczAAJ	7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30	2323232323	M
AAns3GAHTAAOcczAAK	7876	ADAMS	CLERK	7788	12-Jan-83	1100		20	1212121212	M
AAns3GAHTAAOcczAAL	7900	James	CLERK	7698	03-Dec-81	950		30	9090909090	M
AAns3GAHTAAOcczAAM	7902	FORD	ANALYST	7566	03-Dec-81	3000		20	101010101	M
AAns3GAHTAAOcczAAN	7934	MILLER	CLERK	7782	23-Jan-82	1300		10	202020202	M
AAns3GAHTAAOcczAAO	7999	Rani	SALESMAN	7698	23-Sep-88	1900	10	10	303030303	F

Cover Topics:-

 Types of Indexes (In Previous vedio we already discussed)

    A. Simple/Normal/BTree Index

    B. Composite Index

    C. Unique Index

    D. Reverse Key Index

    E. Function Based Index

    F. BitMap Index

1. What is Function-Based Index (See the Vedio on My Channel – Oracle Shooter)

https://www.youtube.com/oracleshooter

2. Syntax Of Function-Based Index

Create Index XX_FUNC_ENAME_INDX ON XXEMP_TL(UPPER(ENAME))

3. How it stored data in database

ROWID	EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	MOBILENO	GENDER
AAns3GAHTAAOcczAAA	7369	Smith	CLERK	19-Aug-21	17-Dec-80	800		20	1234543256	M
AAns3GAHTAAOcczAAB	7499	Allen	SALESMAN	27-Jan-21	20-Feb-81	1600	300	30	2123212541	M
AAns3GAHTAAOcczAAC	7521	Ward	SALESMAN	27-Jan-21	22-Feb-81	1250	500	30	9898989898	M
AAns3GAHTAAOcczAAD	7566	Jones	MANAGER	17-Jun-21	02-Apr-81	2975		20	8989898989	M
AAns3GAHTAAOcczAAE	7654	MARTIN	SALESMAN	27-Jan-21	28-Sep-81	1250	1400	30	7878787878	M
AAns3GAHTAAOcczAAF	7698	BLAKE	MANAGER	17-Jun-21	01-May-81	2850		30	6767676767	M
AAns3GAHTAAOcczAAG	7782	CLARK	MANAGER	17-Jun-21	09-Jun-81	2450		10	5656565656	M
AAns3GAHTAAOcczAAH	7788	Scott	ANALYST	17-Sep-20	09-Dec-82	3000		20	4545454545	M
AAns3GAHTAAOcczAAI	7839	KING	PRESIDENT		17-Nov-81	5000		10	3434343434	M
AAns3GAHTAAOcczAAJ	7844	TURNER	SALESMAN	27-Jan-21	08-Sep-81	1500	0	30	2323232323	M
AAns3GAHTAAOcczAAK	7876	ADAMS	CLERK	27-Apr-21	12-Jan-83	1100		20	1212121212	M
AAns3GAHTAAOcczAAL	7900	James	CLERK	27-Jan-21	03-Dec-81	950		30	9090909090	M
AAns3GAHTAAOcczAAM	7902	FORD	ANALYST	17-Sep-20	03-Dec-81	3000		20	101010101	M
AAns3GAHTAAOcczAAN	7934	MILLER	CLERK	21-Apr-21	23-Jan-82	1300		10	202020202	M
AAns3GAHTAAOcczAAO	7999	Rani	SALESMAN	27-Jan-21	23-Sep-88	1900	10	10	303030303	F

4. When we run our query, our index is used by query or not ( By using Explain Plan)

----  Explain Plan show ----

explain plan into sys.plan_table$ for

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

select * from table(dbms_xplan.display('sys.plan_table$'));

5. on which column we need to create Function-Based Index

Choose that column on which you need to put any function in where clause.

6. Advantages and Disadvantages of Function-Based index (See the video)

1. Over indexing is not good

2. DML operation getting slow

3. Query is Fast

7. LAB

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

Function Based Index

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

select * from XXEMP_TL

Syntax:-

Create Index XX_FUNC_ENAME_INDX ON XXEMP_TL(UPPER(ENAME))

DROP INDEX XX_FUNC_ENAME_INDX

Example:-

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

Whether Index is used by our Query  or not how to check this ?

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

Example :- 

----  Explain Plan show ----

explain plan into sys.plan_table$ for

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

select * from table(dbms_xplan.display('sys.plan_table$'));

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

Metadat Stored in Oracle Tables

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

select * from USER_INDEXES where table_name = 'XXEMP_TL'

select * from USER_IND_COLUMNS  where table_name = 'XXEMP_TL'

select * from USER_IND_STATISTICS where table_name = 'XXEMP_TL'

select * from USER_IND_EXPRESSIONS where table_name = 'XXEMP_TL'

For Better Understanding Please See all Index related Video