Oracle Indexes

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

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

Richard Foote's picture

Oracle11g: Analyze Table Validate Structure Cascade “FAST” (Slow Burn)

I always take notice when Oracle introduces a new “FAST” option, so it was with some excitement when I first noticed in Oracle 11g Rel 1 there was a new FAST option when running the ANALYZE TABLE CASCADE VALIDATE STRUCTURE command.   This was described in the manuals as introducing a hashing scheme that was significantly [...]

Richard Foote's picture

Oracle11g: Zero Sized Unusable Indexes Part II (Nathan Adler)

In my previous post, I discussed how Oracle from 11g R2 onwards will automatically drop the segment and associated storage from unusable index objects. Mohamend Houri asked in the comments section the excellent question of just how useful this feature will be in real life cases when typically indexes are not left in an unusuable state for a [...]

Richard Foote's picture

Oracle11g: Zero Sized Unusable Indexes (Zeroes)

Following on from my previous discussion on “Create On Demand” segments, Oracle 11g R2 has also introduced storage saving initiatives in relation to useable indexes.  Starting with a simple Oracle 10g example, we create a table and associated index:        If we now make the index unusable:        We notice that [...]

Richard Foote's picture

Oracle11g Creation On Demand Indexes (Invisible Touch)

Prior to Oracle11g Release 2, the default and minimum size of a segment is one extent. So in the below example, where we create a table and five associated indexes:     Each of the segments has been allocated an extent, including each of the indexes.   However, since Oracle11g Release 2, this default behaviour has changed. [...]

Richard Foote's picture

Oracle11g Bitmap-Join IOTs (Us and Them)

With each new database release, nice little improvements and enhanced options continually get added. Since 11g R1, two index related features can finally be used in combination with each other.   To demonstrate, I’m first going to create and populate a so-called “large” Data Warehouse table.      I’ll next create a standard bitmap index [...]

Richard Foote's picture

Oracle11g IGNORE_ROW_ON_DUPKEY_INDEX Hint (Micro Cuts)

An interesting new hint was introduced in Oracle11g which provides an alternative approach when inserting data where duplicate values might be an issue.   To illustrate, I’m going to create a little table with just the 10 rows with a unique ID column containing values 1 – 10 policed by a Unique index:     If [...]

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