Character

Use a character data type to store characters and string data. The following character data types are available in Oracle:

• VARCHAR2
• CHAR
• NVARCHAR2 and NCHAR

create table d(d varchar2(30));
insert into d values ('a'),
select dump(d) from d;

DUMP(D)
----------------
Typ=1 Len=1

varchar2(30 byte)

varchar2(30 char)

create table d(d char(30));
insert into d values ('a'),
select dump(d) from d;

DUMP(D)
----------------
Typ=96 Len=30

Numeric

Use a numeric data typeto store data that you’ll potentially need to use with mathematic functions, such as SUM, AVG, MAX, and MIN. Never store numeric information in a character data type. When you use a VARCHAR2 to store data that are inherently numeric, you’re introducing future failures into your system. Eventually, you’ll want to report or run calculations on numeric data, and if they’re not a numeric data type, you’ll get unpredictable and oftentimes wrong results.

Oracle supports three numeric data types:

• NUMBER
• BINARY_DOUBLE
• BINARY_FLOAT

For most situations, you’ll use the NUMBER data type for any type of number data. Its syntax is

NUMBER(scale, precision)

where scale is the total number of digits, and precision is the number of digits to the right of the decimal point. So, with a number defined as NUMBER(5, 2) you can store values +/–999.99. That’s a total of five digits, with two used for precision to the right of the decimal point.

What sometimes confuses DBAs is that you can create a table with columns defined as INT, INTEGER, REAL, DECIMAL, and so on. These data types are all implemented by Oracle with a NUMBER data type. For example, a column specified as INTEGER is implemented as a NUMBER(38).

The BINARY_DOUBLE and BINARY_FLOAT data types are used for scientific calculations. These map to the DOUBLE and FLOAT Java data types. Unless your application is performing rocket science calculations, then use the NUMBER data type for all your numeric requirements.

Date/Time

When capturing and reporting on date-related information, you should always use a DATE or TIMESTAMP data type (and not VARCHAR2). Using the correct date-related data type allows you to perform accurate Oracle date calculations and aggregations and dependable sorting for reporting. If you use a VARCHAR2 for a field that contains date information, you are guaranteeing future reporting inconsistencies and needless conversion functions (such as TO_DATE and TO_CHAR).

Oracle supports three date-related data types:

• DATE
• TIMESTAMP
• INTERVAL

The DATE data type contains a date component as well as a time component that is granular to the second. By default, if you don’t specify a time component when inserting data, then the time value defaults to midnight (0 hour at the 0 second). If you need to track time at a more granular level than the second, then use TIMESTAMP; otherwise, feel free to use DATE.

The TIMESTAMP data type contains a date component and a time component that is granular to fractions of a second. When you define a TIMESTAMP, you can specify the fractional second precision component. For instance, if you wanted five digits of fractional precision to the right of the decimal point, you would specify that as

TIMESTAMP(5)

The maximum fractional precision is 9; the default is 6. If you specify 0 fractional precision, then you have the equivalent of the DATE data type.

The TIMESTAMP data type comes in two additional variations: TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE. These are time zone–aware data types, meaning that when the user selects the data, the time value is adjusted to the time zone of the user’s session.

Oracle also provides an INTERVAL data type. This is meant to store a duration, or interval, of time. There are two types: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. Use the former when precision to the year and month is required. Use the latter when you need to store interval data granular to the day and second.

RAW

The RAW data typeallows you to store binary data in a column. This type of data is sometimes used for storing globally unique identifiers or small amounts of encrypted data.

If you select data from a RAW column, SQL*Plus implicitly applies the built-in RAWTOHEX function to the data retrieved. The data are displayed in hexadecimal format, using characters 0–9 and A–F. When inserting data into a RAW column, the built-in HEXTORAW is implicitly applied.

This is important because if you create an index on a RAW column, the optimizer may ignore the index, as SQL*Plus is implicitly applying functions where the RAW column is referenced in the SQL. A normal index may be of no use, whereas a function-based index using RAWTOHEX may result in a substantial performance improvement.

ROWID

When DBAs hear the word ROWID (row identifier), they often think of a pseudocolumn provided with every table row that contains the physical location of the row on disk; that is correct. However, many DBAs do not realize that Oracle supports an actual ROWID data type, meaning that you can create a table with a column defined as the type ROWID.

There are a few practical uses for the ROWID data type. One valid application would be if you’re having problems when trying to enable a referential integrity constraint and want to capture the ROWID of rows that violate a constraint. In this scenario, you could create a table with a column of the type ROWID and store in it the ROWIDs of offending records within the table. This affords you an efficient way to capture and resolve issues with the offending data (see the section “Enabling Constraints,” later in this chapter, for more details).

LOB

Oracle supports storing large amounts of data in a column via a LOB data type. Oracle supports the following types of LOBs:

• CLOB
• NCLOB
• BLOB
• BFILE

If you have textual data that don’t fit within the confines of a VARCHAR2, then you should use a CLOB to store these data. A CLOB is useful for storing large amounts of character data, such as log files. An NCLOB is similar to a CLOB but allows for information encoded in the nation character set of the database.

BLOBs store large amounts of binary data that usually aren’t meant to be human readable. Typical BLOB data include images, audio, and video files.

CLOBs, NCLOBs, and BLOBs are known as internal LOBs. This is because they are stored inside the Oracle database. These data types reside within data files associated with the database.

BFILEs are known as external LOBs. BFILE columns store a pointer to a file on the OS that is outside the database. When it’s not feasible to store a large binary file within the database, then use a BFILE. BFILEs don’t participate in database transactions and aren’t covered by Oracle security or backup and recovery. If you need those features, then use a BLOB and not a BFILE.

Extended Character Types

Prior to Oracle Database 12c, the maximum length for a VARCHAR2 and NVARCHAR2 was 4,000 bytes, and the maximum length of a RAW column was 2,000 bytes. Starting with Oracle Database 12c, these data types have been extended to accommodate a length of 32,767 bytes.

Check with the current Oracle documentation for the release you’re using to find the exact steps for implementing an extended character type. The procedure for implementing extended character types (as of this writing) is documented in the following list:

1. As SYS, shut down the database:

   SQL> shutdown immediate;

2. Start the database in upgrade mode:

   SQL> startup upgrade;

3. Change the setting of MAX_STRING_SIZE to EXTENDED:

   SQL> alter system set max_string_size=extended scope=both;

4. As SYS, run the utl32k.sql script:

   SQL> @?/rdbms/admin/utl32k.sql

5. Restart the database normally:

   SQL> shutdown immediate;SQL> startup;

Now, you should be able to create an extended column as follows:

SQL> create table d1(dext varchar2(32727)) tablespace users;

The extended character type is actually implemented internally as a LOB, and you can view the LOB details via the USER_LOBS view; for example:

SQL> select table_name, column_name, segment_name, tablespace_name, in_row from user_lobs;

Here is some sample output:

TABLE_NAME COLUMN_NAME     SEGMENT_NAME                   TABLESPACE IN_ROW---------- --------------- ------------------------------ ---------- ----------D1         DEXT            SYS_LOB0000043206C00001$$      USERS      YES

