Search

OakieTags

Who's online

There are currently 0 users and 39 guests online.

Recent comments

Affiliations

Oracle Indexes

Index Advanced Compression vs. Bitmap Indexes (Candidate)

A good question from Robert Thorneycroft I thought warranted its own post. He asked: “I have a question regarding bitmapped indexes verses index compression. In your previous blog titled ‘So What Is A Good Cardinality Estimate For A Bitmap Index Column ? (Song 2)’ you came to the conclusion that ‘500,000 distinct values in a 1 […]

12.1.0.2 Introduction to Zone Maps Part II (Changes)

In Part I, I discussed how Zone Maps are new index like structures, similar to Exadata Storage Indexes, that enables the “pruning” of disk blocks during accesses of the table by storing the min and max values of selected columns for each “zone” of a table. A Zone being a range of contiguous (8M) blocks. I […]

Index Compression Part VI: 12c Index Advanced Compression Block Dumps (Tumble and Twirl)

Sometimes, a few pictures (or in this case index block dumps) is better than a whole bunch of words :) In my previous post, I introduced the new Advanced Index Compression feature, whereby Oracle automatically determines how to best compress an index. I showed a simple example of an indexed column that had sections of index entries that were […]

Index Compression Part V: 12c Advanced Index Compression (Little Wonder)

I’ve finally managed to find some free time in the evening to write a new blog piece :) This will have to be the record for the longest time between parts in a series, having written Part IV of this Index Compression series way way back in February 2008 !! Here are the links to the previous articles […]

12.1.0.2 Introduction to Attribute Clustering (The Division Bell)

One of the really cool new features introduced in 12.1.0.2 is Attribute Clustering. This new table based attribute allows you to very easily cluster data in close physical proximity based on the content of specific columns. As I’ve discussed many times, indexes love table data that is physically clustered in a similar manner to the index […]

12c Index Like Table Statistics Collection (Wearing The Inside Out)

This change introduced in 12c has caught me out on a number of occasions. If you were to create a new table: And then populate it with a conventional insert: We find there are no statistics associated with the table until we explicitly collect them: But if we were to now create an index on this […]

It was 12 years ago today…

It was exactly 12 years ago today that I first presented my Index Internals – Rebuilding The Truth presentation at a local ACT Oracle User Group event. And so my association with Oracle indexes started. It would be an interesting statistic to know how many people have subsequently read the presentation :) It would no doubt result in […]

Estimate Index Size With Explain Plan (I Can’t Explain)

I discussed recently an updated MOS note that details the needs vs. the implications of rebuilding indexes. Following is a neat little trick if you want to very quickly and cheaply estimate the size of an index if it were to be rebuilt or a new index before you actually create the thing. I meant to blog about this sometime […]

Indexing Foreign Key Constraints With Invisible Indexes (Invisible People)

In my previous post I discussed when deleting rows from parent tables, how Bitmap Indexes based on the FK constraint can prevent the expensive Full Tables Scans (FTS) on the child tables but not the associated exclusive table locks. Last year, I discussed how it was possible in Oracle Database 12c to have multiple indexes on the same column list. […]

Indexing Foreign Key Constraints With Bitmap Indexes (Locked Out)

Franck Pachot made a very valid comment in my previous entry on Indexing Foreign Keys (FK) that the use of a Bitmap Index on the FK columns does not avoid the table locks associated with deleting rows from the parent table. Thought I might discuss why this is the case and why only a B-Tree index does […]