Home

Oracle Indexing

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…

Oracle Index Organized Table(IOT)

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.…

Oracle Function Based Index

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…

Oracle Bitmap 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…

Buffer Busy Waits – Reverse Key Index – Demo

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 (…

Oracle Reverse Index

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,…

Cursor Loop Updates

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…

Loading…

Something went wrong. Please refresh the page and/or try again.