Following normal LOB storage rules, Oracle stores the first 4,000 bytes inline within the table. Anything greater than 4,000 bytes goes into a LOB segment.

Creating a Heap-Organized Table

You use the CREATE TABLE statement to create tables. When creating a table, at minimum you must specify the table name, column name(s), and data types and lengths associated with the columns. The Oracle default table type is heap organized. The term heap means that the data aren’t stored in a specific order in the table (instead, they’re a heap of data). Here is a simple example of creating a heap-organized table:

CREATE TABLE dept
(deptno     NUMBER(10)
,dname      VARCHAR2(14 CHAR)
,loc        VARCHAR2(14 CHAR));

If you don’t specify a tablespace, then the table is created in the default permanent tablespace of the user that creates the table. Allowing the table to be created in the default permanent tablespace is fine for a few small test tables. For anything more sophisticated, you should explicitly specify the tablespace in which you want tables created. For reference (in future examples), here are the creation scripts for two sample tablespaces: HR_DATA and HR_INDEX:

CREATE TABLESPACE hr_data
  DATAFILE '/u01/dbfile/O12C/hr_data01.dbf' SIZE 1000m
  EXTENT MANAGEMENT LOCAL
  UNIFORM SIZE 512k SEGMENT SPACE MANAGEMENT AUTO;
--
CREATE TABLESPACE hr_index
  DATAFILE '/u01/dbfile/O12C/hr_index01.dbf' SIZE 100m
  EXTENT MANAGEMENT LOCAL
  UNIFORM SIZE 512k SEGMENT SPACE MANAGEMENT AUTO;

Usually, when you create a table, you should also specify constraints, such as the primary key. The following code shows the most common features you use when creating a table. This DDL defines primary keys, foreign keys, tablespace information, and comments:

CREATE TABLE dept
(deptno     NUMBER(10)
,dname      VARCHAR2(14 CHAR)
,loc        VARCHAR2(14 CHAR)
,CONSTRAINT dept_pk PRIMARY KEY (deptno)
 USING INDEX TABLESPACE hr_index
) TABLESPACE hr_data;
--
COMMENT ON TABLE dept IS 'Department table';
--
CREATE UNIQUE INDEX dept_uk1 ON dept(dname)
TABLESPACE hr_index;
--
CREATE TABLE emp
(empno      NUMBER(10)
,ename      VARCHAR2(10 CHAR)
,job        VARCHAR2(9 CHAR)
,mgr        NUMBER(4)
,hiredate   DATE
,sal        NUMBER(7,2)
,comm       NUMBER(7,2)
,deptno     NUMBER(10)
,CONSTRAINT emp_pk PRIMARY KEY (empno)
 USING INDEX TABLESPACE hr_index
) TABLESPACE hr_data;
--
COMMENT ON TABLE emp IS 'Employee table';
--
ALTER TABLE emp ADD CONSTRAINT emp_fk1
FOREIGN KEY (deptno)
REFERENCES dept(deptno);
--
CREATE INDEX emp_fk1 ON emp(deptno)
TABLESPACE hr_index;

When creating a table, I usually don’t specify table-level physical space properties. If you don’t specify table-level space properties, then the table inherits its space properties from the tablespace in which it’s created. This simplifies administration and maintenance. If you have tables that require different physical space properties, then you can create separate tablespaces to hold tables with differing needs. For instance, you might create a HR_DATA_LARGE tablespace with extent sizes of 16MB and a HR_DATA_SMALL tablespace with extent sizes of 128KB and choose where a table is created based on its storage requirements. See Chapter 4 for details regarding the creation of tablespaces.

Table 7-2 lists some guidelines to consider when creating tables. These aren’t hard-and-fast rules; adapt them as needed for your environment. Some of these guidelines may seem like obvious suggestions. However, after inheriting many databases over the years, I’ve seen each of these recommendations violated in some way that makes database maintenance difficult and unwieldy.

Implementing Virtual Columns

With Oracle Database 11g and higher, you can create a virtual column as part of your table definition. A virtual column is based on one or more existing columns from the same table or a combination of constants, SQL functions, and user-defined PL/SQL functions, or both. Virtual columns aren’t stored on disk; they’re evaluated at runtime, when the SQL query executes. Virtual columns can be indexed and can have stored statistics.

Prior to Oracle Database 11g, you could simulate a virtual column via a SELECT statement or in a view definition. For example, this next SQL SELECT statement generates a virtual value when the query is executed:

select inv_id, inv_count,
  case when inv_count <= 100 then 'GETTING LOW'
       when inv_count > 100 then 'OKAY'
  end
from inv;

Why use a virtual column? The advantages of doing so are as follows:

• You can create an index on a virtual column; internally, Oracle creates a function-based index.
• You can store statistics in a virtual column that can be used by the cost-based optimizer (CBO).
• Virtual columns can be referenced in WHERE clauses.
• Virtual columns are permanently defined in the database; there is one central definition of such a column.

Here is an example of creating a table with a virtual column:

create table inv(
 inv_id number
,inv_count number
,inv_status generated always as (
  case when inv_count <= 100 then 'GETTING LOW'
       when inv_count > 100 then 'OKAY'
  end)
);

In the prior code listing, specifying GENERATED ALWAYS is optional. For example, this listing is equivalent to the previous one:

create table inv(
 inv_id number
,inv_count number
,inv_status as (
  case when inv_count <= 100 then 'GETTING LOW'
       when inv_count > 100 then 'OKAY'
  end)
);

I prefer to add GENERATED ALWAYS because it reinforces in my mind that the column is always virtual. The GENERATED ALWAYS helps document inline what you’ve done. This aids in maintenance for other DBAs who come along long after you.

To view values generated by virtual columns, first insert some data into the table:

SQL> insert into inv (inv_id, inv_count) values (1,100);

Next, select from the table to view the generated value:

SQL> select * from inv;

Here is some sample output:

    INV_ID  INV_COUNT INV_STATUS
---------- ---------- -----------
         1        100 GETTING LOW

You can also alter a table to contain a virtual column:

alter table inv add(
inv_comm generated always as(inv_count * 0.1) virtual
);

And, you can change the definition of an existing virtual column:

alter table inv modify inv_status generated always as(
case when inv_count <= 50 then 'NEED MORE'
     when inv_count >50 and inv_count <=200 then 'GETTING LOW'
     when inv_count > 200 then 'OKAY'
end);

You can access virtual columns in SQL queries (DML or DDL). For instance, suppose you want to update a permanent column based on the value in a virtual column:

SQL> update inv set inv_count=100 where inv_status='OKAY';

A virtual column itself can’t be updated via the SET clause of an UPDATE statement. However, you can reference a virtual column in the WHERE clause of an UPDATE or DELETE statement.

Optionally, you can specify the data type of a virtual column. If you omit the data type, Oracle derives it from the expression you use to define the virtual column.

Several caveats are associated with virtual columns:

• You can only define a virtual column on a regular, heap-organized table. You can’t define a virtual column on an index-organized table, an external table, a temporary table, object tables, or cluster tables.
• Virtual columns can’t reference other virtual columns.
• Virtual columns can only reference columns from the table in which the virtual column is defined.
• The output of a virtual column must be a scalar value (i.e., a single value, not a set of values).

