Who's online

There are currently 0 users and 29 guests online.

Recent comments

Oracle Indexes

Cost of Virtual Indexes (Little Lies)

I’ve previously discussed Virtual Indexes and how they can be used to do basic “what if” analysis if such an index really existed. However, a recent comment on the OTN forums regarding using them to compare index costs made me think a follow-up post regarding the dangers of Virtual Indexes might be warranted. The big advantage of [...]

Index Rebuild – Does it use the Index or the Table ? (Nothing Touches Me)

A common question that gets asked is does Oracle access the index itself or the parent table during an index rebuild to extract the necessary data for the index ? Thought it might be worth a blog post to discuss. Now if the index is currently in an UNUSABLE state, then Oracle clearly can’t use the existing [...]

IOT Secondary Indexes – The Logical ROWID Guess Component Part I (Lucky)

As discussed previously, an index entry within a Secondary Index on an Index Organized Table (IOT) basically consists of the indexed column(s) and the Logical Rowid, the PK column(s) and a “guess” to the physical block in the IOT containing the corresponding row. Let’s discuss this “guess” component in a bit more detail. When the Secondary [...]

IOT Secondary Indexes: Primary Key Considerations (Beauty And The Beast)

As discussed previously, one of the nice features of an IOT Secondary Index is that it contains the mandatory Primary Key of the IOT, which is always maintained and can be used to access the necessary rows of the IOT regardless of  row movement within the IOT itself. This can also be beneficial if only the PK [...]

Indexed Organized Tables – An Introduction to IOT Secondary Indexes (A Second Face)

Man, its been ages since I had free time to update the blog, what with birthday parties to organise, Roger Water concerts to attend and Radiohead concerts in the planning !! OK, time to take an initial look at Secondary Indexes for Index Organized Tables (IOTs). If the IOT needs to be accessed via the Primary Key (PK) column(s), [...]

Index Organized Tables – PCTTHRESHOLD (The Wedding Song)

I’ve recently returned from a great two-week holiday, firstly at the Australian Open Tennis (what a final !!) and then up at the Gold Coast in not quite so sunny Queensland. Time now to get back to my blog In my previous IOT examples, we had a very large column called Description which we didn’t [...]

Index Organized Tables – Overflow Segment Part II (The Loneliest Guy)

In my previous post on Index Organized Tables (IOT), I introduced the concept of the IOT Overflow Segment, where we can store columns that we may not want to include within the actual IOT index structure. Before we move on, I just wanted to cover off a few additional points that could be a trap for the [...]

Index Organized Tables – Overflow Segment (Shadow Man)

In my previous introductory IOT post, I illustrated how an Index Organized Table (IOT) might be worth consideration if most or all columns in a table were to be included within an index. I’m going to use a slightly different demo this time, replacing one of the columns with a much larger DESCRIPTION column, one which [...]

Index Organized Tables – An Introduction Of Sorts (Pyramid Song)

Thought it was high time that I covered in a little detail the subject of Index Organized Tables (IOTs). When used appropriately, they can be an extremely useful method of storing and accessing data. Hopefully by the end of this series, you’ll have a better understanding of IOTs, their respective strengths and weaknesses and so perhaps [...]

Curious Case Of The Ever Increasing Index Solution (A Big Hurt)

Based on the excellent comments in the Quiz post, we have some clever cookies out there I guess the first thing to point out is that based in the basic scenario provided, the index shouldn’t ordinarily be continually growing in this fashion. Although the index values are monotonically increasing, the deletions are leaving behind fully emptied leaf blocks which [...]