BTree Index OR Simple Index in Oracle

(See the Video on My Channel – Oracle Shooter)

https://www.youtube.com/oracleshooter

Cover Topics:-

1. Types of Indexes (In Previous video 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

BTree Index:-

Oracle creates ordered information divided into proper range along with ROWID information stored in Index segment.

2. Syntax Of BTree Index

Create Index XX_BTREE_MGR_INDX ON XXEMP_TL(MGR)

Create Index XX_BTREE_EMPNO_INDX ON XXEMP_TL(EMPNO)

3. How it stored data in database

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

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

5. on which column we need to create BTree Index

Ex:- 1. Bank Account Number

            2. EMPNO

            3. ADHAAR NO

            4. EMAIL

            5. MOBILE NO

6. Advantages and Disadvantages of BTree index (See the video)

1. Over indexing is not good

2. DML operation getting slow

3. Query is Fast

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

LAB

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

Create (Simple / Normal / B-Tree Index )

What Is B-Tree Index:-  Oracle creates ordered information devided in to proper range along with ROWID information stored in Index segment.

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

--How to Understand the ordered way ( without using order by  clause) --

select * from XXEMP_TL

Create Index XX_BTREE_MGR_INDX ON XXEMP_TL(MGR)

--drop index XX_BTREE_MGR_INDX

select MGR from XXEMP_TL ---order by 1

select MGR from XXEMP_TL

where MGR < 10000

--order by 1

SYNTAX:-

Create Index XX_BTREE_EMPNO_INDX ON XXEMP_TL(EMPNO)

DROP INDEX XX_BTREE_EMPNO_INDX

select ROWID, A.* from XXEMP_TL A order by empno

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

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

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

Example :- 

select EMPNO from  XXEMP_TL

select * from XXEMP_TL where EMPNO = 7499

----  Explain Plan show this information ----

explain plan into sys.plan_table$ for

select * from XXEMP_TL where EMPNO = 7499

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'

For Better Understanding Please See all Index related Video

https://www.youtube.com/oracleshooter