Oracle Indexes

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

Big Tables, Sorts and Indexes Solution (Right On Mother)

My, what a clever lot we have reading this blog Indeed, most of the work has already been done for me as we already have a couple of excellent demos in the list of comments. The answer is Yes, the CBO will consider using the index and Yes, there may be a number of scenarios when the [...]

Richard Foote's picture

Big Tables, Sorts and Indexes Quiz (Candidate)

Following on from the previous quiz on Descending indexes. Simple scenario. You have a huge table, 10 Million plus rows. You have an index on a column with a NOT NULL constraint but there are various other columns in the table not included in the index. You want to select all columns and all rows [...]

Richard Foote's picture

Descending Indexes Solution (Yellow Submarine)

Answers to the quiz on Descending Indexes and hopefully some useful dangers and tips on using them. The answer to the first question is Yes, a “normal” Ascending Index can be used by the CBO to retrieve data in descending order and hence possibly avoid a sort. The reason being that leaf blocks in the [...]

Richard Foote's picture

Descending Indexes Quiz (Up On The Ladder)

OK, you won’t find the answer to these questions on my blog, so using my search facility won’t be of any help Actually, it’s quite an easy one this, honest If you have a query such as: SELECT * FROM bowie WHERE id BETWEEN 42 and 84 ORDER BY id DESC; 1) Can a default B-Tree index on [...]

Syndicate content