Physical Storage in Oracle

an Oracle database consists of one or more logical storage units called tablespaces
each tablespace in a database consists of one or more physical files called datafiles; datafiles are standard operating system files; a particular datafile is associated with only one tablespace

tablespaces can be used to control disk space allocation for database data, set quotas for users, allocate database storage across devices to improve performance
a tablespace is created with the CREATE TABLESPACE command; default storage specifications (extent size details) and the physical data file(s) are specified in this command

a segment is the space allocated within a tablespace for a particular logical storage structure
for example, each table is stored in a segment
also have index segments, temporary segments and rollback segments

SYSTEM tablespace - contains the system catalogs, stored PL/SQL programs (includes triggers); this tablespace must be allocated
USER tablespace - used to store user data
TEMPORARY tablespace - used for temporary storage - for example, for sorts when SQL is processing ORDER BY, GROUP BY, joins and when an index is being created

both the CREATE TABLE and CREATE INDEX statements include an optional clause in which to specify the tablespace
if this clause is not coded, the object being created is stored in the default tablespace (a default tablespace is specified when a user account is created)
in the CREATE TABLE or CREATE INDEX command, use the TABLESPACE clause to specify a tablespace other than the default:

CREATE TABLE table-name ...... TABLESPACE tablespace-name;
CREATE INDEX index-name.... TABLESPACE tablespace-name;

PRIMARY KEY (column-list)
USING INDEX TABLESPACE tablespace-name

the size of a data block is set when the database is created; should be a multiple of the operating system's page size
a data block contains:
header information: block address, type of data contained in the block
table directory: specifies what tables have data in the block
row directory: information on rows in the block
free space: to allow for insertions and updates; the amount of free space can be set in the CREATE TABLE, CREATE INDEX statements
row data: table data (or index data)
each table row is stored in one or more "row pieces"
if the row fits in a data block, it is stored as a single row piece; otherwise, stored in multiple row pieces in more than one data block that are chained together

a rowid is associated with each row; this is a physical address for the row; for rows that are comprised of more than one row piece, the rowid is the address of the first row piece
the ROWID has the following format: OOOOOOFFFBBBBBBRRR WHERE:
OOOOOO is the object number - identifies the database segment
FFF is the datafile number; file numbers are unique within a database
BBBBBB is the data block number that contains the row; the block number is relative to the datafile
RRR is the row within the block
every table has a "pseudocolumn" called ROWID
this column can not be modified by users but can be displayed with a SELECT statement using the reserved word ROWID

CREATE [ UNIQUE | BITMAP ] INDEX .....

the index is either stored in the user's default tablespace or in the tablespace specified in the CREATE INDEX statement
the index does not have to be in the same tablespace as the table data
to improve performance, the index tablespace should be mapped to a different disk drive

in a standard B-tree index, the bottom level of the index contains rowids of the corresponding data rows in the data component
in an index-organized table, the table row data is physically stored with the index data
provides good access by the primary key

clusters group data from more than one table
improves access for data that is retrieved together, for example in joins
the cluster key is the column (or columns) that the clustered tables have in common
this is specified when the cluster is created - CREATE CLUSTER statement
all rows with the same cluster key are stored in the same data block
once the cluster has been created, the tables to be included in the cluster are created; their CREATE TABLE statements will include a CLUSTER clause which tells Oracle that the table to be included in a cluster and specifies the cluster column

CREATE CLUSTER cluster-name (column data-type);

CREATE INDEX index-name ON CLUSTER cluster-name
[TABLESPACE tablespace-name];

CREATE TABLE table-name ..... CLUSTER cluster-name (column);

the rows in a hash cluster are stored together based on their hash value (an index cluster stores data together based on the cluster key)
Oracle provides an internal hash function that can be used or the user can specify the hash function to be used:
a unique column that is uniformly distributed over its range of values can be used as the hash value
any valid SQL expression involving a column or columns can be specified as the hash key