Bitmap Index

Bitmap 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 Bitmap Index (See the Vedio on My Channel – Oracle Shooter)

https://www.youtube.com/oracleshooter

2. Syntax Of Bitmap Index

CREATE BITMAP Index XX_BITMAP_GENDER_INDX ON XXEMP_TL(GENDER)

3. How it stored data in the 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)

----  Explain Plan show ----

explain plan into sys.plan_table$ for

select  * from XXEMP_TL where GENDER = 'F'

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

5. on which column we need to create Bitmap Index

Choose that column where the distinct column values are very less such as : -gender, Active_status, ORG_ID

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

1. Over indexing is not good

2. DML operation getting slow

3. Query is Fast

7. LAB

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

Bitmap Index

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

select * from XXEMP_TL

CREATE BITMAP Index XX_BITMAP_GENDER_INDX ON XXEMP_TL(GENDER)

DROP INDEX XX_BITMAP_GENDER_INDX

Example :-

select * from XXEMP_TL where GENDER in ('F','M')

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

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 GENDER = 'F'

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

DROP INDEX XX_BITMAP_GENDER_INDX

explain plan into sys.plan_table$ for

select   * from XXEMP_TL where GENDER = 'F'

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