Database Structure and Space Management

This site describes the architecture of an ORACLE database, including the physical and logical structures that constitute a database.

Relational Database Management Systems

Database management systems have evolved from hierarchical to network to relational models. Today, the most widely accepted database model is the relational model. The relational model has three major aspects:

1. Structures

Structures are well defined objects that store the data of a database. Structures and the data contained within them can be manipulated by operations.

2. Operations

Operations are clearly defined actions that allow users to manipulate the data and structures of a database. The operations on a database must adhere to a pre defined set of integrity rules.

3. Integrity Rules

Integrity rules are the laws that govern which operations are allowed on the data and structures of a database. Integrity rules protect the data and the structures of a database.

An ORACLE database is a collection of data that is treated as a unit. This general purpose of a database is to store and retrieve related information.

Database Structure

An ORACLE database has both a physical and logical structure. By separating physical and logical database structure, the physical storage of data can be managed without affecting the access to logical storage structures.

Physical Database Structure

An ORACLE database's physical structure is determined by the operating system files that constitute the database. Each ORACLE database is comprised of three types of files: one or more data files, two or more redo log files, and one or more control files. The files of a database provide the actual physical storage for database information.

Logical Database Structure

An ORACLE database's logical structure is determined by

one or more tablespaces
the database's schema objects(e.g., tables, views, indexes, clusters, sequences, stored procedures)

The logical storage structures, including tablespaces, segments, and extents, dictate how the physical space of database is used.

Logical Structures

The following sections explain logical structures, including tablespaces, schema objects, data blocks, extents and segments.

Tablespaces:

A database is divided into logical storage units called tablespaces. A tablespace is used to group related logical structures together. For example, tablespaces commonly group all of an application's objects in simply certain administrative operations.

Database, Tablespaces, and data files

The relationship among databases, tablespaces, and data files is illustrated bellow This figure illustrates:

1. Each database is logically divided into one or more tablespaces.
2. One or more data files are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace.
3. The combined size of a tablespace's data files in the total storage capacity of the tablespace.
4. The combined storage capacity of a database's tablespaces is the total storage capacity of the database(6M)

How Data is Stroed in the Database?

Schemas and Schema Objects:

A schema is a collection of objects.. Schema objects are the logical structures. that directly refer to the database's data. Schema objects include such structures as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.

Tables

A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data.

Table data is stored in rows and columns. Every table is defined with a table name and set of columns A synonym is an alias for a table, view, sequence, or program unit. A synonym is not actually an object itself, but instead is a direct reference to an object. Data base administrators most often create public synonyms that make the base schema object available for general, system wide use by any database user.

Indexes, Clusters, and Hash Clusters

Indexes, Clusters, and hash clusters are optional structures associated with tables, which can be created to increase the performance of data retrieval.

Indexes are created to increase the performance of data retrieval, just as the index in this Manual helps you locate specific information faster than if there were no index, an ORACLE index provides a faster access path to table data. When processing a request, ORACLE can use some or all of the available indexes to efficiently locate the requested rows. Indexes are useful when applications often query a table for a range of rows.

Indexes are created on one or more columns of a table. Once created, an index is automatically maintained and used by ORACLE . Changes to table data(such as adding new rows, updating rows, or deleting rows) are automatically incorporated into all relevant indexes with complete transparency to the users.

Clusters are an optional method of storing table data. Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.

The related columns of the tables in a cluster is called cluster key.

The cluster key is indexed so that rows of the cluster can be retrieved with a minimum amount of 1/0. Because the data in a cluster key of an index cluster is stored only once for multiple tables, clusters may store a set of tables more efficiently than if the tables were stored individually . Data stored in the clustered table is accessed via SQL in the same way as data stored in a non clustered table.

Hash clusters also cluster table data in a manner similar to normal, index clusters. However, a row is stored in a hash cluster based on the result of applying a hash function to the row's cluster key value. All rows with the same hash key value are stored together on disk.

Hash clusters are better choice than using an indexed table or index cluster when a table is often queried with equality queries (e.g., return all rows for department 10). For such queries, the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

Database Links
A database link is a named object that describes a "path" from one database to another.

Data Blocks, Extents, and Segments

Oracle Data Blocks

An ORACLE database's data is stored in data blocks. One data block corresponds to a specific number if bytes of physical database space on disk.' A data block size is specified for each ORACLE database when the database is created. A database uses and allocates free database space in ORACLE data blocks

Extents

The next level of logical database is called segment. An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information.

Segments

The level of logical database storage above an extent is called a segment. A segment is a set of extents allocated for a certain logical structure. For example, the different types of segments include:

1. Data segment

All of the table's data is stored in the extents of its data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster's of data segment.

2. Index Segment

Each index has an index segment that stores all of its data.
3. Rollback segment

One or more rollback segments are created by the database administrator for a database to store "undo" information.

Temporary Segment

Temporary segments are created by ORACLE. When a SQL statement needs a temporary work area to complete execution. When the statement finishes execution, the temporary segments extents are returned to the system for future use.

Physical Structures
The physical structure of the database includes datafile, redolog files, and control files.

Data Files

Every ORACLE database has one or more physical data files. A database's data files contain all the database data. The data of the logical database structures such as tables and indexes is physically stored in the data files allocated for a database.

The following are characteristics of data files:

1. A data file can be associated with only one database.
2. Once created, a data file cannot change in size.
3. One or more data files from a logical unit of database storage called a tables pace.

Redo Log Files

Every ORACLE database has a set of two or more redo log files. The set of redo log files for , a database is collectively known as the database's redo log.. The primary function of the redo log is to record all changes made to data. The information in a redo log file is used only to recover the database from a system or media failure that prevents database from being written to database's data files.

Control Files

Every ORACLE database has a control file. A control file records the physical structure of the database. For example, it contains the following types of information:

database name
names and locations of a database's data files and redo log files
time stamp of database creation
Like the redo log, ORACLE allows the control file to be mirrored for the protection of the control file.

If the physical makeup of the database is altered for example, a new data file or redo log file is created, the database's control file is automatically modified' by ORACLE to reflect the change.

An Oracle Instance

Every time a database is started, an SGA is allocated and ORACLE background process are started. The combination of these process and memory process is called an ORACLE instance. The figure below illustrates a multiple process ORACLE instance.

The Oracle Parallel Server Multiple Instance Systems

Some hardware architecture (for example, loosely coupled processors allow multiple computers to share access to data, software, or peripheral devices. ORACLE with the Parallel Server option can take advantage of such architecture by running multiple instances that "share" a single physical database. In appropriate applications, the ORACLE Parallel Server allows access to a single database by the users on multiple machines with increased performance.

Domain Name Search