To view the definition of a virtual column, use the DBMS_METADATA package to see the DDL associated with the table. If you’re selecting from SQL*Plus, you need to set the LONG variable to a value large enough to show all data returned:

SQL> set long 10000;
SQL> select dbms_metadata.get_ddl('TABLE','INV') from dual;

Here is a snippet of the output:

  CREATE TABLE "INV_MGMT"."INV"
   (    "INV_ID" NUMBER,
 "INV_COUNT" NUMBER,
 "INV_STATUS" VARCHAR2(11) GENERATED ALWAYS AS (CASE  WHEN "INV_COUNT"<=50 THEN
'NEED MORE' WHEN ("INV_COUNT">50 AND "INV_COUNT"<=200) THEN 'GETTING LOW' WHEN 
"INV_COUNT">200 THEN 'OKAY' END) VIRTUAL ...

Implementing Invisible Columns

Starting with Oracle Database 12c, you can create invisible columns. When a column is invisible, it cannot be viewed via

• DESCRIBE command
• SELECT * (to access all of a table’s columns)
• %ROWTYPE (in PL/SQL)
• Describes within an Oracle Call Interface (OCI)

However, the column can be accessed if explicitly specified in a SELECT clause or referenced directly in a DML statement (INSERT, UPDATE, DELETE, or MERGE). Invisible columns can also be indexed (just like visible columns).

The main use for an invisible column is to ensure that adding a column to a table won’t disrupt any of the existing application code. If the application code doesn’t explicitly access the invisible column, then it appears to the application as if the column doesn’t exist.

A table can be created with invisible columns, or a column can be added or altered so as to be invisible. A column that is defined as invisible can also be altered so as to be visible. Here is an example of creating a table with an invisible column:

create table inv
(inv_id     number
,inv_desc   varchar2(30 char)
,inv_profit number invisible);

Now, when the table is described, note that the invisible column is not displayed:

SQL> desc inv
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INV_ID                                             NUMBER
 INV_DESC                                           VARCHAR2(30 CHAR)

A column that has been defined as invisible is still accessible if you specify it directly in a SELECT statement or any DML operations. For example, when selecting from a table, you can view the invisible column by specifying it in the SELECT clause:

SQL> select inv_id, inv_desc, inv_profit from inv;

Making Read-Only Tables

Starting with Oracle Database 11g, you can place individual tables in read-only mode. Doing so prevents any INSERT, UPDATE, or DELETE statements from running against a table. In versions prior to Oracle Database 11g, the only way to make a table read-only was to either place the entire database in read-only mode or place a tablespace in read-only mode (making all tables in the tablespace read-only).

There are several reasons why you may require the read-only feature at the table level:

• The data in the table are historical and should never be updated in normal circumstances.
• You’re performing some maintenance on the table and want to ensure that it doesn’t change while it’s being updated.
• You want to drop the table, but before you do, you want to better determine if any users are attempting to update the table.

Use the ALTER TABLE statement to place a table in read-only mode:

SQL> alter table inv read only;

You can verify the status of a read-only table by issuing the following query:

SQL> select table_name, read_only from user_tables where read_only='YES';

To modify a read-only table to read/write, issue the following SQL:

SQL> alter table inv read write;

Understanding Deferred Segment Creation

Starting with Oracle Database 11g Release 2, when you create a table, the creation of the associated segment is deferred until the first row is inserted into the table. This feature has some interesting implications. For instance, if you have thousands of objects that you’re initially creating for an application (such as when you first install it), no space is consumed by any of the tables (or associated indexes) until data are inserted into the application tables. This means that the initial DDL runs more quickly when you create a table, but the first INSERT statement runs slightly slower.

To illustrate the concept of deferred segments, first create a table:

SQL> create table inv(inv_id number, inv_desc varchar2(30 CHAR));

You can verify that the table has been created by inspecting USER_TABLES:

select
 table_name
,segment_created
from user_tables
where table_name='INV';

Here is some sample output:

TABLE_NAME                     SEG
------------------------------ ---
INV                            NO

Next, query USER_SEGMENTS to verify that a segment hasn’t yet been allocated for the table:

select
 segment_name
,segment_type
,bytes
from user_segments
where segment_name='INV'
and segment_type='TABLE';

Here is the corresponding output for this example:

no rows selected

Now, insert a row into a table:

SQL> insert into inv values(1,'BOOK'),

Rerun the query, selecting from USER_SEGMENTS, and note that a segment has been created:

SEGMENT_NAME    SEGMENT_TYPE            BYTES
--------------- ------------------ ----------
INV             TABLE                   65536

If you’re used to working with older versions of Oracle, the deferred–segment creation feature can cause confusion. For example, if you have space-related monitoring reports that query DBA_SEGMENTS or DBA_EXTENTS, be aware that these views aren’t populated for a table or any indexes associated with a table until the first row is inserted into the table.

Creating a Table with an Autoincrementing (Identity) Column

Starting with Oracle Database 12c, you can define a column that is automatically populated and incremented when inserting data. This feature is ideal for automatically populating primary key columns.

You define an autoincrementing (identity) column with the GENERATED AS IDENTITY clause. This example creates a table with primary key column that will be automatically populated and incremented:

create table inv(
 inv_id number generated as identity
,inv_desc varchar2(30 char));
--
alter table inv add constraint inv_pk primary key (inv_id);

Now, you can populate the table without having to specify the primary key value:

insert into inv (inv_desc) values ('Book'),
insert into inv (inv_desc) values ('Table'),

Selecting from the table shows that the INV_ID column has been automatically populated:

select * from inv;

Here is some sample output:

    INV_ID INV_DESC
---------- ------------------------------
         1 Book
         2 Table

When you create an identity column, Oracle automatically creates a sequence and associates the sequence with the column. You can view the sequence information in USER_SEQUENCES:

SQL> select sequence_name, min_value, increment_by from user_sequences;

Here is some sample output for this example:

SEQUENCE_NAME         MIN_VALUE INCREMENT_BY
-------------------- ---------- ------------
ISEQ$$_43216                  1            1

You can identify identity columns via this query:

select table_name, identity_column
from user_tab_columns
where identity_column='YES';

When creating a table with an identity column (such as in the prior example), you can’t directly specify a value for the identity column; for example, if you try this:

insert into inv values(3,'Chair'),

you’ll receive an error:

ORA-32795: cannot insert into a generated always identity column

If, for some reason, you need to occasionally insert values into an identity column, then use the following syntax when creating:

create table inv(
 inv_id number generated by default on null as identity
,inv_desc varchar2(30 char));

Because the underlying mechanism for populating an identity column is a sequence, you have some control over how the sequence is created (just like you would if you manually created a sequence). For instance, you can specify at what number to start the sequence and by how much the sequence increments each time. This example specifies that the underlying sequence start at the number 50 and increment by two each time:

create table inv(
 inv_id number generated as identity (start with 50 increment by 2)
,inv_desc varchar2(30 char));

There are some caveats to be aware of when using autoincrementing (identity) columns:

• Only one per table is allowed.
• They must be numeric.
• They can’t have default values.
• NOT NULL and NOT DEFERRABLE constraints are implicitly applied.
• CREATE TABLE ... AS SELECT will not inherit identity column properties.

Also keep in mind that after inserting into a column that is autoincremented, if you issue a rollback, the transaction is rolled back, but not the autoincremented values from the sequence. This is the expected behavior of a sequence. You can roll back such an insert, but the sequence values are used and gone.

Allowing for Default Parallel SQL Execution

If you work with large tables, you may want to consider creating your tables as PARALLEL. This instructs Oracle to set the degree of parallelism for queries and any subsequent INSERT, UPDATE, DELETE, MERGE, and query statements. This example creates a table with a PARALLEL clause of 2:

create table inv
(inv_id     number,
 inv_desc   varchar2(30 char),
 create_dtt date default sysdate)
parallel 2;

You can specify PARALLEL, NOPARALLEL, or PARALLEL N. If you don’t specify N, Oracle sets the degree of parallelism based on the PARALLEL_THREADS_PER_CPU initialization parameter. You can verify the degree of parallelism with this query:

SQL> select table_name, degree from user_tables;

The main issue to be aware of here is that if a table has been created with a default degree of parallelism, any subsequent queries will execute with parallel threads. You may wonder why a query or a DML statement is executing in parallel (without explicitly invoking a parallel operation).

You can also alter a table to modify its default degree of parallelism:

SQL> alter table inv parallel 1;

Compressing Table Data

As your database grows, you may want to consider table level compression. Compressed data have the benefit of using less disk space and less memory and reduced I/O. Queries that read compressed data potentially run faster because there are fewer blocks to process. However, CPU usage increases as the data are compressed and uncompressed as writes and reads occur, so there is a tradeoff.

Starting with Oracle Database 12c, there are four types of compression available:

• Basic compression
• Advanced row compression (in 11g this was referred to as OLTP compression)
• Warehouse compression (hybrid columnar compression)
• Archive compression (hybrid columnar compression)

Basic compression is enabled with the COMPRESS or COMPRESS BASIC clause (they are synonymous). This example creates a table with basic compression:

create table inv
(inv_id     number,
 inv_desc   varchar2(300 char),
 create_dtt timestamp)
compress basic;

Basic compression provides compression as data are direct-path inserted into the table. By default, tables created with COMPRESS BASIC have a PCTFREE setting of 0. You can override this by specifying PCTFREE when creating the table.

Advanced row compression is enabled with the ROW STORE COMPRESS ADVANCED (in 11g this was enabled with COMPRESS FOR OLTP) clause:

create table inv
(inv_id     number,
 inv_desc   varchar2(300 char),
 create_dtt timestamp)
row store compress advanced;

Advanced row compression provides compression when initially inserting data into the table as well as in any subsequent DML operations. You can verify the compression for a table via the following SELECT statement:

select table_name, compression, compress_for
from user_tables
where table_name='INV';

Here is some sample output:

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
INV                            ENABLED  ADVANCED

You can also create a tablespace with the compression clause. Any table created in that tablespace will inherit the tablespace compression settings. For example, here is how to set the default level of compression for a tablespace:

CREATE TABLESPACE hr_data
  DEFAULT ROW STORE COMPRESS ADVANCED
  DATAFILE '/u01/dbfile/O12C/hr_data01.dbf' SIZE 100m
  EXTENT MANAGEMENT LOCAL
  UNIFORM SIZE 512k SEGMENT SPACE MANAGEMENT AUTO;

If you have a table that already exists, you can alter it to allow compression (either basic or advanced):

SQL> alter table inv row store compress advanced;

Altering to allow compression doesn’t compress the existing data in the table. You’ll need to rebuild the table with Data Pump or move the table to compress the data that were in it prior to enabling compression:

SQL> alter table inv move;

You can disable compression via the NOCOMPRESS clause. This doesn’t affect existing data within the table. Rather, it affects future inserts (basic and advanced row compression) and future DML (advanced row compression); for example,

SQL> alter table inv nocompress;

Oracle also has a warehouse and archive hybrid columnar compression feature that is available when using certain types of storage (such as Exadata). This type of compression is enabled with the COLUMN STORE COMPRESS FOR QUERY  LOW|HIGH or COLUMN STORE COMPRESS FOR ARCHIVE LOW|HIGH clause. For more details regarding this type of compression, see the Oracle Technology Network web site (http://otn.oracle.com).

Avoiding Redo Creation

When you’re creating a table, you have the option of specifying the NOLOGGING clause. The NOLOGGING feature can greatly reduce the amount of redo generation for certain types of operations. Sometimes, when you’re working with large amounts of data, it’s desirable, for performance reasons, to reduce the redo generation when you initially create and insert data into a table.

The downside to eliminating redo generation is that you can’t recover the data created via NOLOGGING in the event a failure occurs after the data are loaded (and before you can back up the table). If you can tolerate some risk of data loss, then use NOLOGGING, but back up the table soon after the data are loaded. If your data are critical, then don’t use NOLOGGING. If your data can be easily recreated, then NOLOGGING is desirable when you’re trying to improve performance of large data loads.

One perception is that NOLOGGING eliminates redo generation for the table for all DML operations. That isn’t correct. The NOLOGGING feature never affects redo generation for normal DML statements (regular INSERT, UPDATE, and DELETE).

The NOLOGGING feature can significantly reduce redo generation for the following types of operations:

• SQL*Loader direct-path load
• Direct-path INSERT /*+ append */
• CREATE TABLE AS SELECT
• ALTER TABLE MOVE
• Creating or rebuilding an index

You need to be aware of some quirks (features) when using NOLOGGING. If your database is in FORCE LOGGING mode, then redo is generated for all operations, regardless of whether you specify NOLOGGING. Likewise, when you’re loading a table, if the table has a referential foreign key constraint defined, then redo is generated regardless of whether you specify NOLOGGING.

You can specify NOLOGGING at one of the following levels:

• Statement
• CREATE TABLE or ALTER TABLE
• CREATE TABLESPACE or ALTER TABLESPACE

I prefer to specify the NOLOGGING clause at the statement or table level. In these scenarios it’s obvious to the DBA executing the statement or DDL that NOLOGGING is used. If you specify NOLOGGING at the tablespace level, then each DBA who creates objects within that tablespace must be aware of this tablespace-level setting. In teams with multiple DBAs, it’s easy for one DBA to be unaware that another DBA has created a tablespace with NOLOGGING.

This example first creates a table with the NOLOGGING option:

create table inv(inv_id number)
tablespace users
nologging;

Next, do a direct-path insert with some test data, and commit the data:

insert /*+ append */ into inv select level from dual
connect by level <= 10000;
--
commit;

What happens if you have a media failure after you’ve populated a table in NOLOGGING mode (and before you’ve made a backup of the table)? After a restore and recovery operation, it will appear that the table has been restored:

SQL> desc inv
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
INV_ID                                             NUMBER

However, try to execute a query that scans every block in the table:

SQL> select * from inv;

Here, an error is thrown, indicating that there is logical corruption in the data file:

ORA-01578: ORACLE data block corrupted (file # 5, block # 203)
ORA-01110: data file 5: '/u01/dbfile/O12C/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

In other words, the data are unrecoverable, because the redo doesn’t exist to restore them. Again, the NOLOGGING option is suitable for large batch loading of data that can easily be reproduced in the event a failure occurs before a backup of the database can be taken after a NOLOGGING operation.

If you specify a logging clause at the statement level, it overrides any table or tablespace setting. If you specify a logging clause at the table level, it sets the default mode for any statements that don’t specify a logging clause and overrides the logging setting at the tablespace. If you specify a logging clause at the tablespace level, it sets the default logging for any CREATE TABLE statements that don’t specify a logging clause.

You verify the logging mode of the database as follows:

SQL> select name, log_mode, force_logging from v$database;

The next statement verifies the logging mode of a tablespace:

SQL> select tablespace_name, logging from dba_tablespaces;

And, this example verifies the logging mode of a table:

SQL> select owner, table_name, logging from dba_tables where logging = 'NO';

You can view the effects of NOLOGGING in a few different ways. One way is to enable autotracing with statistics and view the redo size:

SQL> set autotrace trace statistics;

Then, run a direct-path INSERT statement, and view the redo size statistic:

insert /*+ append */ into inv select level from dual
connect by level <= 10000;

Here is a snippet of the output:

Statistics
----------------------------------------------------------
      13772  redo size

With logging disabled, for direct-path operations, you should see a much smaller redo size number than with a regular INSERT statement, such as,

insert into inv select level from dual
connect by level <= 10000;

Here is the partial output, indicating that the redo size is much greater:

Statistics
----------------------------------------------------------
     159152  redo size

Another method for determining the effects of NOLOGGING is to measure the amount of redo generated for an operation with logging enabled versus operating in NOLOGGING mode. If you have a development environment that you can test in, you can monitor how often the redo logs switch while the operation is taking place. Another simple test is to time how long the operation takes with and without logging. The operation performed in NOLOGGING mode should be faster (because a minimal amount of redo is being generated).

Creating a Table from a Query

Sometimes. it’s convenient to create a table based on the definition of an existing table. For instance, say you want to create a quick backup of a table before you modify the table’s structure or data. Use the CREATE TABLE AS SELECT statement (CTAS) to achieve this; for example,

create table inv_backup
as select * from inv;

The previous statement creates an identical table, complete with data. If you don’t want the data included—you just want the structure of the table replicated—then provide a WHERE clause that always evaluates to false (in this example, 1 will never equal 2):

create table inv_empty
as select * from inv
where 1=2;

You can also specify that no redo be logged when a CTAS table is created. For large data sets, this can reduce the amount of time required to create the table:

create table inv_backup
nologging
as select * from inv;

Be aware that using the CTAS technique with the NOLOGGING clause creates the table as NOLOGGING and doesn’t generate the redo required to recover the data that populate the table as the result of the SELECT statement. Also, if the tablespace (in which the CTAS table is being created) is defined as NOLOGGING, then no redo is generated. In these scenarios, you can’t restore and recover your table in the event a failure occurs before you’re able to back up the table. If your data are critical, then don’t use the NOLOGGING clause.

You can also specify parallelism and storage parameters. Depending on the number of CPUs, you may see some performance gains:

create table inv_backup
nologging
tablespace hr_data
parallel 2
as select * from inv;

SQL> select value from v$diag_info where name='Diag Alert';
VALUE
------------------------------------------------
/orahome/app/oracle/diag/rdbms/o11r2/O11R2/alert

SQL> select value from v$diag_info where name='ADR Home';
VALUE
--------------------------------------
/ora01/app/oracle/diag/rdbms/o12c/O12C

$ cd /ora01/app/oracle/diag/rdbms/o12c/O12C/log

$ cd /ora01/app/oracle/diag/rdbms/o12c/O12C/log/ddl

Obtaining the Needed Lock

When you modify a table, you must have an exclusive lock on the table. One issue is that if a DML transaction has a lock on the table, you can’t alter it. In this situation, you receive this error:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The prior error message is somewhat confusing in that it leads you to believe that you can resolve the problem by acquiring a lock with NOWAIT. However, this is a generic message that is generated when the DDL you’re issuing can’t obtain an exclusive lock on the table. In this situation, you have a few options:

• After issuing the DDL command and receiving the ORA-00054 error, rapidly press the forward slash (/) key repeatedly in hopes of modifying the table between transactions.
• Shut down the database and start it in restricted mode, modify the table, and then open the database for normal use.
• In Oracle Database 11g and higher, set the DDL_LOCK_TIMEOUT parameter.

The last item in the previous list instructs Oracle to repeatedly attempt to run a DDL statement until it obtains the required lock on the table. You can set the DDL_LOCK_TIMEOUT parameter at the system or session level. This next example instructs Oracle to repeatedly try to obtain a lock for 100 seconds:

SQL> alter session set ddl_lock_timeout=100;

The default value for the system-level DDL_LOCK_TIMEOUT initialization parameter is 0. If you want to modify the default behavior for every session in the system, issue an ALTER SYSTEM SET statement. The following command sets the default time-out value to 10 seconds for the system:

SQL> alter system set ddl_lock_timeout=10 scope=both;

Renaming a Table

There are a couple of reasons for renaming a table:

• Make the table conform to standards
• Better determine whether the table is being used before you drop it

This example renames a table, from INV to INV_OLD:

SQL> rename inv to inv_old;

If successful, you should see this message:

Table renamed.

Adding a Column

Use the ALTER TABLE ... ADD statement to add a column to a table. This example adds a column to the INV table:

SQL> alter table inv add(inv_count number);

If successful, you should see this message:

Table altered.

Altering a Column

Occasionally, you need to alter a column to adjust its size or change its data type. Use the ALTER TABLE ... MODIFY statement to adjust the size of a column. This example changes the size of a column to 256 characters:

SQL> alter table inv modify inv_desc varchar2(256 char);

If you decrease the size of a column, first ensure that no values exist that are greater than the decreased size value:

SQL> select max(length(<column_name>)) from <table_name>;

When you change a column to NOT NULL, there must be a valid value for each column. First, verify that there are no NULL values:

SQL> select <column_name> from <table_name> where <column_name> is null;

If any rows have a NULL value for the column you’re modifying to NOT NULL, then you must first update the column to contain a value. Here is an example of modifying a column to NOT NULL:

SQL> alter table inv modify(inv_desc not null);

You can also alter the column to have a default value. The default value is used any time a record is inserted into the table, but no value is provided for a column:

SQL> alter table inv modify(inv_desc default 'No Desc'),

If you want to remove the default value of a column, then set it to NULL:

SQL> alter table inv modify(inv_desc default NULL);

Sometimes, you need to change a table’s data type; for example, a column that was originally incorrectly defined as a VARCHAR2 needs to be changed to a NUMBER. Before you change a column’s data type, first verify that all values for an existing column are valid numeric values. Here is a simple PL/SQL script to do this:

create or replace function isnum(v_in varchar2)
  return varchar is
  val_err exception;
  pragma exception_init(val_err, -6502); -- char to num conv. error
  scrub_num number;
begin
  scrub_num := to_number(v_in);
  return 'Y';
  exception when val_err then
  return 'N';
end;
/

You can use the ISNUM function to detect whether data in a column are numeric. The function defines a PL/SQL pragma exception for the ORA-06502 character-to-number conversion error. When this error is encountered, the exception handler captures it and returns an N. If the value passed in to the ISNUM function is a number, then a Y is returned. If the value can’t be converted to a number, then an N is returned. Here is a simple example illustrating the prior concepts:

SQL> create table stage(hold_col varchar2(30));
SQL> insert into stage values(1);
SQL> insert into stage values('x'),
SQL> select hold_col from stage where isnum(hold_col)='N';
  
HOLD_COL
------------------------------
X

Similarly, when you modify a character column to a DATE or TIMESTAMP data type, it’s prudent to check first to see whether the data can be successfully converted. Here is a function that does that:

create or replace function isdate(p_in varchar2, f_in varchar2)
return varchar is
scrub_dt date;
begin
scrub_dt := to_date(p_in, f_in);
return 'Y';
exception when others then
return 'N';
end;
/

When you call the ISDATE function, you need to pass it a valid date-format mask, such as YYYYMMDD. Here is a simple example to demonstrate the prior concept:

SQL> create table stage2 (hold_col varchar2(30));
SQL> insert into stage2 values('20130103'),
SQL> insert into stage2 values('03-JAN-13'),
SQL> select hold_col from stage2 where isdate(hold_col,'YYYYMMDD')='N';
  
HOLD_COL
------------------------------
03-JAN-13

Renaming a Column

There are a couple of reasons to rename a column:

• Sometimes, requirements change, and you may want to modify the column name to better reflect what the column is used for.
• If you’re planning to drop a column, it doesn’t hurt to rename the column first to better determine whether any users or applications are accessing it.

Use the ALTER TABLE ... RENAME statement to rename a column:

SQL> alter table inv rename column inv_count to inv_amt;

Dropping a Column

Tables sometimes end up having columns that are never used. This may be because the initial requirements changed or were inaccurate. If you have a table that contains an unused column, you should consider dropping it. If you leave an unused column in a table, you may run into issues with future DBAs’ not knowing what the column is used for, and the column can potentially consume space unnecessarily.

Before you drop a column, I recommend that you first rename it. Doing so gives you an opportunity to determine whether any users or applications are using the column. After you’re confident the column isn’t being used, first make a backup of the table, using Data Pump export, and then drop the column. These strategies provide you with options if you drop a column and then subsequently realize that it’s needed.

To drop a column, use the ALTER TABLE ... DROP statement:

SQL> alter table inv drop (inv_name);

Be aware that the DROP operation may take some time if the table from which you’re removing the column contains a large amount of data. This time lag may result in the delay of transactions while the table is being modified (because the ALTER TABLE statement locks the table). In scenarios such as this, you may want to first mark the column unused and then later drop it, when you have a maintenance window:

SQL> alter table inv set unused (inv_name);

When you mark a column unused, it no longer shows up in the table description. The SET UNUSED clause doesn’t incur the overhead associated with dropping the column. This technique allows you to quickly stop the column from being seen or used by SQL queries or applications. Any query that attempts to access an unused column receives the following error:

ORA-00904: ... invalid identifier

You can later drop any unused columns when you’ve scheduled some downtime for the application. Use the DROP UNUSED clause to remove any columns marked UNUSED.

SQL> alter table inv drop unused columns;

Using DELETE

One big difference is that the DELETE statement can be either committed or rolled back. Committing a DELETE statement makes the changes permanent:

SQL> delete from inv;
SQL> commit;

If you issue a ROLLBACK statement instead of COMMIT, the table contains data as they were before the DELETE was issued.

Using TRUNCATE

TRUNCATE is a DDL statement. This means that Oracle automatically commits the statement (and the current transaction) after it runs, so there is no way to roll back a TRUNCATE statement. If you need the option of choosing to roll back (instead of committing) when removing data, then you should use the DELETE statement. However, the DELETE statement has the disadvantage of generating a great deal of undo and redo information. Thus, for large tables, a TRUNCATE statement is usually the most efficient way to remove data.

This example uses a TRUNCATE statement to remove all data from the COMPUTER_SYSTEMS table:

SQL> truncate table computer_systems;

By default, Oracle deallocates all space used for the table, except the space defined by the MINEXTENTS table-storage parameter. If you don’t want the TRUNCATE statement to deallocate the extents, use the REUSE STORAGE parameter:

SQL> truncate table computer_systems reuse storage;

The TRUNCATE statement sets the high-water mark of a table back to 0. When you use a DELETE statement to remove data from a table, the high-water mark doesn’t change. One advantage of using a TRUNCATE statement and resetting the high-water mark is that full table scans only search for rows in blocks below the high-water mark. This can have significant performance implications.

You can’t truncate a table that has a primary key defined that is referenced by an enabled foreign key constraint in a child table—even if the child table contains zero rows. Oracle prevents you from doing this because in a multiuser system, there is the possibility that another session can populate the child table with rows in between the time you truncate the child table and the time you subsequently truncate the parent table. In this scenario, you must temporarily disable the referenced foreign key constraints, issue the TRUNCATE statement, and then reenable the constraints.

Because a TRUNCATE statement is DDL, you can’t truncate two separate tables as one transaction. Compare this behavior with that of DELETE. Oracle does allow you to use the DELETE statement to remove rows from a parent table while the constraints are enabled that reference a child table. This is because DELETE generates undo, is read consistent, and can be rolled back.

Tracing to Detect Space Below the High-Water Mark

You can run this simple test to detect whether you have an issue with unused space below the high-water mark:

1. SQL> set autotrace trace statistics.
2. Run the query that performs the full-table scan.
3. Compare the number of rows processed with the number of logical I/Os (memory and disk accesses).

If the number of rows processed is low, but the number of logical I/Os is high, you may have an issue with the number of free blocks below the high-water mark. Here is a simple example to illustrate this technique:

SQL> set autotrace trace statistics

The next query generates a full-table scan on the INV table:

SQL> select * from inv;

Here is a snippet of the output from AUTOTRACE:

no rows selected
  
Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       7371  consistent gets
       2311  physical reads

The number of rows returned is zero, yet there are 7,371consistent gets (memory accesses) and 2,311 physical reads from disk, indicating free space beneath the high-water mark.

Next, truncate the table, and run the query again:

SQL> truncate table inv;
SQL> select * from inv;

Here is a partial listing from the output of AUTOTRACE:

no rows selected
  
Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads

Note that the number of memory accesses and physical reads are now quite small.

Using DBMS_SPACE to Detect Space Below the High-Water Mark

You can use the DBMS_SPACE package to detect free blocks beneath the high-water mark. Here is an anonymous block of PL/SQL that you can call from SQL*Plus:

set serverout on size 1000000
declare
   p_fs1_bytes number;
   p_fs2_bytes number;
   p_fs3_bytes number;
   p_fs4_bytes number;
   p_fs1_blocks number;
   p_fs2_blocks number;
   p_fs3_blocks number;
   p_fs4_blocks number;
   p_full_bytes number;
   p_full_blocks number;
   p_unformatted_bytes number;
   p_unformatted_blocks number;
begin
   dbms_space.space_usage(
      segment_owner      => user,
      segment_name       => 'INV',
      segment_type       => 'TABLE',
      fs1_bytes          => p_fs1_bytes,
      fs1_blocks         => p_fs1_blocks,
      fs2_bytes          => p_fs2_bytes,
      fs2_blocks         => p_fs2_blocks,
      fs3_bytes          => p_fs3_bytes,
      fs3_blocks         => p_fs3_blocks,
      fs4_bytes          => p_fs4_bytes,
      fs4_blocks         => p_fs4_blocks,
      full_bytes         => p_full_bytes,
      full_blocks        => p_full_blocks,
      unformatted_blocks => p_unformatted_blocks,
      unformatted_bytes  => p_unformatted_bytes
   );
   dbms_output.put_line('FS1: blocks = '||p_fs1_blocks);
   dbms_output.put_line('FS2: blocks = '||p_fs2_blocks);
   dbms_output.put_line('FS3: blocks = '||p_fs3_blocks);
   dbms_output.put_line('FS4: blocks = '||p_fs4_blocks);
   dbms_output.put_line('Full blocks = '||p_full_blocks);
end;
/

In this scenario, you want to check the INV table for free space below the high-water mark. Here is the output of the previous PL/SQL:

FS1: blocks = 0
FS2: blocks = 0
FS3: blocks = 0
FS4: blocks = 3646
Full blocks = 0

In the prior output the FS1 parameter shows that 0 blocks have 0 to 25 percent free space. The FS2 parameter shows that 0 blocks have 25 to 50 percent free space. The FS3 parameter shows that 0 blocks have 50 to 75 percent free space. The FS4 parameter shows there are 3,646 blocks with 75 to 100 percent free space. Finally, there are 0 full blocks. Because there are no full blocks, and a large number of blocks are mostly empty, you can see that free space exists below the high-water mark.

Selecting from Data Dictionary Extents View

You can also detect tables with high-water mark issues by selecting from DBA/ALL/USER_EXTENTS views. If a table has a large number of extents allocated to it, but has zero rows, that’s an indication that an extensive amount of data have been deleted from the table; for example,

SQL> select count(*) from user_extents where segment_name='INV';
  
  COUNT(*)
----------
        44

Now, inspect the number of rows in the table:

SQL> select count(*) from inv;
  
  COUNT(*)
----------
         0

The prior table most likely has had data inserted into it, which resulted in extents’ being allocated. And, subsequently, data were deleted, and the extents remained.

Lowering the High-Water Mark

How can you reduce a table’s high-water mark? You can use several techniques to set the high-water mark back to0:

• A TRUNCATE statement
• ALTER TABLE ... SHRINK SPACE
• ALTER TABLE ...  MOVE

Using the TRUNCATE statement was discussed earlier in this chapter (see the section “Using TRUNCATE”). Shrinking a table and moving a table are discussed in the following sections.

SQL> select tablespace_name, segment_space_management from dba_tablespaces;

SQL> alter table inv enable row movement;

SQL> alter table inv shrink space;

SQL> alter table inv shrink space cascade;

SQL> alter table inv move tablespace users;

SQL> select table_name, tablespace_name from user_tables where table_name='INV';
  
  
TABLE_NAME           TABLESPACE_NAME
-------------------- ------------------------------
INV                  USERS

SQL> alter table inv move tablespace users nologging;

Datafile number
Block number
Location of the row within the block
Object number

SQL> select rowid, emp_id from emp;

ROWID                  EMP_ID
------------------ ----------
AAAFJAAAFAAAAJfAAA          1

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)     EMP_ID
------------------------------------ ----------
                                   5          1

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, note that Oracle generates a cryptic name for the constraint (such as 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));

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 users);

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 nonunique index on the primary key column and then define the primary key constraint:

