IOT

Issues with OICA/OIC (OPTIMIZER_INDEX_COST_ADJ/OPTIMIZER_INDEX_CACHING) parameters have already been mentioned many times. Recently I’ve noticed one more and I think I didn’t see this case somewhere else on the Internet so I’ll share it.

Here’s a simple table T1 holding 1M rows with the indexed column X having 10 distinct values:

Having mentioned a couple of dangers associated with IOT Secondary Indexes, thought I might discuss a couple of their nicer attributes. In the previous post, we saw how 50-50 index block splits on the ALBUM_SALES_IOT IOT table caused rows to move to new leaf blocks, resulting in a degradation in the PCT_DIRECT_ACCESS value of the associated ALBUM_SALES_IOT_TOTAL_SALES_I secondary [...]

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 [...]

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 [...]

I’m just doing some last minute preparation for the Norwegian User Group Spring Seminar. This is quite a large conference with 5 concurrent streams and a very good line-up of presenters. You can see the agenda here.

What is also a little unusual about this conference is that it is mostly on a boat, or rather a ship. When I was first asked if I would put forward a talk or two I declined – as I am very prone to being sea-sick. Truls Bergersen, who helps organize the event, got in touch with me a few weeks later and said “we are doing a day on land first, do you want to present then?”. Sure! That would be great! So I am, I’ll be doing my talk on Index Organized Tables once more {and then I think I’ll put it to bed for a couple of years}.

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), [...]

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 [...]

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 [...]

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 [...]

I’m really pleased to see that Richard Foote has started a series on Index Organized Tables. You can see his introductory post on the topic here. As ever with Richard, he puts in lots of detail and explanation and I’ve been a fan of his blogging style for a long time.

I’ve got a few posts on the topic left to do myself, maybe this competition will spur me to get on and write them!