CIS 421b: Database Administration I

Chapter 7: Basic Table Management

Objectives:

 

This chapter discusses tables in terms of creation and storage. The objectives important to this chapter are:

1. Different types of tables
2. Creating tables
3. Creating special tables: varrays and nested tables
4. Object and partitioned tables

Concepts:

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:

• relational table - a standard table as described in the text so far
• index-organized table - a table whose data is actually sorted according to its index
• object table - each row in this table is one attribute of a particular type of object; it is a template for creating tables that that hold this kind of object
• temporary table - typically used for tables that are needed by one user in one session
• external table - tables whose data are not actually in the database; the table points to a file outside the database
• nested table - a table that holds data from a single column of another table
• XML table - one column in this table holds XML data to allow a web page or web application to access this data
• cluster - a storage structure that places the data of multiple tables in one segment

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.

• Character - 7 types
  • CHAR is for fields up to 2000 bytes. If data shorter than the field width is entered, spaces are used to pad the field.
  • VARCHAR is an old type replaced by VARCHAR2. Both can hold up to 4000 bytes. VARCHAR2 adjusts the the actual data size.
  • NCHAR and NVARCHAR2 are variations that support Unicode characters. NCHAR fields can be up to 2000 bytes, NVARCHAR2 fields can be up to 4000 bytes.
  • LONG - an old data type, supports up to 2 GB. We are encouraged to use LOB or CLOB instead.
• Number -one type is given: NUMBER
• DATE - 6 types
  • DATE - stores a 4 digit year, month, day, and time in hours, minutes, and seconds.
  • TIMESTAMP - includes the above data and adds fractions of seconds.
  • TIMESTAMP WITH TIME ZONE - adds the time zone of the event
  • TIMESTAMP WITH LOCAL ZONE - the time zone of the local server is used, times from other zones are adjusted to the local zone time
  • INTERVAL YEAR TO MONTH - holds time intervals expressed in years and months
  • INTERVAL DAY TO SECOND - holds time intervals expressed in days and seconds
• LOB - 4 types
  • BLOB - Binary Locator Objects
  • CLOB - Character Locator Objects
  • NCLOB - Unicode Locator Objects
  • BFILE - pointers to binary files
• RAW 2 types
  • RAW - up to 4000 characters of any type not handled by Oracle
  • LONG RAW - up to 2 GB of binary data. We are encouraged to use CLOB or BFILE.
• ROWID - 2 types
  • ROWID - standard data type for rowid data in Oracle 7 (and earlier versions)
  • UROWID - for rowid data in versions after Oracle 7

   

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:

• range - division is based on values in a column (or group of columns)
• hash - division is done evenly across a specific number of partitions
• list - rows in that table are assigned to a partition based on a list of values
• composite range-hash - like range division, but each range is given a specific number of subpartitions
• composite range-list - range partitioning is used, and within ranges you create subpartitions based on a list of values