Top 60 Oracle Blogs

Recent comments

Oracle Indexes

Curious Case Of The Ever Increasing Index Quiz (She’ll Drive The Big Car)

I received an email recently that had a nice example of what can potentially go wrong with an index. Let’s first create a simple table with a unique index and populate it with 200,000 rows (following demo run on So far, everything is as expected. With have an index with 200,000 rows that currently has [...]

Index Organized Tables – A Start (Star)

Happy New Year !! As has been mentioned to me on quite a number of occasions, I’ve haven’t really covered the area of Index Organized Tables (IOTs) on this blog. This despite covering the subject of IOTs in quite some detail in my Index Internals Seminar. Which I readily admit is a bit of a shame as [...]

Why Are My Indexes Still Valid Solution (A Second Face)

I’ve been so busy lately, I just haven’t had any spare time to post. For now, the quick answer to the last quiz is that the second table was indeed an Index Organized Table (IOT). One of the nice benefits of an IOT is that when re-organised, unlike a Heap Table, all indexes remain valid, [...]

Why Are My Indexes Still Valid Quiz ? (Move On)

OK, this quiz is a nice easy one, the lads at work got this without too much trouble.    Normally, when you MOVE (re-org) a table, all the associated indexes become Unusable. As below:         So the indexes are now all unusable ..     However, I previously created another table called BOWIE that [...]

Why Is My Index Not Being Used No. 2 Solution (The Narrow Way)

As many have identified, the first thing to point out is that the two queries are not exactly equivalent. The BETWEEN clause is equivalent to a ‘>= and <=’ predicate, whereas the original query only had a ‘> and <’ predicate. The additional equal conditions at each end is significant. The selectivity of the original query is basically costed [...]

Why Is My Index Not Being Used No. 2 Quiz (Quicksand)

I have a table that has 1M rows with dates that span 2000 days, all evenly distributed (so there are 500 rows per day for the mathematically challenged). All stats are 100% accurate and I have an index on the date column.         OK, I now select 1 day’s worth of data: [...]

Why Is My Index Not Being Used Solution (Eclipse)

Well done to everyone that got the correct answer Indeed, the subtle but significant difference between the two demos was that demo one created the table in a tablespace called USER_DATA with manual segment space management (with freelists/freelist groups set to 1), while demo two created the table in a tablespace called USER_DATA1 with automatic segment space management. [...]

Why Is My Index Not Being Used Quiz (Brain Damage)

This one is a little different as it comes in the form of a demo (and about 1 minute to read) so you have to work a little   I create table, index and sequence:     I then create a little procedure that simply adds 100,000 rows to the table:     I then [...]

Rebuilding Indexes and the Clustering Factor Solution (Move On)

Excellent !! This quiz created quite a bit of debate and it was nice to sit back and read some interesting discussions. The Clustering Factor is basically the measurement of how well aligned the data in the underlining table is in relation to the index and is the number of table related I/Os required to read the entire table via a [...]

Rebuilding Indexes and the Clustering Factor Quiz (One Of The Few)

Today’s question has been prompted by various recent comments regarding the Clustering Factor (CF) of an index and how to change the CF requires a reorg of the underlining table. It used to be quite a common myth that if the CF of an index was greater that “X” or based on some nonsensical formula the [...]