Hello welcome back to another set up video so we are going to continue to learn
about in the slip at more in our last video we discus about the intuition
behind index and then we left you at this point that
whenever we are creating an index we are creating an extra storage area and lets say this area is called
my_index and it contains we create an index in employ number so therefore in storage index we have
employ number and a pointer to the actual raw so hear is in this video we are going to learn
something how we are going to implement this area so basically implementation implementation of indexes so this is what we are going to discuss
in this set up videos in this video so essentially oracle provides
you different types of implementation based on different use case and their are a index called bstar tree index and something called reverse reverse bstar index and bitmap index function base index and cluster btree index and so on so their are total 7 to 8 types of index implementation
are their but in this course we are going to discuss
about the 4 important types of you know index implementation and then the remaining
things we can give some example so that you learn like know
how to extend this things ok so if their is a case like lets see in this video lets try to address this btree index so to start with i am going to do this thing so lets say we have this is
some more data like know more kind of table where we can
explain and in this table i have almost 100 raw’s starting from employ id 100 to 200 ok so whenever i am create an index so this is
an employ index and then this is the employee emp id then this is a
address to this raw and so on ok so the question hear is i ask you how we are going to you know basically store this thing are you going to store on
a list so that 100 101 then 102 like that or we can store in different
way the problem storing in the list that if we storing in
the link list something like that some kind of data structure like this then
whenever we are going to search we are going to will traverse the link list completely so instead of doing that lets try to store in a different way and then
let me like know give some institution how do i store
and then we can explain what is the actual program things behind that ok so imagine like know what we have we have employ number 100 to 200 so what i am going to do i am going to put a
rectangular hear and then what i am do i am do to have 2 information hear like if the employ id is less the 150 ok less then 150 then go to another box in the rite side ok so this is the box in the rite side ok so this contains this condition 150 like know
the employ id less then 150 and then one pointer that pointer is basically pointing another block then if i have more that 150 then what i am saying them to go another block in the rite side is it clear so rite side ok so this is like know anything is less
then 150 can be found hear employ id any employ id
greater then 150 will be fond in this side ok so this is the
condition ok so now when i am go to this you know this block if the employ is from 100 to 110 then i tell ok go to another block down below and that block
is this one so that block is this one ok so 100 to 110 to this one 110 to 120 go to this block ok so lets give this block 1 this is block 2 and so on ok and then if the employ id and so on so basically we are going to cover
everything in 100 to say 140 150 and hear is i have another block and that block
is going to contain anything from 140 to 150 now similarly in the left hand side we are going to do some similar kind of thing
that if this thing is being to 150 to 160 go to this block so this block is going to contain all the
employ id from 150 to 160 then another thing from 160 so lets see 160 to 170 the you go to this block ok and so on so finally eventually we are going to have
another block that is going to have 190 to 200 190 to 200 put this block then in this final block was b1 b2 so like know i have 100 then i have
a raw id so raw id of that for example you know
for whatever 100 raw id for this employ 100 what the raw id of this raw id and this raw
id this raw id are going to stored hear in this thing the employ id is
100 and raw id of that address lets say call raw id
1 and raw 1 say raw id 2 similarly we have hear
in this things something called 160 say raw id 9 165 say raw id 55 or something like that ok so this is how i am going to store ok so basically the id instead of storing
them in a link list in a serial order and i am going to tore something is looks like
a tree in this tree this 2 blocks are called branch block and this is the last one is called leaf block so why this leaf block because
it does not have any children ok so this is what this is ends and
remember what is the important thing the branch block contain a condition
and a pointer the pointer to point to the next block leaf
block contains the actual key value which is employ
id 100 and what is actually the address and their is important different so branch
block is evaluated and then passed out to the next one and then
leaf block will going to give me the poverty now lets with this kind of thing
scenario lets try to give a query select * from emp where employ id=165 ok so then whats going to happen we come to this block this block suggest us
to go to the rite side then we come to hit this block and this block suggest us that in order to
get the range 165 we have to go to whatever is
mention hear then i go to this place and it suggest me to go to this block this is the leaf block then i will scan from button to down i realize
that 165 is something hear and this is the where i
get my raw id what is raw id raw id is 55 then what i do i go to this raw id 55 wherever
it is and then pulled that record so pull that record from hear and give it
back so what essential usually you did hear that by storing this
thing in a binary search tree every time you are narrowing
down your search basically the goal of index is to get things faster ok and you achieve that
each time you go to a branch block you are all most
have you know down to 50% down that means whatever your raw id will come in this ares because you do not have to really search this area
any more ok the moment you go hear this area is gone ok and this is what is called
binary search tree implementation of index people called it conventional
index or non conventional index that’s all the same and
then whenever basically this is the additional structure
that oracle maintain behind the seen if you want to do a insert into an table
employee then again is going to insert the rite place of the index
structure hear ok so that is what is all about your binary search tree index


Leave a Reply

Your email address will not be published. Required fields are marked *