Pro Oracle Database 11g Administration (32 page)

SQL> alter table inv shrink space compact;

157

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Here’s some sample output:

ROWID EMP_ID

------------------ ----------

AAAFWXAAFAAAAlWAAA 1

The ROWID pseudo-column value isn’t physically stored in the database. Oracle calculates its value when you query it. The ROWID contents are displayed as base 64 values that can contain the characters A–Z, a–

z, 0–9, +, and /. You can translate the ROWID value into meaningful information via the DMBS_ROWID

package. For example, to display the relative file number in which a row is stored, issue this statement: SQL> select dbms_rowid.rowid_relative_fno(rowid), emp_id from emp; Here’s some sample output:

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) EMP_ID

------------------------------------ ----------

5 1

You can use the ROWID value in the SELECT and WHERE clauses of a SQL statement. In most cases, the ROWID uniquely identifies a row. However, it’s possible to have rows in different tables that are stored in the same cluster and so contain rows with the same ROWID.

Creating a Temporary Table

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a table that stores data only provisionally.

You can specify that the temporary table retain the data for a session or until a transaction commits. Use ON COMMIT PRESERVE ROWS to specify that the data be deleted at the end of the user’s session. In this example, the rows will be retained until the user either explicitly deletes the data or terminates the session:

create global temporary table today_regs

on commit preserve rows

as select * from f_registrations

where create_dtt > sysdate - 1;

Specify ON COMMIT DELETE ROWS to indicate that the data should be deleted at the end of the transaction. The following example creates a temporary table named TEMP_OUTPUT and specifies that records should be deleted at the end of each committed transaction:

create global temporary table temp_output(

temp_row varchar2(30))

on commit delete rows;

■
Note
If you don’t specify a commit method for a global temporary table, then the default is ON COMMIT DELETE

ROWS.

159

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Creating an Index-Organized Table

Index-organized tables (IOTs) are efficient objects when the table data is typically accessed through querying on the primary key. Use the ORGANIZATION INDEX clause to create an IOT: create table prod_sku

(prod_sku_id number,

sku varchar2(256),

create_dtt timestamp(5),

constraint prod_sku_pk primary key(prod_sku_id)

)

organization index

including sku

pctthreshold 30

tablespace inv_mgmt_data

overflow

tablespace mts;

An IOT stores the entire contents of the table’s row in a B-tree index structure. IOTs provide fast access for queries that have exact matches and/or range searches on the primary key.

All columns specified up to and including the column specified in the INCLUDING clause are stored in the same block as the PROD_SKU_ID primary-key column. In other words, the INCLUDING clause specifies the last column to keep in the table segment. Columns listed after the column specified in the INCLUDING clause are stored in the overflow data segment. In the previous example, the CREATE_DTT

column is stored in the overflow segment.

PCTTHRESHOLD specifies the percentage of space reserved in the index block for the IOT row. This value can be from 1 to 50, and defaults to 50 if no value is specified. There must be enough space in the index block to store the primary key.

The OVERFLOW clause details which tablespace should be used to store overflow data segments.

Notice that DBA/ALL/USER_TABLES includes an entry for the table name used when creating an IOT.

Additionally, DBA/ALL/USER_INDEXES contains a record with the name of the primary-key constraint specified. The INDEX_TYPE column contains a value of IOT - TOP for IOTs: SQL> select index_name,table_name,index_type from user_indexes;

Managing Constraints

The next several sections in this chapter deal with constraints. Constraints provide a mechanism to ensure that data conforms to certain business rules. You must be aware of what types of constraints are available and when it’s appropriate to use them. Oracle provides several types of constraints:

• Primary key

• Unique key

• Foreign key

• Check

• NOT NULL

Implementing and managing these constraints are discussed in the next several subsections.

161

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Creating Primary-Key Constraints

When you implement a database, most tables you create require a primary-key constraint to guarantee that every record in the table can be uniquely identified. There are multiple techniques for adding a primary-key constraint to a table. The first example creates the primary key inline with the column definition:

create table dept(

dept_id number primary key

,dept_desc varchar2(30));

If you select the CONSTRAINT_NAME from USER_CONSTRAINTS, notice that Oracle generates a cryptic name for the constraint (something like SYS_C003682). Use the following syntax to explicitly give a name to a primary-key constraint:

create table dept(

dept_id number constraint dept_pk primary key using index tablespace users, dept_desc varchar2(30));

■
Note
When you create a primary-key constraint, Oracle also creates a unique index with the same name as the constraint.

You can also specify the primary-key constraint definition after the columns have been defined. The advantage of doing this is that you can define the constraint on multiple columns. The next example creates the primary key when the table is created, but not inline with the column definition: create table dept(

dept_id number,

dept_desc varchar2(30),

constraint dept_pk primary key (dept_id)

using index tablespace prod_index);