SQL> create index dept_pk on dept(dept_id) tablespace users;
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.

Confused about which method to use to create a primary key? All 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, which adds the constraint after the table has been created.

Enforcing Unique Key Values

In addition to creating a primary key constraint, you should create unique constraints on any combinations 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. Besides the surrogate primary key, sometimes users have a column (or columns) that the business uses to uniquely identify a record (also called a logical key). Using both a surrogate key and a 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 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 inline the tablespace information 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 users);

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

alter table dept
add constraint dept_desc_uk1 unique (dept_desc)
using index tablespace users;

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) tablespace users;
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.

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

For example, suppose the EMP table is created with a DEPT_ID column. To ensure that each employee is assigned a valid department, you can create a foreign key constraint that enforces the rule that each DEPT_ID in the EMP table must exist in the DEPT table.

For reference, here’s how the parent table DEPT table was created for these examples:

create table dept(
dept_id number primary key,
dept_desc varchar2(30));

A foreign key must reference a column in the parent table that has a primary key or a unique key defined on it. DEPT is the parent table and has a primary key defined on DEPT_ID.

You can use several methods to create a foreign key constraint. The following example creates a foreign key constraint on the DEPT_ID column in the EMP table:

create table emp(
emp_id number,
name varchar2(30),
dept_id constraint emp_dept_fk references dept(dept_id));

