Methods for Storing Data in a Database

Different Means of Storing Data

• Regular Table
• Partitioned Table
• Index-Organized Table
• Clustered Table

Regular Table

A regular table, generally referred to as a 'table', is the most commonly used form of storing user data. The database administrator has very limited control over the distribution of rows in an un-clustered 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 one or more partitions, each of which stores rows that have been partitioned using range partitioning, hash partitioning, composite partitioning, or list partitioning. Partitions are useful for large tables that can be queried or manipulated using several processes concurrently.

Index-Organized Table

An index-organized table is like a heap table with a primary key index on one or more of its columns. It provides fast key-based access to table data for queries involving exact matches and range searches.

Clustered Table

A clustered table provides an optional method for storing table data. It 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.

Character Data, Numeric Data, CLOB, and TIMESTAMP Data Types

• Character Data
• Numeric Data
• CLOB
• TIMESTAMP Data Type

Character Data

Character data can be stored as either fixed-length or variable-length strings in the database. Fixed-length character data types, such as CHAR and NCHAR, are stored with padded blanks.

Numeric Data

Numbers in a database are always stored as variable-length data. Numeric data types require specific bytes for the exponent, significant digits, and negative numbers.

CLOB

CLOB and LONG are used for large fixed-width character data.

TIMESTAMP Data Type

This data type stores the date and time, including fractional seconds up to 9 decimal places.

NCLOB, ROWID, and UROWID Data Types

• NCLOB Data Type
• ROWID and UROWID Data Type

ROWID and UROWID Data Type

The database server provides a single datatype called the universal ROWID or UROWID. It supports ROWIDs of foreign tables and can store all kinds of ROWIDs.

Nested Tables, Extended ROWID, Restricted ROWID, and Structure of a Row

• Nested Tables
• Extended ROWID
• Restricted ROWID
• Structure of a Row

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.

Extended ROWID

An Extended ROWID needs 10 bytes of storage on disk and is displayed using 18 characters. It consists of the following components.

Restricted ROWID

Earlier database versions used the restricted ROWID format, which used only six bytes internally and did not contain the data object number.

Structure of a Row

Row data is stored in database blocks as variable-length records. Columns for a row are generally stored in the order in which they are defined, and any trailing NULL columns are not stored.

CREATE TABLE Command and Changing Storage Parameters

• CREATE TABLE Command
• Changing Storage Parameters

The CREATE TABLE command is used to create relational tables or object tables. The TABLESPACE clause specifies the tablespace where the table will be created.

Index Classification and Maintenance

• Index Classification
• Coalescing Indexes
• Index Validity, Dropping Indexes
• Rebuilding Indexes
• Syntax, Manual Allocation/De-allocation of Index Space
• Bitmap Index
• Normal B-Tree Index
• DML Operation on Indexes
• Domain Indexes
• Function-Based Indexes
• Single-Column and Concatenated Indexes
• Unused Columns, Dropping a Column, Dropping a Table, Truncating a Table

Indexes can be classified based on their logical design or on their physical implementation. Partitioned indexes are often used with partitioned tables to improve scalability and manageability.

B-Trees, Index Trees, and Bitmap Indexes

B-Trees and index trees contain root, branch blocks, and leaf nodes. Bitmap indexes are used for specific data operations.

Index Maintenance and Operations

Various operations such as dropping, rebuilding, and coalescing indexes are used to maintain index performance and integrity.