Pro Oracle Database 11g Administration (28 page)

A logical table that consists of

Type used with large tables with millions of

separate physical segments.

rows.

Clustered

A group of tables that share the

Type used to reduce I/O for tables that are

same data blocks.

often joined on the same columns.

External

Tables that use data stored in

Type lets you efficiently access data in a file

operating system files outside

outside of the database (like a CSV file).

of the database.

Nested

A table with a column with a

Rarely used.

data type that is another table.

Object

A table with a column with a

Rarely used.

data type that is an object type.

This chapter focuses on the table types that are most often used, in particular heap-organized, index-organized, and temporary tables. Partitioned tables are used extensively in data warehouse environments and are covered separately in Chapter 12. For details on table types not covered in this chapter, see the Oracle SQL Reference Guide, which is available for download from http://otn.oracle.com.

The number of table features expands with each new version of Oracle. Consider this: the Oracle SQL Reference Guide presents nearly 80 pages of syntax associated with the CREATE TABLE statement. On top of that, the ALTER TABLE statement takes up another 80 plus pages of details related to table maintenance. For most situations, you typically need to use only a fraction of the table options available.

134

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Creating a Table

Listed next are the general factors you should consider when creating a table:

• Type of table (heap-organized, temporary, index-organized, partitioned, and so on)

• Naming conventions

• Column data types and sizes

• Constraints (primary key, foreign keys, and so on)

• Index requirements (see Chapter 8 for details)

• Initial storage requirements

• Special features such as virtual columns, read-only, parallel, compression, no logging, and so on

• Growth requirements

• Tablespace(s) for the table and its indexes

Before you run a CREATE TABLE statement, you need to give some thought to each item in the previous list. To that end, DBAs often use data-modeling tools to help manage the creation of DDL

scripts that are used to create database objects. Data-modeling tools allow you to visually define tables and relationships and the underlying database features.

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 associated with the columns. The Oracle default table type is heap-organized. The term
heap
means that the data isn’t stored in a specific order in the table (instead, it’s a heap of data). Here’s a simple example of creating a heap-organized table with four columns:

create table d_sources(

d_source_id number not null,

source_type varchar2(32),

create_dtt date default sysdate not null,

update_dtt timestamp(5)

);

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.

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:

135

CHAPTER 7 ■ TABLES AND CONSTRAINTS

create table operating_systems(

operating_system_id number(19, 0) not null,

version varchar2(50),

os_name varchar2(256),

release varchar2(50),

vendor varchar2(50),

create_dtt date default sysdate not null,

update_dtt date,

constraint operating_systems_pk primary key (operating_system_id)

using index tablespace inv_mgmt_index

)

tablespace inv_mgmt_data

;

--

create unique index operating_system_uk1 on operating_systems

(os_name, version, release, vendor)

tablespace inv_mgmt_index

;

--

create table computer_systems(

computer_system_id number(38, 0) not null,

agent_uuid varchar2(256),

operating_system_id number(19, 0) not null,

hardware_model varchar2(50),

create_dtt date default sysdate not null,

update_dtt date,

constraint computer_systems_pk primary key (computer_system_id)

using index tablespace inv_mgmt_index

) tablespace inv_mgmt_data;

--

comment on column computer_systems.computer_system_id is

'Surrogate key generated via an Oracle sequence.';

--

create unique index computer_system_uk1 on computer_systems(agent_uuid) tablespace inv_mgmt_index;

--

alter table computer_systems add constraint computer_systems_fk1

foreign key (operating_system_id)

references operating_systems(operating_system_id);

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 example, you might create a DATA_LARGE tablespace with extent sizes of 16MB and a DATA_SMALL tablespace with extents 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.

136

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Table 7–2.
Guidelines to Consider When Creating Tables

Recommendation Reasoning

Use standards when naming tables, columns,

Helps document the application and simplifies

constraints, triggers, indexes, and so on.

maintenance.

If a column always contains numeric data, make it

Enforces a business rule and allows for the greatest

a number data type.

flexibility, performance, and consistent results

when using Oracle SQL math functions (which

may behave differently for an “01” character

versus a 1 number).

If you have a business rule that defines the length

Enforces a business rule and keeps the data

and precision of a number field, then enforce it:

cleaner.

for example, NUMBER(7,2). If you don’t have a

business rule, make it NUMBER(38).

For character data that is of variable length, use

Follows Oracle’s recommendation of using

VARCHAR2 (and not VARCHAR).

VARCHAR2 for character data (instead of VARCHAR).

The Oracle documentation states that in the

future, VARCHAR will be redefined as a separate data

type.

If you have a business rule that specifies the

Enforces a business rule and keeps the data

maximum length of a column, then use that

cleaner.

length, as opposed to making all columns

VARCHAR2(4000).

Use DATE and TIMESTAMP data types appropriately.

Enforces a business rule, ensures that the data is of

the appropriate format, and allows for the greatest

flexibility when using SQL date functions.

Specify a separate tablespace for the table and

Simplifies administration and maintenance.

indexes. Let the table and indexes inherit storage

attributes from the tablespaces.

Most tables should be created with a primary key.

Enforces a business rule and allows you to

uniquely identify each row.

Create a numeric surrogate key to be the primary

Makes joins easier and more efficient.

key for each table. Populate the surrogate key from

a sequence.

Create primary-key constraints out of line.

Allows you more flexibility when creating the

primary key, especially if you have a situation

where the primary key consists of multiple

columns.

137

CHAPTER 7 ■ TABLES AND CONSTRAINTS

Recommendation Reasoning

Create a unique key for the logical user: a

Enforces a business rule and keeps the data

recognizable combination of columns that makes

cleaner.

a row unique.

Create comments for the tables and columns.

Helps document the application and eases

maintenance.

Avoid large object (LOB data types if possible.

Prevents maintenance issues associated with LOB

columns, like unexpected growth, performance

issues when copying, and so on.

If a column always should always have a value,

Enforces a business rule and keeps the data

then enforce it with a NOT NULL constraint.

cleaner.

Create audit-type columns such as CREATE_DTT and

Helps with maintenance and figuring out when

UPDATE_DTT that are automatically populated with

data was inserted and/or updated. Other types of

default values and/or triggers.

audit columns to consider include the users who

inserted and updated the row.

Use check constraints where appropriate.

Enforces a business rule and keeps the data

cleaner.

Define foreign keys where appropriate.

Enforces a business rule and keeps the data

cleaner.

Implementing Virtual Columns

With Oracle Database 11
g
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 and/or a combination of constants, SQL functions, and user-defined PL/SQL functions. 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 11
g
, 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).

138

CHAPTER 7 ■ TABLES AND CONSTRAINTS

• 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’s 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, the next 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 helps with 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

■
Note
If you insert data into the table, nothing is stored in a column GENERATED ALWAYS AS. The virtual value is generated when you select from the table.

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

);

139

CHAPTER 7 ■ TABLES AND CONSTRAINTS

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 example, 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';