Note that the DEPT_ID data type isn’t explicitly defined. The foreign key constraint derives the data type from the referenced DEPT_ID column of the DEPT table. You can also explicitly specify the data type when you define a column (regardless of the foreign key definition):

create table emp(
emp_id number,
name varchar2(30),
dept_id number constraint emp_dept_fk references dept(dept_id));

You can also specify the foreign key definition out of line from the column definition in the CREATE TABLE statement:

create table emp(
emp_id number,
name varchar2(30),
dept_id number,
constraint emp_dept_fk foreign key (dept_id) references dept(dept_id)
);

And, you can alter an existing table to add a foreign key constraint:

alter table emp
add constraint emp_dept_fk foreign key (dept_id)
references dept(dept_id);

Checking for Specific Data Conditions

A check constraint works well for lookups when you have a short list of fairly static values, such as a column that can be either Y or N. In this situation the list of values most likely won’t change, and no information needs to be stored other than Y or N, so a check constraint is the appropriate solution. If you have a long list of values that needs to be periodically updated, then a table and a foreign key constraint are a better solution.

Also, a check constraint works well for a business rule that must always be enforced and that can be written with a simple SQL expression. If you have sophisticated business logic that must be validated, then the application code is more appropriate.

You can define a check constraint when you create a table. The following enforces the ST_FLG column to contain either a 0 or 1:

