This chapter discusses tables in terms of creation and storage. The objectives important to this chapter are:
The chapter begins by asking you to run a script to create objects needed for the exercises in this chapter. It continues with an impenetrable discussion of several types of tables that can be created in an Oracle database:
We are reminded of the hierarchy of tablespace structures. Data blocks exist in an extent. One or more extents exist in a segment. Each schema object is represented by a segment. Many segments may exist inside a tablespace.
The chapter continues with a discussion of new storage parameters that can be used when creating a table. The parameters from the last chapter can be used for individual settings for a table, overriding the settings for its tablespace.
Data blocks for tables may contain free space, which is reduced as more data is added to the table. Data blocks also contain directories of the rows in the in this block, and of the table itself. These directories grow as more rows are added. Blocks also contain common and variable headers, which contain information about the block. The directories and headers are considered to be overhead, because they do not contain actual data.
Table rows are typically stored in single data blocks. If a row grows too large for one block, it can be moved to another emptier block, leaving behind a pointer to a new block. This is called a migrated row. If a row is too large for a single block, it is stored in multiple blocks as a chained row. These two kinds of rows slow down the database.
Two parameters affect how many concurrent transactions can access a block: INITRANS and MAXTRANS. They are set in the STORAGE section of a table's creation command. The default value for INITRANS is 1.
You can avoid some of the inefficiencies of migrated and chained rows by increasing block size, and by modifying the values of PCTFREE and PCTUSED. The two parameters must add up to 100 or less. The value of PCTFREE controls what percentage of free space must remain free when rows are inserted. The free space is used for growth of existing rows.
The value of PCTUSED limits what percentage of the block may be in use, and still allow new rows to be added. If it is set to 60, for example, a new row may not be added if the block is at least 60% used.
The text discusses the actual structure of rows of data. A row has two parts: the row header, and the column data. The row header contains a rowid, and a cluster key ID, if the row is part of a cluster. The row header also includes the number of columns in the row. The column data section contains the length of each column, followed by the data in it.
The chapter offers a list of data types commonly used in Oracle.
Some advice is offered about table design. The chapter provides settings that affect the way your database is intended to grow. Some new items appear as well: Oracle table and column names can be from 1 to 30 characters long. When creating a table, these names are assumed to be in all capital letters, unless enclosed in quotes, which forces the capitalization actually used. Quoted names can begin with a space, unquoted names must begin with a letter.
The text elaborates on temporary table, telling us that they continue to exist after the user who creates them ends the current session, but the data in them does not.
Oracle 8 added the possibility of storing a table in one column of another table. This can be done two ways, with a varray or with a nested table. A varray is preferred when you have only a few rows, or when you plan to examine each row in the table. A nested table is preferred when you have a large table, or a changing table, and you will only access specific rows in it. A varray will contain a list of data, appearing to violate the rule of first normal form. It is stored in the table that holds it. A nested table will contain as many rows as needed, and is stored separately from the table that uses it.
Object tables are described as holding user defined objects. This is an example of extending the kind of objects that can be created in your database.
Very large tables become slower to search. To improve performance, Oracle allows you to break the table into pieces called partitions. To make best use of partitions, you should break up the table based on an aspect that it is often searched on. Oracle offers five ways to partition a table: