Oracle Indexes

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

Richard Foote's picture

Best Method To Select One Row From Small Table – Solution (Revolution 1)

OK, time for some answers, although of course regular readers of this blog will already know the answer :) When selecting one row from the small table as in the quiz in my previous post, the correct order is as follows: 1) PK access of an Index Organized Table. This option only requires just the 1 consistent [...]

Richard Foote's picture

Best Method To Select One Row From Small Table Quiz (Each Small Candle)

Assume you have a tiny little table with just 42 rows (naturally) that all fit in one table block. Order the following options in order of “efficiency” (most efficient option first) when accessing just one of these rows: 1) Full Table Scan of Heap Table 2) PK access of an Index Organised Table 3) Index access [...]

Richard Foote's picture

METHOD_OPT=> SIZE AUTO Quiz Solution (The Trickster)

I was going to leave it for a few days but there have already been so many comments and discussions on all this, I thought I better write something up. In case anyone was wondering, yes I probably am driving my colleagues at work mad with my “Question of the Day” !! Unfortunately, some might [...]

Richard Foote's picture

METHOD_OPT => SIZE AUTO Quiz (Automatic For The People)

OK, a nice, easy, simple one today. No tricks, honest You have a table with 3 columns and lets say 1M rows. Column 1 is effectively unique, so it has 1M distinct values. Let’s say 1 – 1000000, not that it really matters. Column 2 has 254 distinct values, all evenly distributed so it has [...]

Richard Foote's picture

MIN / MAX Quiz Answer (One Shot)

Not only are my regular blog readers a good deal better looking than the average person, but they’re quite a bit smarter as well As most people have correctly identified, the answer I was after to my previous Min/Max Quiz is that Option 1 is indeed the odd one out, as it’s the only option [...]

Richard Foote's picture

MIN/MAX Quiz (Funtime)

At my work I’ve recently introduced a little “Question of the Day” for my fellow DBAs, hopefully to pass on a few interesting little titbits of information and have a bit of fun along the way. I was going to just write a blog article on the following topic of how Oracle deals with MIN and [...]

To prevent automated spam submissions leave this field empty.
Syndicate content