Re: Index Ordered Table ordering

Ed Prochak wrote:

> The O doesn't stand for Ordering. I understood ORGANIZATION INDEX to
> mean that the data is small enough to fit as part of the index. IOW,
> when you query the table, the data is fetched from the index,
> eliminating the need to do a second disc read.
>
> Daniel, can you correct me?

You are correct except that in an IOT the table is the index. Rather than the table being a heap with no row organization the rows of data in the table are ordered as they would be in a primary key index.

The advantage, where an IOT makes sense, is that you never have to read the index, find a rowid, then read the table block. You also save the overhead of maintaining both table and index. The disadvantage is that inserts into the middle of things can be quite costly.

Generally speaking you want to think IOT when the primary key consists of almost every column in the table. An example being a table that stores labor hours like this:

CREATE TABLE emp_hours (
employee_id NUMBER(10) NOT NULL,
work_date DATE NOT NULL,

project_no  NUMBER(10) NOT NULL,
task_id     NUMBER(5) NOT NULL,
work_hours  NUMBER(4,2) NOT NULL);

As it is possible for the same employee to work on multiple projects and tasks on the same day every column except work_hours is required to uniquely identify the row. Also ... there will never be an update or a delete and all new rows are appended at the end making it a perfect IOT candidate.

Some people, of course, would go running for a surrogate key but doing so would be a mistake. A surrogate key would allow duplicates, it would require maintaining a PK as well as a unique index, and the key would undoubtedly never be used anywhere else in the app.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)