Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
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.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.