Search

OakieTags

Who's online

There are currently 0 users and 30 guests online.

Recent comments

Affiliations

Oracle Indexes

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

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

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

BLEVEL 1 => BLEVEL 2 (Teenage Wildlife)

Jonathan Lewis recently wrote a really nice blog piece blevel=1 on the dangers of an index toggling between BLEVEL 1 and BLEVEL 2. I thought it would be useful to demonstrate this issue with a quick demo (Note: this example is on 11.2.0.1, with an 8K block size).   First, create a simple little table with 336,000 rows [...]

Indexing A Column With Just One Distinct Value (All The Madmen)

When one thinks about a column that might benefit from being indexed, one generally considers columns with lots of different values so that the selectivity of the column is such that relatively few rows get selected, making the index appealing to the cost based optimizer.   There are of course many exceptions to this generalisation [...]

Bitmap Indexes & Minimize Records_Per_Block (Little Wonder)

As mentioned in my first post regarding the use of Bitmap Indexes to service Not Equal predicates, an index entry in a Bitmap Index consists of the indexed value and a pair of rowids that determine the range of potential rows spanned by the corresponding bitmap sequence. However, Oracle has no way of determining how many [...]

Bitmap Indexes and Not Equal Part II (Sheep)

An excellent comment/question by mdinh made me realise my demos in Part I might be a little extreme in returning 0 rows and perhaps give the false impression that Not Equal conditions are only considered or applicable if no rows are returned. This is not the case and with the bitmap index now considered with Not [...]

Bitmap Indexes and Not Equal (Holy Holy)

Way back, I previously discussed how the CBO will simply ignore any possible indexes when determining the best execution plan involving a NOT EQUAL(<>) condition, even if an index might in theory provide the most efficient access path. Oracle just assumes that the vast majority of rows are likely to be returned and so doesn’t even [...]

DEL_LF_ROWS Index Rebuild Criteria ? (Codex)

In the same article and book where the erroneous claims regarding the BLKS_GETS_PER_ACCESS were made, we also find the following recommendation:   “Another rebuild condition would be cases where deleted leaf nodes comprise more than 20% of the index nodes“.   This is a very common claim however no matter how often it might be [...]

BLKS_GETS_PER_ACCESS Index Rebuild Criteria ? (Twisted Logic)

A recent question on the database OTN forum and a previous request by Charles Hooper that I cover some basic indexing concepts for newbie’s who might be confused by “dubious” information out there in internet land has prompted me to discuss the BLKS_GETS_PER_ACCESS metric, available in INDEX_STATS after an analyze validate structure operation.   The OTN [...]