Database Reference
In-Depth Information
Summary
Hopefully, after reading this chapter you have come to the conclusion that not all tables are created equal. Oracle
provides a rich variety of table types that you can exploit. In this chapter, we have covered many of the salient aspects
of tables in general and explored the many different table types Oracle provides for us to use.
We began by looking at some terminology and storage parameters associated with tables. We looked at the
usefulness of
FREELIST
s in a multiuser environment where a table is frequently inserted/updated by many people
simultaneously, and how the use of ASSM tablespaces could make it so we don't even have to think about that. We
investigated the meaning of
PCTFREE
and
PCTUSED
, and we developed some guidelines for setting them correctly.
Then we got into the different types of tables, starting with the common heap. The heap organized table is by far
the most commonly used table in most Oracle applications, and it is the default table type. We moved on to examine
index organized tables, which provide us with the ability to store our table data in an index structure instead of a heap
table. We saw how these are applicable for various uses, such as lookup tables and inverted lists, where a heap table
would just be a redundant copy of the data. Later, we saw how IOTs can really be useful when mixed with other table
types, specifically the nested table type.
We looked at cluster objects, of which Oracle has three kinds: index, hash, and sorted hash. The goals of the
cluster are twofold:
•
To give us the ability to store data from many tables together on the same database block(s).
•
To give us the ability to force like data to be stored physically together based on some cluster
key. In this fashion, all of the data for department
10
(from many tables) may be stored
together.
These features allow us to access related data very quickly with minimal physical I/O. We observed the main
differences between index clusters and hash clusters, and we discussed when each would (and would not) be
appropriate.
Next, we covered nested tables. We reviewed the syntax, semantics, and usage of nested tables. We saw how they
are, in fact, a system-generated and maintained parent/child pair of tables, and we discovered how Oracle physically
does this for us. We looked at using different table types for nested tables, which by default use a heap-based table. We
found that there will probably never be a reason not to use an IOT instead of a heap table for nested tables.
Then we looked into the ins and outs of temporary tables, including how to create them, where they get their
storage from, and the fact that they introduce no concurrency-related issues at runtime. We explored the differences
between session-level and transaction-level temporary tables, and we discussed the appropriate method for using
temporary tables in an Oracle database.
This chapter finished with a look into the workings of object tables. As with nested tables, we discovered there
is a lot going on under the covers with object tables in Oracle. We discussed how object views on top of relational
tables can give us the functionality of an object table, while at the same time giving us easy access to the underlying
relational data.
