Database Tables - Expert Oracle Database Architecture - page 376

The same would be true if you frequently use BETWEEN queries on a primary or unique key. Having the data stored
physically sorted will increase the performance of those queries as well. For example, I maintain a table of stock
quotes in my database. Every day, for hundreds of stocks, I gather together the stock ticker, date, closing price, day's
high, day's low, volume, and other related information. The table looks like this:
EODA@ORA12CR1> create table stocks
2 ( ticker varchar2(10),
3 day date,
4 value number,
5 change number,
6 high number,
7 low number,
8 vol number,
9 primary key(ticker,day)
10 )
11 organization index
12 /
Table created.
I frequently look at one stock at a time for some range of days (e.g., computing a moving average). If I were to use
a heap organized table, the probability of two rows for the stock ticker ORCL existing on the same database block are
almost zero. This is because every night, I insert the records for the day for all of the stocks. This fills up at least one
database block (actually, many of them). Therefore, every day I add a new ORCL record, but it is on a block different
from every other ORCL record already in the table. If I query as follows
Select * from stocks
where ticker = 'ORCL'
and day between sysdate-100 and sysdate;
Oracle would read the index and then perform table access by rowid to get the rest of the row data. Each of the
100 rows I retrieve would be on a different database block due to the way I load the table—each would probably be a
physical I/O. Now consider that I have this same data in an IOT. That same query only needs to read the relevant index
blocks, and it already has all of the data. Not only is the table access removed, but all of the rows for ORCL in a given
range of dates are physically stored near each other as well. Less logical I/O and less physical I/O is incurred.
Now you understand when you might want to use IOTs and how to use them. What you need to understand next
is what the options are with these tables. What are the caveats? The options are very similar to the options for a heap
organized table. Once again, we'll use DBMS_METADATA to show us the details. Let's start with the three basic variations
of the IOT:
EODA@ORA12CR1> create table t1
2 ( x int primary key,
3 y varchar2(25),
4 z date
5 )
6 organization index;
Table created.
EODA@ORA12CR1> create table t2
2 ( x int primary key,
3 y varchar2(25),
4 z date