2 Tables and Table Clusters

Schema Object Types

The most important schema objects in a relational database are tables. A table stores data in rows.

Oracle SQL enables you to create and manipulate many other types of schema objects, including the following:

• Indexes

  Indexes are schema objects that contains an entry for each indexed row of the table or table cluster and provide direct, fast access to rows. Oracle Database supports several types of index. An index-organized table is a table in which the data is stored in an index structure. See Chapter 3, "Indexes and Index-Organized Tables".

• Partitions

  Partitions are pieces of large tables and indexes. Each partition has its own name and may optionally have its own storage characteristics. See "Overview of Partitions".

• Views

  Views are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data. See "Overview of Views".

• Sequences

  A sequence is a user-created object that can be shared by multiple users to generate integers. Typically, sequences are used to generate primary key values. See "Overview of Sequences".

• Dimensions

  A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. Dimensions are commonly used to categorize data such as customers, products, and time. See "Overview of Dimensions".

• Synonyms

  A synonym is an alias for another schema object. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary. See "Overview of Synonyms".

• PL/SQL subprograms and packages

  PL/SQL is the Oracle procedural extension of SQL. A PL/SQL subprogram is a named PL/SQL block that can be invoked with a set of parameters. A PL/SQL package groups logically related PL/SQL types, variables, and subprograms. See "PL/SQL Subprograms" and "PL/SQL Packages".

Other types of objects are also stored in the database and can be created and manipulated with SQL statements but are not contained in a schema. These objects include database users, roles, contexts, and directory objects.

Schema Object Storage

Some schema objects store data in logical storage structures called segments. For example, a nonpartitioned heap-organized table or an index creates a segment. Other schema objects, such as views and sequences, consist of metadata only. This section describes only schema objects that have segments.

Oracle Database stores a schema object logically within a tablespace. There is no relationship between schemas and tablespaces: a tablespace can contain objects from different schemas, and the objects for a schema can be contained in different tablespaces. The data of each object is physically contained in one or more data files.

Figure 2-2 shows a possible configuration of table and index segments, tablespaces, and data files. The data segment for one table spans two data files, which are both part of the same tablespace. A segment cannot span multiple tablespaces.

Figure 2-2 Segments, Tablespaces, and Data Files

Description of "Figure 2-2 Segments, Tablespaces, and Data Files"

Schema Object Dependencies

Some schema objects reference other objects, creating schema object dependencies. For example, a view contains a query that references tables or other views, while a PL/SQL subprogram invokes other subprograms. If the definition of object A references object B, then A is a dependent object with respect to B and B is a referenced object with respect to A.

Oracle Database provides an automatic mechanism to ensure that a dependent object is always up to date with respect to its referenced objects. When a dependent object is created, the database tracks dependencies between the dependent object and its referenced objects. When a referenced object changes in a way that might affect a dependent object, the dependent object is marked invalid. For example, if a user drops a table, no view based on the dropped table is usable.

An invalid dependent object must be recompiled against the new definition of a referenced object before the dependent object is usable. Recompilation occurs automatically when the invalid dependent object is referenced.

As an illustration of how schema objects can create dependencies, the following sample script creates a table test_table and then a procedure that queries this table:

CREATE TABLE test_table ( col1 INTEGER, col2 INTEGER );

CREATE OR REPLACE PROCEDURE test_proc
AS
BEGIN
 FOR x IN ( SELECT col1, col2 FROM test_table )
 LOOP
   -- process data
   NULL;
 END LOOP;
END;
/

The following query of the status of procedure test_proc shows that it is valid:

SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
 
OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID

After adding the col3 column to test_table, the procedure is still valid because the procedure has no dependencies on this column:

SQL> ALTER TABLE test_table ADD col3 NUMBER;
 
Table altered.
 
SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
 
OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID

However, changing the data type of the col1 column, which the test_proc procedure depends on in, invalidates the procedure:

SQL> ALTER TABLE test_table MODIFY col1 VARCHAR2(20);
 
Table altered.
 
SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
 
OBJECT_NAME STATUS
----------- -------
TEST_PROC   INVALID

Running or recompiling the procedure makes it valid again, as shown in the following example:

SQL> EXECUTE test_proc
 
PL/SQL procedure successfully completed.
 
SQL> SELECT OBJECT_NAME, STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'TEST_PROC';
 
