0473-0475

Previous Table of Contents Next

Page 473

Figure 20.1.
A table with five
extents.

TIP

As a general rule of thumb, when a database segment uses more than 10 extents, you should re-create the table with only one extent to improve the performance of full-table, scan-type accesses .

NOTE

Before you create a table, index, or cluster, you should calculate the amount of storage the table will use. If possible, create the table with an initial extent capable of holding the data for it. Doing this minimizes the overhead of allocating additional extents and searching noncontiguous storage areas for data.

Forcing Additional Extents

Generally, you want to calculate the amount of storage that a table or other segment will use and to make sure that the amount of storage is allocated to the object for its initial extent. This ensures that the object has the amount of storage it requires when it is created and that the

Page 474

system overhead of allocating additional extents is reduced. In some cases, however, allocating these extents wastes storage if they are allocated to an object but are not used until some time in the future. Moreover, it might not be possible to allocate the storage required into one initial extent. The Oracle blocks for an extent must be contiguous in one data file for the tablespace in which the object has been created.

Suppose that you have the space required, but it is not in one contiguous storage area. One solution is to modify the storage parameters for the segment when it is created so that more than one extent is allocated when the table is created. Another solution is to allocate storage manually by using the ALTER TABLE statement:

 SQLPLUS> ALTER TABLE karam ALLOCATE EXTENT; 

This statement allocates the next extent. The size of this extent is the same as what it would be if the extent were allocated automatically when more storage was required. That is, the extent's size is larger than the previous extent by a given percentage increase parameter. The default percentage increase parameter is 50 percent.

Deleting the rows of a table does not release the storage allocated to the table. The rows disappear, but the table continues to use the storage allocated. To release the storage, drop or truncate the table. Dropping the table causes all the information about its structure, the database triggers on it, and the constraints for it to be lost. The truncate command is a quick way of removing the rows of a table without modifying its structure. With the truncate SQL statement, you have the option of releasing all but the initial extent. The truncate command is a data definition language (DDL) statement. When it is issued successfully, it cannot be rolled back.

Using the ROWID in Oracle8

The ROWID is an internal physical address for every row in every nonclustered table in the database. It is a unique identifier that is not repeated for any two rows across the whole database. The ROWID is a pseudo-column that never appears in the list of columns when a table is described. It can be selected from any table, but it can never be modified.

The ROWID is allocated for the row when the row is inserted. The ID remains for the row until the row is deleted or until the table is dropped. The syntax for the ROWID follows :

 SQLPLUS> SELECT ROWID FROM karam; 

The ROWID is composed of four components in base64 format. This ID specifies the database object ID, which includes the row, the file the row is in, the block it is in, and the row sequence number within the block.

Page 475

If you know the ROWID for a row, you can use it to access the row. In fact, using the ROWID is the quickest way to get to any row in the database. You can use this code, for example:

 SQLPLUS> SELECT mycol FROM karam WHERE ROWID = `AAAAziAABAAACiwAAA' FOR UPDATE OF mycol; 

This statement uses the ROWID to lock the row. You must always perform a query to access the ROWID before you can use it. This ID is useful when you access the row and later in the program logic if you need to update or lock a row.

Don't assume that the ROWID remains the same across transactions. Another transaction might delete the row after one of your transactions completes.

Oracle8 provides a set of packaged procedures in the DBMS_ROWID package that you can use to manipulate and extract parts of the ROWID. You can extract the object ID by using the DBMS_ROWID.ROWID_OBJECT function, for example, and you can use the object ID returned to interrogate DBA_objects for the name of the object that owns the row.

In addition, you can use the routines provided in DBMS_ROWID to convert the ROWID to or from a hexadecimal format (which is the format used in Oracle7).

Using the Storage Clause

You have learned about the default storage parameters that apply to segments created in the database. Often, though, you want to have more control over the storage parameters and where the storage is allocated, especially in a production database. You can specify a generic storage clause at the end of the CREATE statements when you create the database segments.

Understanding the Storage Clause

You can put a storage clause at the end of the CREATE statement for a database segment. The format follows:

 STORAGE (INITIAL xK           NEXT     xK           MINEXTENTS x           MAXEXTENTS x           PCTINCREASE x). 

The storage clause overrides the default storage parameters that would be used for the database segment. The initial extent parameter specifies the number of bytes, kilobytes, or megabytes of storage to allocate for the first extent of the database segment. The next parameter specifies the amount of storage to allocate for the second extent for the segment. The values for the initial and next extent parameters always are specified in terms of the number of bytes, kilobytes, or megabytes instead of the number of Oracle blocks. This is because the size of an Oracle block varies from platform to platform and even from one database on a machine to another. By specifying the storage in terms of bytes, kilobytes, or megabytes, you ensure that the same amount of storage is always allocated, regardless of the size of the Oracle block.

Previous Table of Contents Next