Top 60 Oracle Blogs

Recent comments

Index Internals

Enable Index To Search For NULLs By Adding Constant to Index List. But Some Constants Better Than Others (Never Let Me Down)

By default, Oracle doesn’t index an entry if all columns within the index are NULL. However, (as I’ve blogged previously), it’s possible to index all possible NULL values by simply adding a constant value to the index column list. Importantly, the CBO knows when a column has all it’s NULL values indexed and can potentially […]

“Hidden” Efficiencies of Non-Partitioned Indexes on Partitioned Tables Part II (Aladdin Sane)

In Part I of this series, I highlighted how a Non-Partitioned Global Index on a Partitioned Table is able to effectively perform “Partition Pruning” by reading only the associated index entries to access just the table blocks of interest from relevant table partitions when the table partitioned keys are specified in an SQL Predicate. Understanding […]

Index Column Order – Impact On Index Branch Blocks Part I (Day-In Day-Out)

I recently replied on Twitter to some comments regarding an excellent blog post by Franck Pachot – Covering indexes in Oracle, and branch size, where I disagreed somewhat with one of the conclusions stated in the post: “ensure that selective columns appear as early as possible (without compromising the index access efficiency of course) in order to […]

Indexing Seminar: Why Small Classes? (A Small Plot Of Land)

Firstly, a huge thank-you to everyone who has enroled in one of my “Indexing Internals and Best Practices” Seminars running soon is Australia. The feedback and overall reaction has been extremely positive and I can’t wait to present all the material I’ve been putting together these past few weeks. There are currently limited places still […]

Why A Brand New Index Might Benefit From An Immediate Coalesce (One Slip)

A recent question on the OTN Forums Reg: Index – Gathering Statistics vs. Rebuild got me thinking on a scenario not unlike the one raised in the question where a newly populated index might immediately benefit from a coalesce. I’ve previously discussed some of the pertinent concepts such as how index rebuilds can make indexes bigger, not smaller […]

Empty Leaf Blocks After Rollback Part II (Editions of You)

In my last post, I discussed how both 1/2 empty and totally empty leaf blocks can be generated by rolling back a bulk update operation. An important point I made within the comments of the previous post is that almost the exact scenario would have taken place had the transaction committed rather than rolled back. A […]

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

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