If the table has already been created and you want to add a primary-key constraint, use the ALTER

TABLE statement. This example places a primary-key constraint on the DEPT_ID column of the DEPT

table:

alter table dept

add constraint dept_pk primary key (dept_id)

using index tablespace users;

When a primary-key constraint is enabled, Oracle automatically creates a unique index associated with the primary-key constraint. Some DBAs prefer to first create a non-unique index on the primary-key column and then define the primary-key constraint:

SQL> create index dept_pk on dept(dept_id);

SQL> alter table dept add constraint dept_pk primary key (dept_id); The advantage of this approach is that you can drop or disable the primary-key constraint independently of the index. When you’re working with large data sets, you may want that sort of flexibility. If you don’t create the index before creating the primary-key constraint, then whenever you drop or disable the primary-key constraint, the index is automatically dropped.

162

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Confused about which method to use to create a primary key? All of the methods are valid and have their merits. Table 7–4 summarizes the primary-key and unique-key constraint creation methods. I’ve used all these methods to create primary-key constraints. Usually, I use the ALTER TABLE statement that adds the constraint after the table has been created.

Table 7–4.
Primary-Key and Unique-Key Constraint Creation Methods
Constraint Creation Method Advantages

Disadvantages

Inline, no name

Very simple

Oracle-generated name

makes troubleshooting

harder; less control over

storage attributes; only

applied to a single column

Inline, with name

Simple; user-defined name makes

Requires more thought than

troubleshooting easier

inline without name

Inline, with name and tablespace

User-defined name and

Less simple

definition

tablespace; makes troubleshooting

easier

After column definition (out of

User-defined name and

Less simple

line)

tablespace; can operate on

multiple columns

ALTER TABLE add just constraint

Lets you manage constraints in

More complicated

separate statements (and files)

from table creation scripts; can

operate on multiple columns

CREATE INDEX, ALTER TABLE add

Separates the index and constraint

Most complicated, more to

constraint

so you can drop/disable

maintain, more moving parts

constraints without affecting the

index; can operate on multiple

columns

Enforcing Unique Key Values

In addition to creating a primary-key constraint, you should also create unique constraints on any combination of columns that should always be unique within a table. For example, for the primary key for a table, it’s common to use a numeric key (sometimes called a
surrogate key
) that is populated via a sequence. In addition to the surrogate primary key, sometimes users have column(s) that the business uses to uniquely identify a record (also called a
logical key
). Using both a surrogate key and logical key

• Lets you efficiently join parent and child tables on a single numeric column

• Allows updates to logical-key columns without changing the surrogate key A unique key guarantees uniqueness on the defined column(s) within a table. There are some subtle differences between primary-key and unique-key constraints. For example, you can define only one 163

CHAPTER 7 ■ TABLES AND CONSTRAINTS

primary key per table, but there can be several unique keys. Also, a primary key doesn’t allow a NULL

value in any of its columns, whereas a unique key allows NULL values.

As with the primary-key constraint, you can use several methods to create a unique column constraint. This method uses the UNIQUE keyword inline with the column: create table dept(

dept_id number

,dept_desc varchar2(30) unique);

If you want to explicitly name the constraint, use the CONSTRAINT keyword: create table dept(

dept_id number

,dept_desc varchar2(30) constraint dept_desc_uk1 unique);

As with primary keys, Oracle automatically creates an index associated with the unique-key constraint. You can specify the tablespace information inline to be used for the associated unique index: create table dept(

dept_id number

,dept_desc varchar2(30) constraint dept_desc_uk1

unique using index tablespace prod_index);

You can also alter a table to include a unique constraint:

SQL> alter table dept add constraint dept_desc_uk1 unique (dept_desc); And you can create an index on the columns of interest before you define a unique-key constraint: SQL> create index dept_desc_uk1 on dept(dept_desc);

SQL> alter table dept add constraint dept_desc_uk1 unique(dept_desc); This can be helpful when you’re working with large data sets and you want to be able to disable or drop the unique constraint without dropping the associated index.

■
Note
See Table 7–4 for a description of the advantages and disadvantages of the various unique-key and primary-key constraint creation methods.

Creating Foreign-key Constraints

Foreign-key constraints are used to ensure that a column value is contained within a defined list of values. Using a foreign-key constraint is an efficient way of enforcing that data must be a predefined value before an insert or update is allowed. This technique works well for the following scenarios:

• The list of values contains many entries.

• Other information about the lookup value needs to be stored.

• It’s easy to select, insert, update, or delete values via SQL.