OBJECT_NAME STATUS
----------- -------
TEST_PROC   VALID

SYS and SYSTEM Schemas

All Oracle databases include default administrative accounts. Administrative accounts are highly privileged and are intended only for DBAs authorized to perform tasks such as starting and stopping the database, managing memory and storage, creating and managing database users, and so on.

The administrative account SYS is automatically created when a database is created. This account can perform all database administrative functions. The SYS schema stores the base tables and views for the data dictionary. These base tables and views are critical for the operation of Oracle Database. Tables in the SYS schema are manipulated only by the database and must never be modified by any user.

The SYSTEM account is also automatically created when a database is created. The SYSTEM user name is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle Database options and tools. Never use the SYSTEM schema to store tables of interest to nonadministrative users.

Sample Schemas

An Oracle database may include sample schemas, which are a set of interlinked schemas that enable Oracle documentation and Oracle instructional materials to illustrate common database tasks. The hr schema is a sample schema that contains information about employees, departments and locations, work histories, and so on.

Figure 2-3 is an entity-relationship diagram of the tables in the hr schema. Most examples in this book use objects from this schema.

Figure 2-3 HR Schema

Description of "Figure 2-3 HR Schema"

Columns and Rows

A table definition includes a table name and set of columns. A column identifies an attribute of the entity described by the table. For example, the column employee_id in the employees table refers to the employee ID attribute of an employee entity.

In general, you give each column a column name, a data type, and a width when you create a table. For example, the data type for employee_id is NUMBER(6), indicating that this column can only contain numeric data up to 6 digits in width. The width can be predetermined by the data type, as in the case of DATE.

A table can contain a virtual column, which unlike a nonvirtual column does not consume disk space. The database derives the values in a virtual column on demand by computing a set of user-specified expressions or functions. For example, the virtual column income could be a function of the salary and commission_pct columns.

After you create a table, you can insert, query, delete, and update rows using SQL. A row is a collection of column information corresponding to a record in a table. For example, a row in the employees table describes the attributes of a specific employee.

Example: CREATE TABLE and ALTER TABLE Statements

The Oracle SQL command to create a table is CREATE TABLE. Example 2-1 shows the CREATE TABLE statement for the employees table in the hr sample schema. The statement specifies columns such as employee_id, first_name, and so on, specifying a data type such as NUMBER or DATE for each column.

CREATE TABLE employees
    ( employee_id    NUMBER(6)
    , first_name     VARCHAR2(20)
    , last_name      VARCHAR2(25)
         CONSTRAINT     emp_last_name_nn  NOT NULL
    , email          VARCHAR2(25)
        CONSTRAINT     emp_email_nn  NOT NULL
    , phone_number   VARCHAR2(20)
    , hire_date      DATE
        CONSTRAINT     emp_hire_date_nn  NOT NULL
    , job_id         VARCHAR2(10)
        CONSTRAINT     emp_job_nn  NOT NULL
    , salary         NUMBER(8,2)
    , commission_pct NUMBER(2,2)
    , manager_id     NUMBER(6)
    , department_id  NUMBER(4)
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0)
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
    ) ;

Example 2-2 shows an ALTER TABLE statement that adds integrity constraints to the employees table. Integrity constraints enforce business rules and prevent the entry of invalid information into tables.

ALTER TABLE employees
ADD ( CONSTRAINT     emp_emp_id_pk
                     PRIMARY KEY (employee_id)
    , CONSTRAINT     emp_dept_fk
                     FOREIGN KEY (department_id)
                      REFERENCES departments
    , CONSTRAINT     emp_job_fk
                     FOREIGN KEY (job_id)
                      REFERENCES jobs (job_id)
    , CONSTRAINT     emp_manager_fk
                     FOREIGN KEY (manager_id)
                      REFERENCES employees
    ) ;

Example 2-3 shows 8 rows and 6 columns of the hr.employees table.

EMPLOYEE_ID FIRST_NAME  LAST_NAME      SALARY COMMISSION_PCT DEPARTMENT_ID
----------- ----------- ------------- ------- -------------- -------------
        100 Steven      King            24000                           90
        101 Neena       Kochhar         17000                           90
        102 Lex         De Haan         17000                           90
        103 Alexander   Hunold           9000                           60
        107 Diana       Lorentz          4200                           60
        149 Eleni       Zlotkey         10500             .2            80
        174 Ellen       Abel            11000             .3            80
        178 Kimberely   Grant            7000            .15

