All language subtitles for [SubtitleTools.com] Index Organized Tables - Learning Oracle 12c [Video]

In this lesson, we'll be looking
at index-organized tables.

So typical tables--
typical relational tables--

created with the
CREATE TABLE command

are what we refer to
as heap organized.

So that means if I
insert a row into a table

and then I insert a second row
into the table, and a third,

those rows are actually
stored in the order

that we put them in the table.

That's actually how they're
stored in the database blocks.

An index-organized
table, or IOT,

is actually structured
in an index.

So the table is not
structured heap organized.

It is actually
structured in an index.

So essentially, the table and
the index are built together.

So what's unique about an
IOT is that it's dynamic.

Because it's
structured in an index,

the rows can be moved to
preserve that structure.

So index-organized tables can
be good in certain situations,

but because of the way
that they're built,

they're used in
specific situations.

So they're good
for small tables,

and particularly for tables
that function as lookup tables--

so maybe at a table that has
a relatively small number

of rows, something like--

maybe as an example--

a table that has cities and
zip codes and counties, maybe,

for lookups.

You already have the table,
and if you can load it

in an ordered way that
it's loaded in the same way

that it's structured, it can be
fairly quick to load that data,

and then that can be used
as an index-organized table.

It's also good, with those
small tables, where there's

an exact match on the value.

So we're not using
a lot of functions.

We really are using an
index-organized table, maybe,

to join to another table,
or as a quick lookup table.

And it can also have the
benefit of reducing storage

requirements, because you're not
storing a table and an index.

You're actually
storing them together.

So there is some degree of
savings in terms of storage.

But you wouldn't want a
really, really large table

in an index-organized table, and
you wouldn't want a table to be

defined as an IOT if you're
going to be changing the values

a lot, because that operation--

while it is dynamic--

the rules of an IOT
are that it must store

the table in that structure.

It must be structured
in an index.

It will have to move those
rows if you change them,

and so that can be a
pretty expensive operation.

But if that's not a
problem, then an IOT

can be really useful,
because it's not

a matter of scanning an
index, finding the row ID,

and then returning the row.

It's finding the value
and returning the row.

So let's look at
creating an IOT.

And we'll create table
state_lookup_iot.

You have an area code column,
state name, city name.

One of the key
things about an IOT

is that we have to define
a primary key for it.

And that's going to be what
defines its organization

We say primary key area code.

So the upper structure,
here, is fairly simple,

and this is just
defining a primary key.

What makes an IOT unique is the
next part of the statement--

organization index, and then
we can put it in a table space

if we'd like.

So now, then, we can load
the state_lookup_iot table,

and when we load
the data, it will

be structured in the form
of an index on area code.