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.
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 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 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 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