The output in Example 2-3 illustrates some of the following important characteristics of tables, columns, and rows:

• A row of the table describes the attributes of one employee: name, salary, department, and so on. For example, the first row in the output shows the record for the employee named Steven King.

• A column describes an attribute of the employee. In the example, the employee_id column is the primary key, which means that every employee is uniquely identified by employee ID. Any two employees are guaranteed not to have the same employee ID.

• A non-key column can contain rows with identical values. In the example, the salary value for employees 101 and 102 is the same: 17000.

• A foreign key column refers to a primary or unique key in the same table or a different table. In this example, the value of 90 in department_id corresponds to the department_id column of the departments table.

• A field is the intersection of a row and column. It can contain only one value. For example, the field for the department ID of employee 104 contains the value 60.

• A field can lack a value. In this case, the field is said to contain a null value. The value of the commission_pct column for employee 100 is null, whereas the value in the field for employee 149 is .2. A column allows nulls unless a NOT NULL or primary key integrity constraint has been defined on this column, in which case no row can be inserted without a value for this column.

Oracle Data Types

Each column has a data type, which is associated with a specific storage format, constraints, and valid range of values. The data type of a value associates a fixed set of properties with the value. These properties cause Oracle Database to treat values of one data type differently from values of another. For example, you can multiply values of NUMBER data type, but not values of RAW data type.

When you create a table, you must specify a data type for each of its columns. Each value subsequently inserted in a column assumes the column data type.

Oracle Database provides several built-in data types. The most commonly used data types fall into the following categories:

• Character Data Types

• Numeric Data Types

• Datetime Data Types

• Rowid Data Types

• Format Models and Data Types

Other important categories of built-in types include raw, large objects (LOBs), and collections. PL/SQL has data types for constants and variables, which include BOOLEAN, reference types, composite types (records), and user-defined types.

SQL> SELECT ROWID FROM employees WHERE employee_id = 100;
 
ROWID
------------------
AAAPecAAFAAAABSAAA

SQL> SELECT last_name employee, TO_CHAR(salary, '$99,990.99')
  2  FROM   employees
  3  WHERE  department_id = 80 AND last_name = 'Russell';
 
