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/