CIS 421b: Database Administration I

Chapter 8: Advanced Table Management

Objectives:

 

This chapter continues the discussion of tables. The objectives important to this chapter are:

1. Creating tables with LOBs and tables that are index organized
2. Understanding table management
3. Using data dictionary views with tables

Concepts:

LOBs fall come in the four types listed in the last chapter:

• BLOB - Binary Locator Objects
• CLOB - Character Locator Objects
• NCLOB - Unicode Locator Objects
• BFILE - pointers to binary file

A table that contains an LOB can actually contain it or not, which divides the type into two groups. The large data object that the LOB points to can be stored in the table (Internal LOB), or it can be stored externally (External LOB). The only type in the external group is BFILE. The text makes a distinction that internal LOBs use copy semantics. This means that when you move one from one table or row to another, you move the entire object that it points to. Heavy freight. When you move an external LOB, you are only moving the pointer. The text refers to this as reference semantics.

Internal LOBs can be stored inside or outside the rows that use them. Inside storage is also called inline storage. Outside storage is also called out of line storage. This is done automatically by Oracle 9i, storing objects larger than 4KB out of line. In both of these cases, the LOB is still considered an Internal LOB.

CHUNK size is a parameter that can be set for LOBs. It tells the system how much data to handle at a time from the LOB. It must be a multiple of the data block size for your database. The default value is the same as the data block size and the maximum is 32 KB.

This chapter explains that an index organized table is actually stored with its records sorted by the primary key of the table. You may recall that the definition of a relation states that the order of rows is unimportant. This is still correct, but storing the table already sorted gives your system faster performance. A table that is not sorted this way is called a heap-organized table. It will contain index blocks that are separate from the data blocks. In this case, the index blocks must be searched for a record' s ROWID, then the record is found in the data block. With an index organized table, both blocks are combined, so the searches are faster.

The text lists four types of changes that can be made to the structure of a table after it is already in use:

• storage settings - as listed earlier in the text
• column order - columns can be moved within the table, but only by a lengthy process
• drop or mark a column as unavailable - use the ALTER TABLE command with the clause DROP columnname to drop a column; use the SET UNUSED columnname clause to make the column unavailable
• truncate or drop a table - dropping a table has been covered; truncating the table means to drop only the rows, but not the structure of the table. Use the TRUNCATE TABLE tablename command.

Oracle tables can be analyzed and optimized. Analysis can be done with the ANALYZE TABLE tablename command, or with the DBMS_STATS utility. Optimizing can be done with the rule-based or cost-based Optimizer. The cost-based Optimizer is the new, preferred utility.