EMPLOYEE                  TO_CHAR(SAL
------------------------- -----------
Russell                    $14,000.00

SQL> UPDATE employees
  2  SET hire_date = TO_DATE('1998 05 20','YYYY MM DD')
  3  WHERE last_name = 'Hunold';

Integrity Constraints

Integrity constraints are named rules that restrict the values for one or more columns in a table. These rules prevent invalid data entry into tables. Also, constraints can prevent the deletion of a table when certain dependencies exist.

If a constraint is enabled, then the database checks data as it is entered or updated. Data that does not conform to the constraint is prevented from being entered. If a constraint is disabled, then data that does not conform to the constraint can be allowed to enter the database.

In Example 2-1, the CREATE TABLE statement specifies NOT NULL constraints for the last_name, email, hire_date, and job_id columns. The constraint clauses identify the columns and the conditions of the constraint. These constraints ensure that the specified columns contain no null values. For example, an attempt to insert a new employee without a job ID generates an error.

You can create a constraint when or after you create a table. Constraints can be temporarily disabled if needed. The database stores constraints in the data dictionary.

Object Tables

An Oracle object type is a user-defined type with a name, attributes, and methods. Object types make it possible to model real-world entities such as customers and purchase orders as objects in the database.

An object type defines a logical structure, but does not create storage. Example 2-5 creates an object type named department_typ.

CREATE TYPE department_typ AS OBJECT
   ( d_name     VARCHAR2(100),
     d_address  VARCHAR2(200) );
/

An object table is a special kind of table in which each row represents an object. The CREATE TABLE statement in Example 2-6 creates an object table named departments_obj_t of the object type department_typ. The attributes (columns) of this table are derived from the definition of the object type. The INSERT statement inserts a row into this table.

CREATE TABLE departments_obj_t OF department_typ;
INSERT INTO departments_obj_t 
  VALUES ('hr', '10 Main St, Sometown, CA');

Like a relational column, an object table can contain rows of just one kind of thing, namely, object instances of the same declared type as the table. By default, every row object in an object table has an associated logical object identifier (OID) that uniquely identifies it in an object table. The OID column of an object table is a hidden column.

Temporary Tables

Oracle Database temporary tables hold data that exists only for the duration of a transaction or session. Data in a temporary table is private to the session, which means that each session can only see and modify its own data.

Temporary tables are useful in applications where a result set must be buffered. For example, a scheduling application enables college students to create optional semester course schedules. Each schedule is represented by a row in a temporary table. During the session, the schedule data is private. When the student decides on a schedule, the application moves the row for the chosen schedule to a permanent table. At the end of the session, the schedule data in the temporary data is automatically dropped.

External Tables

An external table accesses data in external sources as if this data were in a table in the database. You can use SQL, PL/SQL, and Java to query the external data.

External tables are useful for querying flat files. For example, a SQL-based application must access employee records in a text file. The records are in the following form:

100,Steven,King,SKING,515.123.4567,17-JUN-03,AD_PRES,31944,150,90
101,Neena,Kochhar,NKOCHHAR,515.123.4568,21-SEP-05,AD_VP,17000,100,90 
102,Lex,De Haan,LDEHAAN,515.123.4569,13-JAN-01,AD_VP,17000,100,90

You could create an external table, copy the file to the location specified in the external table definition, and use SQL to query the records in the text file.

External tables are also valuable for performing ETL tasks common in data warehouse environments. For example, external tables enable the pipelining of the data loading phase with the transformation phase, eliminating the need to stage data inside the database in preparation for further processing inside the database. See "Overview of Data Warehousing and Business Intelligence".

External Table Access Drivers

An access driver is an API that interprets the external data for the database. The access driver runs inside the database, which uses the driver to read the data in the external table. The access driver and the external table layer are responsible for performing the transformations required on the data in the data file so that it matches the external table definition. Figure 2-4 represents how external data is accessed.

Figure 2-4 External Tables

Description of "Figure 2-4 External Tables"

Oracle provides the ORACLE_LOADER (default) and ORACLE_DATAPUMP access drivers for external tables. For both drivers, the external files are not Oracle data files.

ORACLE_LOADER enables read-only access to external files using SQL*Loader. You cannot create, update, or append to an external file using the ORACLE_LOADER driver.

The ORACLE_DATAPUMP driver enables you to unload external data. This operation involves reading data from the database and inserting the data into an external table, represented by one or more external files. After external files are created, the database cannot update or append data to them. The driver also enables you to load external data, which involves reading an external table and loading its data into a database.

See Also:

• Oracle Database Administrator's Guide to learn about managing external tables, external connections, and directory objects

• Oracle Database Utilities to learn about external tables

• Oracle Database SQL Language Reference for information about creating and querying external tables

Table Storage

Oracle Database uses a data segment in a tablespace to hold the table data. As explained in "User Segments", a segment is composed of data files made up of data blocks.

The data segment for a table (or cluster data segment, when dealing with a table cluster) is located in either the default tablespace of the table owner or in a tablespace named in the CREATE TABLE statement.

50,Shipping,121,1500
120,Treasury,,1700
70,Public Relations,204,2700
30,Purchasing,114,1700
130,Corporate Tax,,1700
10,Administration,200,1700
110,Accounting,205,1700

ALTER TABLE oe.orders COMPRESS FOR OLTP;

CREATE TABLE sales (
    prod_id     NUMBER     NOT NULL,
    cust_id     NUMBER     NOT NULL, ... )
 PCTFREE 5 NOLOGGING NOCOMPRESS
 PARTITION BY RANGE (time_id)
 ( partition sales_2008 VALUES LESS THAN(TO_DATE(...)) COMPRESS BASIC,
   partition sales_2009 VALUES LESS THAN (MAXVALUE) COMPRESS FOR OLTP );

Overview of Indexed Clusters

An indexed cluster is a table cluster that uses an index to locate data. The cluster index is a B-tree index on the cluster key. A cluster index must be created before any rows can be inserted into clustered tables.

Assume that you create the cluster employees_departments_cluster with the cluster key department_id, as shown in Example 2-8. Because the HASHKEYS clause is not specified, this cluster is an indexed cluster. Afterward, you create an index named idx_emp_dept_cluster on this cluster key.

CREATE CLUSTER employees_departments_cluster
   (department_id NUMBER(4))
SIZE 512;

CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;

You then create the employees and departments tables in the cluster, specifying the department_id column as the cluster key, as follows (the ellipses mark the place where the column specification goes):

CREATE TABLE employees ( ... )
   CLUSTER employees_departments_cluster (department_id);
 
CREATE TABLE departments ( ... )
   CLUSTER employees_departments_cluster (department_id);

Finally, you add rows to the employees and departments tables. The database physically stores all rows for each department from the employees and departments tables in the same data blocks. The database stores the rows in a heap and locates them with the index.

Figure 2-5 shows the employees_departments_cluster cluster, which contains employees and departments. The database stores rows for employees in department 20 together, department 110 together, and so on. If the tables are not clustered, then the database does not ensure that the related rows are stored together.

Figure 2-5 Clustered Table Data

Description of "Figure 2-5 Clustered Table Data"

The B-tree cluster index associates the cluster key value with the database block address of the block containing the data. For example, the index entry for key 20 shows the address of the block that contains data for the employees in department 20:

20,AADAAAA9d

The cluster index is separately managed, just like an index on a nonclustered table, and can exist in a separate tablespace from the table cluster.

Overview of Hash Clusters

A hash cluster is like an indexed cluster, except the index key is replaced with a hash function. No separate cluster index exists. In a hash cluster, the data is the index.

The key of a hash cluster, like the key of an indexed cluster, is a single column or composite key. Oracle Database uses a hash function to generate a distribution of numeric values, called hash values, which are based on specific cluster key values. The database hashes the cluster key into a data block address where the data is located. The database stores all rows with the same key value together on disk.

With an indexed table or index cluster, Oracle Database locates table rows using key values stored in a separate index. To find or store a row in an indexed table or cluster, at least two I/Os must be performed:

• One or more I/Os to find or store the key value in the index

• Another I/O to read or write the row in the table or cluster

To find or store a row in a hash cluster, Oracle Database applies the hash function to the cluster key value of the row. The resulting hash value corresponds to a data block in the cluster, which the database reads or writes on behalf of the issued statement.

Hashing is an optional way of storing table data to improve the performance of data retrieval. Hash clusters may be beneficial when the following conditions are met:

• A table is queried much more often than modified.

• The hash key column is queried frequently with equality conditions, for example, WHERE department_id=20. For such queries, the cluster key value is hashed. The hash key value points directly to the disk area that stores the rows.

• The number of rows in the table during its lifetime can reasonably be guessed.

Hash Cluster Creation

When you create a hash cluster, you use the same CREATE CLUSTER statement as for an indexed cluster, you you must add the HASHKEYS parameter. Example 2-9 shows the creation of a hash cluster, specifying department_id as the hash key. In this example, HASHKEYS specifies the number of departments that are likely to exist.

Example 2-9 Hash Cluster

CREATE CLUSTER employees_departments_cluster
   (department_id NUMBER(4))
SIZE 8192 HASHKEYS 100;

Afterward, you create the employees and departments tables in the cluster and load data just as in the indexed cluster described in Example 2-8.

In this scenario, users frequently execute queries such as the following, entering different department ID numbers for p_id:

SELECT *
FROM   employees
WHERE  department_id = :p_id;
 
SELECT * 
FROM   departments 
WHERE  department_id = :p_id;

SELECT * 
FROM   employees e, departments d 
WHERE  e.department_id = d.department_id
AND    d.department_id = :p_id;

Assume that a user executes the first query, specifying 20 as the department ID. The database uses the value 20 as input for a hash function and then locates the block that contains the rows for employees in department 20.

Figure 2-6 depicts a hash cluster segment as a horizontal row of blocks. As shown in the diagram, it is possible for each query to retrieve the data in a single I/O.

Figure 2-6 Retrieving Data from a Hash Cluster

Description of "Figure 2-6 Retrieving Data from a Hash Cluster"

A limitation of hash clusters is the unavailability of range scans on nonindexed cluster keys (see "Index Range Scan"). Assume that no separate index exists for the hash cluster created in Example 2-9. A query for departments with IDs between 20 and 100 cannot use the hashing algorithm because it cannot hash every possible value between 20 and 100. Because no index exists, the database must perform a full scan.

See Also:

Oracle Database Administrator's Guide to learn how to create and manage hash clusters