Database Tables - Expert Oracle Database Architecture - page 369

COMPRESS/NOCOMPRESS : Both ASSM and MSSM. Enables or disables compression of table
data during either direct-path operations or during conventional path (“normal,” if you will)
operations such as INSERT . Prior to Oracle9 i Release 2, this option was not available. Starting
with Oracle9 i Release 2 through Oracle 10 g Release 2, the option was COMPRESS or NOCOMPRESS
to either use or not use table compression during direct-path operations only. In those
releases, only direct-path operations such as CREATE TABLE AS SELECT , INSERT
/*+ APPEND */ , ALTER TABLE T MOVE , and SQL*Loader direct-path loads could take
advantage of compression. Starting with Oracle 11 g Release 1 and above, the options
are NOLOGGING, COMPRESS FOR OLTP , and COMPRESS BASIC. NOLOGGING disables any
compression, COMPRESS FOR OLTP enables compression for all operations (direct or
conventional path), and COMPRESS BASIC enables compression for direct-path operations only.
Starting with Oracle 12 c Release 1, these compression options are now specified syntactically
as ROW STORE COMPRESS BASIC (enables compression during direct-path operations), and ROW
STORE COMPRESS ADVANCED (enables compression for all operations).

•

■ lOb data that is stored out of line in the lOb segment does not make use of the PCTFREE / PCTUSED
parameters set for the table. these lOb blocks are managed differently: they are always filled to capacity and returned to
the FREELIST only when completely empty.

Note

These are the parameters you want to pay particularly close attention to. With the introduction of locally
managed tablespaces, which are highly recommended, I find that the rest of the storage parameters (such as
PCTINCREASE , NEXT , and so on) are simply not relevant anymore.

Index Organized Tables
Index organized tables ( IOTs ) are quite simply tables stored in an index structure. Whereas a table stored in a heap
is unorganized (i.e., data goes wherever there is available space), data in an IOT is stored and sorted by primary
key. IOTs behave just like “regular” tables do as far as your application is concerned; you use SQL to access them as
normal. They are especially useful for information retrieval, spatial, and OLAP applications.
What is the point of an IOT? You might ask the converse, actually: what is the point of a heap organized table?
Since all tables in a relational database are supposed to have a primary key anyway, isn't a heap organized table just
a waste of space? We have to make room for both the table and the index on the primary key of the table when using
a heap organized table. With an IOT, the space overhead of the primary key index is removed, as the index is the data,
and the data is the index. The fact is that an index is a complex data structure that requires a lot of work to manage and
maintain, and the maintenance requirements increase as the width of the row to store increases. A heap, on the other
hand, is trivial to manage by comparison. There are efficiencies in a heap organized table over an IOT. That said, IOTs
have some definite advantages over their heap counterparts. For example, I once built an inverted list index on some
textual data (this predated the introduction of interMedia and related technologies). I had a table full of documents,
and I would parse the documents and find words within them. My table looked like this:

create table keywords
( word varchar2(50),
position int,
doc_id int,
primary key(word,position,doc_id)
);