create table emp(
emp_id number,
emp_name varchar2(30),
st_flg number(1) CHECK (st_flg in (0,1))
);

A slightly better method is to give the check constraint a name:

create table emp(
emp_id number,
emp_name varchar2(30),
st_flg number(1) constraint st_flg_chk CHECK (st_flg in (0,1))
);

A more descriptive way to name the constraint is to embed information in the constraint name that describes the condition that was violated; for example,

create table emp(
emp_id number,
emp_name varchar2(30),
st_flg number(1) constraint "st_flg must be 0 or 1" check (st_flg in (0,1))
);

You can also alter an existing column to include a constraint. The column must not contain any values that violate the constraint being enabled:

SQL> alter table emp add constraint
     "st_flg must be 0 or 1" check (st_flg in (0,1));

Enforcing Not Null Conditions

Another common condition to check for is whether a column is null; you use the NOT NULL constraint to do this. The NOT NULL constraint can be defined in several ways. The simplest technique is shown here:

create table emp(
emp_id number,
emp_name varchar2(30) not null);

A slightly better approach is to give the NOT NULL constraint a name that makes sense to you:

create table emp(
emp_id number,
emp_name varchar2(30) constraint emp_name_nn not null);

Use the ALTER TABLE command if you need to modify a column for an existing table. For the following command to work, there must not be any NULL values in the column being defined as NOT NULL:

