Reverse key Index

Reverse Key Index
--================================================================

for better understanding please go to the YouTube channel.

https://www.youtube.com/oracleshooter

ROWID	EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO	MOBILENO	GENDER	Performance Score
AAns3GAHTAAOcczAAA	7369	SMITH	CLERK	7902	800		20	1234543256	M	11
AAns3GAHTAAOcczAAB	7499	ALLEN	SALESMAN	7698	1600	300	30	2123212541	M	17
AAns3GAHTAAOcczAAC	7521	WARD	SALESMAN	7698	1250	500	30	9898989898	M	96
AAns3GAHTAAOcczAAD	7566	JONES	MANAGER	7839	2975		20	8989898989	M	33
AAns3GAHTAAOcczAAE	7654	MARTIN	SALESMAN	7698	1250	1400	30	7878787878	M	45
AAns3GAHTAAOcczAAF	7698	BLAKE	MANAGER	7839	2850		30	6767676767	M	100
AAns3GAHTAAOcczAAG	7782	CLARK	MANAGER	7839	2450		10	5656565656	M	55
AAns3GAHTAAOcczAAH	7788	SCOTT	ANALYST	7566	3000		20	4545454545	M	65
AAns3GAHTAAOcczAAI	7839	KING	PRESIDENT		5000		10	3434343434	M	95
AAns3GAHTAAOcczAAJ	7844	TURNER	SALESMAN	7698	1500	0	30	2323232323	M	99
AAns3GAHTAAOcczAAK	7876	ADAMS	CLERK	7788	1100		20	1212121212	M	98
AAns3GAHTAAOcczAAL	7900	JAMES	CLERK	7698	950		30	9090909090	M	93
AAns3GAHTAAOcczAAM	7902	FORD	ANALYST	7566	3000		20	101010101	M	91
AAns3GAHTAAOcczAAN	7934	MILLER	CLERK	7782	1300		10	202020202	M	92
AAns3GAHTAAOcczAAO	7999	Rani	SALESMAN	7698	1900	10	10	303030303	F	90

Cover Topics:-

 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

1. What is Reverse Key Index (See the Video on My Channel – Oracle Shooter)

https://www.youtube.com/oracleshooter

2. Syntax Of Reverse Key Index

CREATE  Index XX_SCORE_R_INDX ON XXEMP_TL(PERFORMANCE_SCORE)  REVERSE

3. How it stored data in the database

  

ROWID	EMPNO	ENAME	JOB	MGR	SAL	COMM	DEPTNO	MOBILENO	GENDER	Performance Score
AAns3GAHTAAOcczAAA	7369	SMITH	CLERK	7902	800		20	1234543256	M	11
AAns3GAHTAAOcczAAB	7499	ALLEN	SALESMAN	7698	1600	300	30	2123212541	M	17
AAns3GAHTAAOcczAAC	7521	WARD	SALESMAN	7698	1250	500	30	9898989898	M	96
AAns3GAHTAAOcczAAD	7566	JONES	MANAGER	7839	2975		20	8989898989	M	33
AAns3GAHTAAOcczAAE	7654	MARTIN	SALESMAN	7698	1250	1400	30	7878787878	M	45
AAns3GAHTAAOcczAAF	7698	BLAKE	MANAGER	7839	2850		30	6767676767	M	100
AAns3GAHTAAOcczAAG	7782	CLARK	MANAGER	7839	2450		10	5656565656	M	55
AAns3GAHTAAOcczAAH	7788	SCOTT	ANALYST	7566	3000		20	4545454545	M	65
AAns3GAHTAAOcczAAI	7839	KING	PRESIDENT		5000		10	3434343434	M	95
AAns3GAHTAAOcczAAJ	7844	TURNER	SALESMAN	7698	1500	0	30	2323232323	M	99
AAns3GAHTAAOcczAAK	7876	ADAMS	CLERK	7788	1100		20	1212121212	M	98
AAns3GAHTAAOcczAAL	7900	JAMES	CLERK	7698	950		30	9090909090	M	93
AAns3GAHTAAOcczAAM	7902	FORD	ANALYST	7566	3000		20	101010101	M	91
AAns3GAHTAAOcczAAN	7934	MILLER	CLERK	7782	1300		10	202020202	M	92
AAns3GAHTAAOcczAAO	7999	Rani	SALESMAN	7698	1900	10	10	303030303	F	90

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 PERFORMANCE_SCORE  between 10 and 50  -- ( not supported by Reverse key but fully supported by BTree Index)

--select  * from XXEMP_TL where PERFORMANCE_SCORE > 30 --( not supported by Reverse key but fully supported by BTree Index)

select  * from XXEMP_TL where PERFORMANCE_SCORE = 55  --  (  supported by both Reverse key and  BTree Index)

--select  * from XXEMP_TL where PERFORMANCE_SCORE  in ( 11, 55, 98 ) --  (  supported by both Reverse key and  BTree Index)

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

1. not supported by Reverse Key Index

2. supported by Reverse Key Index

3. supported by Btree Index

4. supported by Btree Index

5. where we create Reverse key Index how to select that column

Select that column where most of the data is cover in one side means when you think about the distribution all data comes in one side , you are not able to create the balanced tree.

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

1. system will not do INDEX RANGE SCAN – ( between and > and < ) ( Disadvantages)

2. DML operation getting slow

3. Query is Fast only for ( in and – Operators )

4. index block contention ( advantage)

7. LAB

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

Reverse key Index

Note :- will not be do used INDEX RANGE SCAN

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

select rowid, a.* from XXEMP_TL a

CREATE  Index XX_SCORE_R_INDX ON XXEMP_TL(PERFORMANCE_SCORE)  REVERSE

DROP INDEX XX_SCORE_R_INDX

Example :-

select  * from XXEMP_TL where PERFORMANCE_SCORE  between 10 and 50

select  * from XXEMP_TL where PERFORMANCE_SCORE > 30

select  * from XXEMP_TL where PERFORMANCE_SCORE = 55

select  * from XXEMP_TL where PERFORMANCE_SCORE  in ( 11, 55, 98 )

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

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 PERFORMANCE_SCORE  between 10 and 50  -- ( not supported by Reverse key but fully supported by BTree Index)

--select  * from XXEMP_TL where PERFORMANCE_SCORE > 30 --( not supported by Reverse key but fully supported by BTree Index)

select  * from XXEMP_TL where PERFORMANCE_SCORE = 55  --  (  supported by both Reverse key and  BTree Index)

--select  * from XXEMP_TL where PERFORMANCE_SCORE  in ( 11, 55, 98 ) --  (  supported by both Reverse key and  BTree Index)

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