Function Based Function
Function Based Index for better understanding please go to the
youtube channel..
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)
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
Comments
Post a Comment