Updating indexes for block

The block size is configurable for any given database but is usually one of 4Kb, 8Kb, 16Kb, or 32Kb.

until I've read the manual from cover to cover and found out everything it can do, every built-in gizmo, and every trashy piece of after-market merchandise that can be plugged into it. I don't mean a deep, spiritual, one-ness with indexes; I mean just a basic understanding of the mechanics of the things.

T., there's no shortage of people who can peel off a half-hour litany on their new Blackberry/IPod/Notepad/Digital Watch within a day of purchase. I worked with Oracle databases for 5 years before I understood indexes - and it's right there in the manual (Concepts manual, for those interested).

This is a Full Table Scan, selecting rows in no particular order, except that they are nearest to hand.

This metaphor works on a couple of levels: you would grab leaves in handfuls, not one by one.

In a large phone book, this reduced list containing one entry per page will still cover many pages, so the process is repeated, creating the next level up in the index, and so on until we are left with a single page: the That's it; 3 blocks to find a specific row in a million row table.

In reality, index blocks often fit 100 or more rows, so b-Trees are typically quite shallow. Always add 1 to include the leaf level; this tells you the number of blocks a unique index scan must read to reach the leaf-block.

A Full Table Scan does the same thing: when a bock is read from disk, Oracle caches the next few blocks with the expectation that it will be asked for them very soon.

Type this in SQL*Plus: Just to shake things up a bit (and to feed an undiagnosed obsessive compulsive disorder), you decide to pick up the leaves in order of size.

A deeper index would be more interesting, but it would take a while to dump. As you insert new rows into the table, new rows are inserted into index leaf blocks.

When a leaf block is full, another insert will cause the block to be split into two blocks, which means an entry for the new block must be added to the parent branch-block. The process propagates back up the tree until the parent of split has space for one more entry, or the root is reached. Staggeringly, this process ensures that every branch will be the same length. Indexes have three main uses: This is an extraordinary tuning technique in OLTP systems like SQL*Forms that return one page of detail at a time to the screen.

Not only should you not bother creating or investigating the existence of an index, you should Oracle is not already using an index.

Tags: , ,