MANAGING TABLES:

There are several methods for storing user data in an Oracle database:
• Regular tables
• Partitioned tables
• Index-organized tables
• Clustered tables

Regular Table
A regular table (generally referred to as a “table”) is the most commonly used form of storing user data. This is the default table and is the main focus of discussion in this lesson. A
database administrator has very limited control over the distribution of rows in an unclustered
table. Rows can be stored in any order depending on the activity on the table.

Partitioned Table
A partitioned table enables the building of scalable applications. It has the following
characteristics:
• A partitioned table has one or more partitions, each of which stores rows that have been
partitioned using range partitioning, hash partitioning, composite partitioning, or list
partitioning.
• Each partition in a partitioned table is a segment and can be located in a different
tablespace.
• Partitions are useful for large tables that can be queried or manipulated using several
processes concurrently.
• Special commands are available to manage partitions within a table.

Index-Organized Table
An index-organized table is like a heap table with a primary key index on one or more of its columns. However, instead of maintaining two separate storage spaces for the table and a Btree index, an index-organized table maintains a single B-tree containing the primary key of the table and other column values. An overflow segment may exist due to the PCTTHRESHOLD value being set and the result of longer row lengths requiring the overflow area.
Index-organized tables provide fast key-based access to table data for queries involving exact matches and range searches.
Also, storage requirements are reduced because key columns are not duplicated in the table and index. The remaining non-key columns are stored in the index unless the index entry
becomes very large; in that case, the Oracle server provides an OVERFLOW clause to handle
the problem.

Clustered Table
A clustered table provides an optional method for storing table data. A cluster is made up of a
table or group of tables that share the same data blocks, which are grouped together because
they share common columns and are often used together.

Clusters have the following characteristics:
• Clusters have a cluster key, which is used to identify the rows that need to be stored
together.
• The cluster key can consist of one or more columns.
• Tables in a cluster have columns that correspond to the cluster key.
• Clustering is a mechanism that is transparent to the applications using the tables. Data
in a clustered table can be manipulated as though it were stored in a regular table.
• Updating one of the columns in the cluster key may entail physically relocating the row.
• The cluster key is independent of the primary key. The tables in a cluster can have a
primary key, which may be the cluster key or a different set of columns.
• Clusters are usually created to improve performance. Random access to clustered data
may be faster, but full table scans on clustered tables are generally slower.
• Clusters renormalize the physical storage of tables without affecting the logical
structure.

Scalar data type:

Fixed-length character

 CHAR and NCHAR
 upper limit of 2,000 bytes per row.
 The default is 1 character or 1 byte, depending on the character set.

Variable-length character

 VARCHAR2 and NVARCHAR2
 can vary in size for each row, up to 4,000 bytes




Numeric Data

Numbers in an Oracle database are always stored as variable-length data.
They can store up to 38 significant digits. Numeric data types require:
– One byte for the exponent
– One byte for every two significant digits in the mantissa
– One byte for negative numbers if the number of significant digits is less than 38
Bytes

DATE Data Type

The Oracle server stores dates in fixed-length fields of seven bytes. An Oracle DATE always includes the time.

TIMESTAMP Data Type

This data type stores the date and time including fractional seconds up to 9 decimal places. TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE can use time zones to factor items such as Daylight Savings Time. TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE can be used in primary keys, TIMESTAMP WITH TIME ZONE can not.

RAW Data Type

This data type enables the storage of small binary data. The Oracle server does not perform character set conversion when RAW data is transmitted across machines in a network or if RAW data is moved from one database to another using Oracle utilities. The number of bytes needed to store the actual column value, and can vary in size for each row, up to 2,000 bytes.

Long, Long Raw and Large Object (LOBs) Data Types

Oracle provides six data types for storing LOBs:
• CLOB and LONG for large fixed-width character data
• NCLOB for large fixed-width national character set data
• BLOB and LONG RAW for storing unstructured data
• BFILE for storing unstructured data in operating system files.
LONG and LONG RAW data types were previously used for unstructured data, such as
binary images, documents, or geographical information, and are primarily provided for
backward compatibility. These data types are superseded by the LOB data types. LOB data
types are distinct from LONG and LONG RAW, and they are not interchangeable. LOBs will
not support the LONG application programming interface (API), and vice versa.
Varying Arrays (VARRAY)

Varying arrays are useful to store lists that contain a small number of elements, such as phone numbers for a customer.

VARRAYS have the following characteristics:

• An array is an ordered set of data elements.
• All elements of a given array are of the same data type.
• Each element has an index, which is a number corresponding to the position of the
element in the array.
• The number of elements in an array determines the size of the array.
• The Oracle server allows arrays to be of variable size, which is why they are called
VARRAYs, but the maximum size must be specified when declaring the array type.

Nested Tables

Nested tables provide a means of defining a table as a column within a table. They can be used to store sets that may have a large number of records such as number of items in an order.
Nested tables generally have the following characteristics:

• A nested table is an unordered set of records or rows.
• The rows in a nested table have the same structure.
• Rows in a nested table are stored separate from the parent table with a pointer from the
corresponding row in the parent table.
• Storage characteristics for the nested table can be defined by the database administrator.
• There is no predetermined maximum size for a nested table.

Relationship Data Types (REFs)

Relationship types are used as pointers within the database. The use of these types requires the Objects option. As an example, each item that is ordered could point to or reference a row in the PRODUCTS table, without having to store the product code.