Indexing is one of the most frequent approaches when resolving query performance issues raised within a database (though not necessary the right approach, but we can pick this up later). However, in order to better use indexing strategies, we should go trough the process on understanding index types and their functionality. First of all, please … Continue reading “Oracle Indexing”
What is an IOT? An IOT implies having your entire table indexed, within the same B-Tree structure. Should be used for larger scale tables or dimensions, highly used within the database. How to? Accessing the data is done via the primary key, much faster as the key and the data reside in the same structure. … Continue reading “Oracle Index Organized Table(IOT)”
What is a Function-Based Index? A function-based index is an index created on the result of a function or expression. This type of index is more used for situations with atypical searches on the specified columns (e.g. search on an user email address ignoring sensitivity can be optimised using a function based index; rounded up … Continue reading “Oracle Function Based Index”
Common usage of Bitmap Indexes is a data warehousing environment. This implies large amounts of data, high level of ad-hoc queries but a low level of concurrent DLM transactions. Why to use? reduced response time reduced storage requirements compared to other indexing techniques efficient maintenance during parallel DML and load Bitmap indexes are typically only a … Continue reading “Oracle Bitmap Index”
We discussed earlier about indexing, and specifically reverse key indexing. I mentioned that is a solution for buffer busy waits on numerical consecutive inserted keys (like the sequence based generated ones) . This post will test that solution. For regular index test, I’ve created the following table, sequence and associated index: create table test_tb ( … Continue reading “Buffer Busy Waits – Reverse Key Index – Demo”
This is one of the least used Oracle indexes I’ve encountered in a data-warehousing environment. However, a very good instrument in performance problems on OLTPs. To understand a reverse key index, we must first look at the regular b-tree index on a numerical key, and its storage in the database memory. The index will store, … Continue reading “Oracle Reverse Index”
I’m writing this topic mostly for database developer coming from the programming world. I have seen various procedural units which use the for syntax to run updates on base tables. Scenarios like the one bellow. FOR update SQL: for i in (select * from t1) loop update t1 set amount=amount*(1+(Select adjustment from t2 where discount=i.discount … Continue reading “Cursor Loop Updates”
Something went wrong. Please refresh the page and/or try again.