Hi Siva, thank you very much. I had gone through this video about an year ago ang noted it. But today interviwer asked about index block contention. I was struggled to answer that question. Now again after this video , got refreshed. Thank tou 😀
One of the best videos/material I've ever seen explaining Index contention and reverse key Index. Thank you so much. You deserve a Like and Subscribe :)
Hi shiva, Hands down the best explanation. I wanted to understand even though it’s addressing the distribution of data, but what is the point of creating the index if after the select query in the explain plan it’s going for full table search instead of index search? Please kindly help me understand this.
Reverse indexes are just as efficient as unreversed indexes for finding specific values, although they aren't helpful for range queries. Range queries are uncommon for artificial values such as sequence numbers. When searching the index, the query processor simply reverses the search target before looking it up. I hope this answer helps you to understand the usage of reverse key index.:)
As usual, very nicely explained Siva! However, I have 2 queries. 1.why would we ever use reverse key index if it does not perform the very function of an index, which is a quick scan using range scan. 2. Also, why would we rebuild such index. Why not simply drop it and create a new index. Thanks!
@@SivaAcademy Same Question I am also having, If you say that reverse key solves the index block contention problem , My antenna says how did we get this problem in the first place , because of the index creation , but that normal index would have given some performance also but the reverse key even after solving the index block contention issue does the full table scan.... It better not to have any index at all in such scenario.... please correct me if I am wrong..
@@anandsuman2012 I have the same question, also the article "docs.oracle.com/cd/A87860_01/doc/paraserv.817/a76970/appanaly.htm#5247" says it maintains the column order but in the video its says it don't.
@Vedant, Thanks for your comment. Insertion wont become faster, indexes help to improve query performance., insert will have additional overhead of inserting into index segment as well..
Hey Siva, its really very well explained a layman can also understand the concept....Have a suggestion, Could you also include a topic, in which scenario/s index could be rebuilt??
@Robertcrues, Any dml changes to the base table, will impact the underlying index data, hence its applicable for reversekey index as well, good that you raised this point, i will post a video specific on index impact due to DML operations
Shiva academy PLSql videos are awesome and simple to understand.. I am trying to switch my company by these videos only..🙏😊
Hi Siva, thank you very much. I had gone through this video about an year ago ang noted it. But today interviwer asked about index block contention. I was struggled to answer that question. Now again after this video , got refreshed. Thank tou 😀
Great 😊😊👍
Super explanation. Every day I am tuning your channel to get some information from your Academy. Thank you so much.
You are most welcome
You are grate sir, I understand every topic you teach
My pleasure bro, thank you
Very useful and simple way to explain the reverse index pros/cons. Thank you.
Welcome 🙏
One of the best videos/material I've ever seen explaining Index contention and reverse key Index. Thank you so much. You deserve a Like and Subscribe :)
It's my pleasure,Thank you so much :-)
you are a very great person
My pleasure
You are brilliant Siva , such a key points like range scan is not applicable on reverse key index ,rare people will tell these things in any video
My pleasure, thank you bro
One of the best !!!!
Thank you
Hi shiva,
Hands down the best explanation.
I wanted to understand even though it’s addressing the distribution of data, but what is the point of creating the index if after the select query in the explain plan it’s going for full table search instead of index search?
Please kindly help me understand this.
Reverse indexes are just as efficient as unreversed indexes for finding specific values, although they aren't helpful for range queries. Range queries are uncommon for artificial values such as sequence numbers. When searching the index, the query processor simply reverses the search target before looking it up. I hope this answer helps you to understand the usage of reverse key index.:)
Superb ,,❤❤
Thank you
Nice explain. I have one query, if the index key is a varchar column, then how it will reverse it?
How does Index work with Varchar columns , how is the tree branches and leafs created and how does reverse index work?
Thank you
Great explanation. Thank you so much.
Welcome bro 💐
excellent, thanks for the explanation!
Welcome
As usual, very nicely explained Siva! However, I have 2 queries.
1.why would we ever use reverse key index if it does not perform the very function of an index, which is a quick scan using range scan.
2. Also, why would we rebuild such index. Why not simply drop it and create a new index.
Thanks!
1. Reverse key index is mainly to avoid index index contention
2. rebuild will not have to recreate the index from scratch
@@SivaAcademy Same Question I am also having, If you say that reverse key solves the index block contention problem , My antenna says how did we get this problem in the first place , because of the index creation , but that normal index would have given some performance also but the reverse key even after solving the index block contention issue does the full table scan.... It better not to have any index at all in such scenario.... please correct me if I am wrong..
@@anandsuman2012 I have the same question, also the article "docs.oracle.com/cd/A87860_01/doc/paraserv.817/a76970/appanaly.htm#5247" says it maintains the column order but in the video its says it don't.
superbly explain.. Now i understand block contention issue properly and how reverse index works..
how insertion become fast with reverse index ?
@Vedant, Thanks for your comment. Insertion wont become faster, indexes help to improve query performance., insert will have additional overhead of inserting into index segment as well..
here question is when reverse key index will be use. can you please tell in which condition this index will be use
Hey Siva, its really very well explained a layman can also understand the concept....Have a suggestion, Could you also include a topic, in which scenario/s index could be rebuilt??
Hi Vipul,
Sure, I will add a video on index rebuild scenarios and use cases.
Thanks for your comment.
Hi Siva
By default index will have their own impact while insertion. What about reverse key? Can you please explain
Hi what is clustered index is this part of oracle Or sql server
Is there any explanation for index skip scan ?
How this index works for non numerical values
To be summarize,
Only thing we can use this index when there is lot of index data contention and less use of index rage scan.
Correct me if I'm wrong.
Absolutely right
What is the problem caused by index block contention
if full table scan is going on then the performance would be still slow right ?
Yes, but not always. Full table scan would be good in few cases
Hi Siva. Could you please explain the global index and clustered and non clustured index
Sure, Please stay tuned.
Where Clustered and non-clustered terminolgy used then sir?
Excellent
@Ganesh, thanks bro 🙏
Please let us know about partition pruning...
Sure, its already part of my upcoming list, please stay tuned 👍
Super
🙏 Thanks bro
Hi Siva
By default index will have their own impact while insertion. What about reverse key? Can you please explain
@Robertcrues, Any dml changes to the base table, will impact the underlying index data, hence its applicable for reversekey index as well, good that you raised this point, i will post a video specific on index impact due to DML operations