Previous | Table of Contents | Next

Page 85

new extents is the primary concern in database space management. See Chapter 21 for more information.

Extent information is stored in the DBA_EXTENTS view.

TablespacesA tablespace is a logical structure used to group similarly accessed data. Each tablespace is made up of one or more datafiles. All database objects must specify a tablespace where they're to be created. The data that makes up the objects will then be stored in the datafiles allocated to the specified tablespace.

Tablespaces are used to separate the I/O involved in data access. For example, one tablespace can be created to hold data objects, and another created to hold index objects. By allocating datafiles to these tablespaces that reside on different physical disks, you ensure that access to the index data will not interfere with access to the data the index points to. See Chapter 32, "Tuning I/O," for more information.

The tablespace also plays an important part in database backup and recovery. Because a logical tablespace maps directly to one or more physical datafiles, backing up and recovering data is generally done at the tablespace (datafile) level. (The exception to this, of course, is if the backup or recovery operation is applied to the entire database.)

You can view tablespace information in DBA_TABLESPACES.

Understanding Database Segments

Database segments are the user-created objects stored in the database. These include the tables, indexes, rollback segments, table clusters, and hash clusters that make up the section of the database we use to perform our work.

Tables

Tables are the database segments that hold your data. Each table is composed of one or more columns, each of which is assigned a name and data type. The data type of each column defines the type and precision of data to be stored in the table. Valid Oracle table data types are listed in Table 6.3.

Table 6.3 Valid Oracle Table Data Types


Data Type Description Max Size
CHAR Fixed-length character field, padded with trailing blanks 255 bytes
VARCHAR Variable-length character field 2KB (4KB *)
VARCHAR2 Variable-length character field 2KB (4KB *)
LONG Variable-length character data 2GB

Page 86

Table 6.3 Continued


Data Type Description Max Size
NUMBER Variable-length numeric data 1¥10-130 to 9.99¥10125
DATE Fixed-length date and time field N/A
RAW Variable-length raw binary data 255 bytes
LONG RAW Variable-length raw binary data 2GB
ROWID Row ID variable type N/A
* NCHAR National language fixed-length character field, with trailing blanks 255 bytes
* NVARCHAR2 National language variable-length character field 2KB
* CLOB Variable-length single-byte character data 4GB
* NCLOB Single- or multi-byte character data 4GB
* BLOB Binary data field 4GB
* BFILE Pointer to binary data stored in an external file N/A
* Oracle8 data type

NOTE
Oracle recommends that all variable-length character fields be defined as VARCHAR2, rather than VARCHAR, to guard against ANSI specification changes. Oracle guarantees VARCHAR2 will never change functionality in a way that will require modifications to applications to make them upward compatible. Because VARCHAR functionality is mandated by ANSI standards boards, Oracle cannot guarantee that the functionality will not drastically change in new versions.

DBA_TABLES and DBA_TAB_COLUMNS contain information on the tables in the database.

Indexes

Indexes are data segments created to speed access to specific table data. An index holds the value for one or more columns of a table and the ROWID for the corresponding column values. When the Oracle server needs to find a specific row in a table, it looks up the ROWID in the index and then pulls the data directly from the table.

There are several types of indexes available in the Oracle RDBMS. By far, the most common index type is the B*-Tree index. This is the index type used when a standard CREATE INDEX statement is executed. A B*-Tree index is a variation on the standard search tree algorithm, wherein by traversing the index tree, you are guaranteed to find any leaf node in the same number of tree traversals. Each leaf node points to the next leaf node and the previous one,

Page 87

allowing fast index traversals for index range scans and the like. The B*-Tree index is guaranteed to stay balanced, and three-fourths of each node is kept empty to provide space for updates.

An in-depth explanation of the architecture of the B*-Tree index is beyond the scope of this book. However, extensive information on the subject can be found in most modern database textbooks, such as Elmasri and Navathe's Fundamentals of Database Systems.

A cluster index is the index on the column shared by tables in the cluster. Clustered tables are explained later in this section. Unlike convention indexes, cluster indexes only store the index key value once in the index, no matter how many times the index key is repeated in the table.
A cluster index must be created on a cluster before any data manipulation language (DML) operations can be performed against the cluster.

The newest index type is the bitmap index. In a bitmap index, a bitmap is created from the column values in the indexed table and stored in the index, rather than from the actual column values. To put it another way, the index holds a bitmap for each row in the key that contains one bit for each row in the table. The bit is 1 if the value is contained within the row and 0 if it is not. In columns with low cardinality, bitmap indexes can be much smaller and more efficient than traditional B*-Tree indexes. Table 6.4 shows a sample bitmap on a car table. The bitmap key is the color column of the table.

Table 6.4 Bitmap Index on the Car Table


Car Color Bitmap
Red 0 0 0 1 0 0 1 0 0 0 1 0 0 0 1 0 0 0 0 1
Green 1 0 0 0 0 0 0 1 0 1 0 0 0 1 0 0 1 0 1 0
Silver 0 0 1 0 0 0 0 0 1 0 0 0 1 0 0 1 0 0 0 0
White 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0
Black 0 1 0 0 0 1 0 0 0 0 0 1 0 0 0 0 0 0 0 0

In this example, there are 20 rows in the table. The bitmap will be on (1) where the row in the car table matches the color. So, rows 4, 7, 11, 15, and 20 are red cars, while rows 2, 6, and 12 are black. The structure of the bitmap index creates much smaller indexes than a traditional B*-Tree index but, as discussed, is suited only for certain types of data.

DBA_INDEXES and DBA_IND_COLUMNS contain information on all of the indexes in the database.

Rollback Segments

Rollback segments are the database objects that store the "before images," or original data blocks that are changed in database transactions. They are used to provide a read-consistent view of data that has been changed but not yet committed. When a data change is made, the before image is copied into the rollback segment, and the change is made to the data blocks in

Previous | Table of Contents | Next