SQL> alter table emp modify(emp_name not null);

Disabling Constraints

One nice feature of Oracle is that you can disable and enable constraints without dropping and recreating them. This means that you avoid having to know the DDL statements that would be required to recreate the dropped constraints.

Occasionally, you need to disable constraints. For example, you may be trying to truncate a table but receive the following error message:

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Oracle doesn’t allow a truncate operation on a parent table with a primary key that is referenced by an enabled foreign key in a child table. If you need to truncate a parent table, you first have to disable all the enabled foreign key constraints that reference the parent table’s primary key. Run this query to determine the names of the constraints that need to be disabled:

col primary_key_table form a18
col primary_key_constraint form a18
col fk_child_table form a18
col fk_child_table_constraint form a18
--
select
 b.table_name primary_key_table
,b.constraint_name primary_key_constraint
,a.table_name fk_child_table
,a.constraint_name fk_child_table_constraint
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner')
and b.table_name = upper('&pk_table_name'),

For this example, there is only one foreign key dependency:

PRIMARY_KEY_TAB PRIMARY_KEY_CON FK_CHILD_TABLE  FK_CHILD_TABLE_
--------------- --------------- --------------- ---------------
DEPT            DEPT_PK         EMP             EMP_DEPT_FK

Use the ALTER TABLE statement to disable constraints on a table. In this case, there is only one foreign key to disable:

