RE: RE: Index-Organized Table experiences

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 28 Apr 2004 11:12:42 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F877E0@irvmbxw02>

Oracle8i Concepts Release 2 (8.1.6) A76965-01=20

10 Schema Objects=20

Secondary Indexes on Index-Organized Tables=20 Secondary index support on index-organized tables provides efficient = access to index-organized table using columns that are not the primary = key nor a prefix of the primary key.=20

Oracle constructs secondary indexes on index-organized tables using = logical row identifiers (logical rowids) that are based on the table's = primary key. A logical rowid optionally includes a physical guess, which = identifies the block location of the row. Oracle can use these guesses = to probe directly into the leaf block of the index-organized table, = bypassing the primary key search. Because rows in index-organized tables = do not have permanent physical addresses, the guesses can become stale = when rows are moved to new blocks.=20

For an ordinary table, access by a secondary index involves a scan of = the secondary index and an additional I/O to fetch the data block = containing the row. For index-organized tables, access by a secondary = index varies, depending on the use and accuracy of physical guesses:=20

Without guesses, access involves two index scans: a secondary index scan = followed by a scan of the primary key index.=20

With accurate guesses, access involves a secondary index scan and an = additional I/O to fetch the data block containing the row.=20

With inaccurate guesses, access involves a secondary index scan and an = I/O to fetch the wrong data block (as indicated by the guess), followed = by a scan of the primary key index.

See Also: "Logical Rowids" =20

-----Original Message-----

Jared Still

Guess?

What does that mean?

An IOT is essentially a b*tree that is treated as a table.

What kind of guessing would be involved?

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

Received on Wed Apr 28 2004 - 13:10:11 CDT