Skip to Main Content
  • Questions
  • Index-organized tables (IOT) and logical rowid

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, jessy.

Asked: January 10, 2022 - 10:48 am UTC

Last updated: January 18, 2022 - 2:47 am UTC

Version: 19.3.0.0.0

Viewed 100+ times

You Asked

hello tom

i wanted to know what is the perfect scenario to implement a IOT(index organized table) on a database design from what i know so far we need to create an IOT when the table is small(both row and column) and almost all of the column in the table are indexed.is there another scenario where IOT can boost the database performance?

also i wanted to know why logical rowid is slower than physical rowid can you explain it pls?


thanks

and Chris said...

If a table has:

* A compound primary key (PK)
* Where you often query the leading column(s) of this PK
* There are relatively few queries filtering/joining on other columns of the table

An IOT is likely a good candidate. To see why some background.

The database can store new rows anywhere there's space in a heap table (the default).

An IOT stores the data in (primary key) index order. New rows must go in the correct location for the index. So rows with consecutive primary key (PK) values will be physically stored near each other. Using heap tables rows with consecutive PK values may be spread throughout the whole table.

This makes IOTs good for cases where you'll access many rows by primary key in the same statement. This typically happens when you have compound PKs.

The classic example is junction/link tables to store many-to-many relationships. Often the only columns in these tables are the PKs from the parent tables and possibly some metadata (e.g. insert date).

For example, to store all the addresses for a customer you might have:

create table customer_addresses (
  customer_id references customers,
  address_id  references addresses,
  insert_date date,
  primary key ( customer_id, address_id )
)


Making this an IOT does a few things:

* Ensures the rows for all a customer's addresses will be next to each other. This can make lookups by customer faster.
* Lookups by customer are also index-only accesses. To get all the columns in a heap table, you'll have to access the table too.
* Reduces storage. If this is a heap table, the database creates the table and the primary key index. But all the table's columns are in the PK! So making this an IOT roughly halves disk usage.

Here's a demo of the difference an IOT could make. I've loaded up a heap table with data and selected all the rows for a given customer:

create table customer_addresses (
  customer_id,
  address_id,
  insert_date,
  primary key ( customer_id, address_id )
) as
with custs as (
  select level id from dual
  connect by level <= 10000
), addrs as (
  select level id from dual
  connect by level <= 10000
)
  select c.id, a.id, sysdate from custs c
  join   addrs a
  on     mod ( c.id, 5 ) - 2 = round ( sin ( a.id ) * 2 )
  order  by dbms_random.value;

set serveroutput off
alter session set statistics_level = all;

set feed only
select * from customer_addresses
where  customer_id = 2;
set feed on

select * 
from   table(dbms_xplan.display_cursor( format => 'IOSTATS LAST'));


The plan for the query is:

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |      1 |        |   1603 |00:00:00.01 |    1626 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER_ADDRESSES |      1 |   1603 |   1603 |00:00:00.01 |    1626 |
|*  2 |   INDEX RANGE SCAN                  | SYS_C0030393       |      1 |   1603 |   1603 |00:00:00.01 |      24 |
--------------------------------------------------------------------------------------------------------------------


Note it reads the PK index and accesses the table. Pay close attention to the "Buffers" column - 1,626 means that many I/O operations.

Run the same demo, but this time make the table an IOT:

create table customer_addresses (
  customer_id,
  address_id,
  insert_date,
  primary key ( customer_id, address_id )
) organization index as
with custs as (
  select level id from dual
  connect by level <= 10000
), addrs as (
  select level id from dual
  connect by level <= 10000
)
  select c.id, a.id, sysdate from custs c
  join   addrs a
  on     mod ( c.id, 5 ) - 2 = round ( sin ( a.id ) * 2 )
  order  by dbms_random.value;


And the plan becomes:

----------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |      1 |        |   1603 |00:00:00.01 |      25 |      7 |
|*  1 |  INDEX RANGE SCAN| SYS_IOT_TOP_164258 |      1 |    709 |   1603 |00:00:00.01 |      25 |      7 |
----------------------------------------------------------------------------------------------------------


Now the database only reads the index (there is no "table"). And it only does 25 I/Os - 65x less work!

IOTs can also be handy for master-detail relationships, particularly when you'll keep adding new rows for a given parent over time.

For example, customer transactions (assuming a customer can't make two transactions at the exact same time)

create table customer_transactions (
  customer_id references customers,
  transaction_datetime timestamp,
  ...
  primary key ( customer_id, transaction_datetime )
) organization index


Showing a customer their recent transaction is likely one of the most common queries against this table.

By making it an IOT, you ensure that all the rows for a customer are near each other => less work to fetch them => faster queries.

So if IOTs are so great, why would you use a heap table?

Firstly there are a few restrictions on these - check the docs for a complete list.

Next, for tables with single-column PKs the advantage is smaller. Unless you regularly run range queries like:

where pk_column between ... and ...


There's less benefit to having consecutive PK values next to each other in the table.

Finally queries on non-PK columns can be slower. This leads on to your next question:

i wanted to know why logical rowid is slower than physical rowid can you explain it pls?

A physical rowid is the exact location of the row on disk. This rarely changes.

A logical rowid is based on the PK values. As the data changes in an IOT, index splits will happen. This means which block an index entry is in is likely to change over time.

Secondary indexes (i.e. any other than the PK) on the IOT store the initial location of the row in the index. If the actual location of the row changes, the database uses the PK values to search the IOT for the row.

So as the data changes, searches using these secondary indexes can be less efficient.

Richard Foote discusses this in more detail

https://richardfoote.wordpress.com/2012/03/19/indexed-organized-tables-an-introduction-to-iot-secondary-indexes-a-second-face/
https://richardfoote.wordpress.com/2012/04/26/iot-secondary-indexes-the-logical-rowid-guess-component-part-i-lucky/

Rating

  (2 ratings)

Comments

Including in IOT ?

jessy wijaya, January 15, 2022 - 11:08 am UTC

what is the purpose of including?
Chris Saxon
January 17, 2022 - 1:52 pm UTC

In an IOT, all the data for each row must fit within an index block (8Kb by default). If rows can be larger than this, you need to create an overflow segment. This stores the columns too large to fit in the index structure.

The database uses the PCTTHRESHOLD to determine which columns to move to the overflow. When you insert a row that will leave the block with a lower percentage of free space than this setting, the database moves it to the overflow segment.

INCLUDING is a manual cut-off column for this overflow. This is the last column the database will try and store in the index block. All following columns go in the overflow segment, regardless of the space available in the block.

This can be useful if you have rarely accessed long string columns at the end of the table. For example notes, descriptions, etc.

including

jessy wijaya, January 17, 2022 - 2:28 pm UTC

i assume we use including for column before LOB or large string data type so the data get stored into the overflow
Connor McDonald
January 18, 2022 - 2:47 am UTC

Most commonly yes, not that might not always be the case.

For example, you might have a lot of small columns, but (say) only the first 5 are most frequently accessed. You might choose to only include those 5 to keep the index nice and dense for performance because you know that columns 6 onwards are rarely accessed.

Although its always a trade off - a lot of people simply don't use an IOT and add columns to the indexes of a normal heap table to get the same benefit (at the cost of slightly more space)

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.