Top 60 Oracle Blogs

Recent comments

Oracle Indexes

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

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

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

Oracle11g: New Locking Modes When Policing FK Constraints (A Wolf at the Door)

As I’ve been focusing mainly with Oracle 11g at work these days, thought I might look at a number of Oracle 11g related topics in the coming weeks.   To start with, there’s been a subtle but potentially significant change introduced in Oracle 11g (since with regard to the manner in which locks are [...]

Index Block Dumps: Final Demo (Come Together)

The intent of this blog piece is just to bring together the whole discussion of block dumps and how we can use block dumps to demonstrate Oracle behaviour. First, let’s start with a fresh little demo, creating an index on a NAME column with 500 entries (note this specific demo uses an database running [...]

Index Block Dump: Index Only Section Part II (Station To Station)

Finally, we look at the last portion of the index block dump which refers to the actual 3 index entries in our demo that currently reside within the index leaf block we dumped previously. row#0[8021] flag: ——, lock: 0, len=15 col 0; len 5; (5):  42 4f 57 49 45 col 1; len 6; (6):  02 01 48 8a [...]

Index Block Dump: Index Only Section Part I (TVC 15)

Having already covered general block header details relevant to several different types of Oracle blocks (Block Dumps Part I and Part II), the next part of the block dump is relevant only to index blocks. Below is a dump of the index only section of an index leaf block dump: Leaf block dump =============== header [...]

Index Block Dump: Block Header Part II and Read Consistency (I Can’t Read)

OK, let’s look at the next portion of the index block dump.   Following the hex dump of the block (as we ended Part I of the series) is the second part of the block header (see below):    Block header dump:  0x0201490a  Object id on Block? Y  seg/obj: 0x1c205  csc: 0x00.2d11214  itc: 2  flg: -  [...]

Index Block Dump: Block Header Part I (Editions Of You)

I’ve previously looked at how to generate an Oracle block dump, time to now go into a little more detail. As I mentioned, a block dump is a formatted representation of the actual contents of an Oracle block. Producing strategic block dumps can be an extremely useful method of determining what might be going on in Oracle under the [...]

Bitmap Index Degradation Since 10g (Fix You)

As discussed in my earlier post on Bitmap Index Degradation After DML Prior To 10g, Oracle wasn’t particularly efficient in the manner it maintained Bitmap Indexes after DML operations. During insert operations, if an existing Bitmap index entry didn’t cover the rowid range of a new row to be inserted, Oracle would create a new Bitmap index entry with a [...]