SQL> alter table emp disable constraint emp_dept_fk;

You can now truncate the parent table:

SQL> truncate table dept;

Don’t forget to reenable the foreign key constraints after the truncate operation has completed, like this:

SQL> alter table emp enable constraint emp_dept_fk;

You can disable a primary key and all dependent foreign key constraints with the CASCADE option of the DISABLE clause. For example, the next line of code disables all foreign key constraints related to the primary key constraint:

SQL> alter table dept disable constraint dept_pk cascade;

This statement doesn’t cascade through all levels of dependencies; it only disables the foreign key constraints directly dependent on DEPT_PK. Also keep in mind that there is no ENABLE...CASCADE statement. To reenable the constraints, you have to query the data dictionary to determine which constraints have been disabled and then reenable them individually.

Sometimes, you run into situations, when loading data, in which it’s convenient to disable all the foreign keys before loading the data (perhaps from a schema-level import, using the imp utility). In these situations the imp utility imports the tables in alphabetical order and doesn’t ensure that child tables are imported before parent tables. You may also want to run several import jobs in parallel to take advantage of parallel hardware. In such scenarios, you can disable the foreign keys, perform the import, and then reenable the foreign keys.

Here is a script that uses SQL to generate SQL to disable all foreign key constraints for a user:

set lines 132 trimsp on head off feed off verify off echo off pagesize 0
spo dis_dyn.sql
select 'alter table ' || a.table_name
|| ' disable constraint ' || a.constraint_name || ';'
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner'),
spo off;

This script generates a file, named dis_dyn.sql, that contains the SQL statements to disable all the foreign key constraints for a user.

EnablingConstraints

This section contains a few scripts to help you enable constraints that you’ve disabled. Listed next is a script that creates a file with the SQL statements required to reenable any foreign key constraints for tables owned by a specified user:

set lines 132 trimsp on head off feed off verify off echo off pagesize 0
spo enable_dyn.sql
select 'alter table ' || a.table_name
|| ' enable constraint ' || a.constraint_name || ';'
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner'),
spo off;

When enabling constraints, by default, Oracle checks to ensure that the data don’t violate the constraint definition. If you’re fairly certain that the data integrity is fine and that you don’t need to incur the performance hit by revalidating the constraint, you can use the NOVALIDATE clause when reenabling the constraints. Here is an example:

select 'alter table ' || a.table_name
|| ' modify constraint ' || a.constraint_name || ' enable novalidate;'
from dba_constraints a
,dba_constraints b
where a.r_constraint_name = b.constraint_name
and a.r_owner = b.owner
and a.constraint_type = 'R'
and b.owner = upper('&table_owner'),

The NOVALIDATE clause instructs Oracle not to validate the constraints being enabled, but it does enforce that any new DML activities adhere to the constraint definition.

In multiuser systems the possibility exists that another session has inserted data into the child table while the foreign key constraint was disabled. If that happens, you see the following error when you attempt to reenable the foreign key:

ORA-02298: cannot validate (<owner>.<constraint>) - parent keys not found

In this scenario, you can use the ENABLE NOVALIDATE clause:

SQL> alter table emp enable novalidate constraint emp_dept_fk;

To clean up the rows that violate the constraint, first ensure that you have an EXCEPTIONS table created in your currently connected schema. If you don’t have an EXCEPTIONS table, use this script to create one:

SQL> @?/rdbms/admin/utlexcpt.sql

Next, populate the EXCEPTIONS table with the rows that violate the constraint, using the EXCEPTIONS INTO clause:

SQL> alter table emp modify constraint emp_dept_fk validate
exceptions into exceptions;

This statement still throws the ORA-02298 error as long as there are rows that violate the constraint. The statement also inserts records into the EXCEPTIONS table for any bad rows. You can now use the ROW_ID column of the EXCEPTIONS table to remove any records that violate the constraint.

Here, you see that one row needs to be removed from the EMP table:

SQL> select * from exceptions;

Here is some sample output:

ROW_ID             OWNER    TABLE_NAME CONSTRAINT
------------------ -------- ---------- --------------------
AAAFKQAABAAAK8JAAB MV_MAINT EMP        EMP_DEPT_FK

To remove the offending record, issue a DELETE statement:

SQL> delete from emp where rowid = 'AAAFKQAABAAAK8JAAB';

If the EXCEPTIONS table contains many records, you can run a query such as the following to delete by OWNER and TABLE_NAME:

delete from emp where rowid in
(select row_id
from exceptions
where owner=upper('&owner') and table_name = upper('&table_name'));

You may also run into situations in which you need to disable primary key or unique key constraints, or both. For instance, you may want to perform a large data load and for performance reasons want to disable the primary key and unique key constraints. You don’t want to incur the overhead of having every row checked as it’s inserted.

The same general techniques used for disabling foreign keys are applicable for disabling primary and unique keys. Run this query to display the primary key and unique key constraints for a user:

select
a.table_name
,a.constraint_name
,a.constraint_type
from dba_constraints a
where a.owner = upper('&table_owner')
and a.constraint_type in ('P','U')
order by a.table_name;

When the table name and constraint name are identified, use the ALTER TABLE statement to disable the constraint:

SQL> alter table dept disable constraint dept_pk;