my notes...

Regular Tables

:

The default table. Rows can be stored in any matter depending upon the activity in the database.

Partitioned Tables

:

These enable the building of scalable applications.

Characteristics:

• Partitioned using range, hash, composite or list partitioning.

• Each partition is a segment and can located in a different tablespace.

• Special command available to manage partitions within a table.

• Useful for large table which can be queried & manipulated using several processes concurrently.

Index Organized Tables

:

It is like a heap table with primary key on one or more of its columns. However instead of maintaining two separate storages, an IOT maintains a single B-tree containing the primary key and the rest of the column values.

An overflow segment may exist due to PCTTHRESHOLD value being set and the result of longer row lengths requiring the overflow area.

Characteristics:

• provides fast key-based access to table data for queries involving exact matches and range searches.

• lower storage requirements as columns not duplicated in separate index.

• the remaining non-key columns are stored in the index unless the index entry becomes too large, whereby Oracle provides an OVERFLOW clause to handle the problem.

Clustered Tables

:

A cluster is made up of a table or group of table that share the same data blocks, which are grouped together because they share common columns and are often used together.

Characteristics:

• Clustering is a mechanism which is transparent to the applications using the tables. Data in a clustered table can be manipulated as though it was stored in a regular table.

• Clusters normalizes the physical storage of the tables without affecting the logical structure.

• Clusters are usually created to improve performance. Random access to clustered data may be faster, but full table scan on clustered tables are generally slow.

• Updating one of the columns in the cluster key can cause row migration.

  Cluster key:

• Clusters have a cluster key which is used to identify the rows.

• The cluster key can consist of one or more columns.

• Tables in a cluster have columns corresponding to the cluster key.

• The cluster key is independent of the primary key. The tables in a cluster can have primary key which can be the cluster key or may be not.

Note : Display uses base-64 encoding scheme using following character sets : A-Z,a-z,0-9, and /. That is a total 64 characters.

Segment no.(Data object No.)

^{Points to tablespace} >

Tablespace (since a segment can reside in only one tablespace)

^{Relative file no.} >

Relative Datafile of the tablespace

^{Block no.} >

Block No.

^{Row ID .} >

Row directory entry for the row

^Row >

Beginning of the row

Notes :

• Display uses base-16 format.

• Prior to Oracle 7, Oracle required datafile numbers to be unique within the database

• Oracle 8.0.3 had introduced a 16 byte extended ROWID temporarily. Thereafter the 10bytes format was introduced.

Uses :

• Still used in non-partitioned indexes on non-partitioned tables where all index entries refer to rows within the same segment.

Notes :

• Columns are generally stored in the order in which they are defined.

• Any trailing NULL column are not stored. A single byte for column length is required for

• non-trailing NULL columns.

• Adjacent rows don't require any space in between them. Each row in a block has an entry in the row directory. The directory slot points to the beginning of the row.

Note : The above is a subset of the CREATE TABLE clause.

NEXT

:

PCTINCREASE

:

MINEXTENT

:

MAXEXTENT

:

PCTFREE

:

PCTUSED

:

Deprecated from Oracle 9i together with FREELIST, FREELIST GROUPS

INTRANS

:

MAXTRANS

:

TABLESPACE

:

Syntax

Example

ALTER TABLE table_name
{[storage_clause]
[INITRANS integer]
[MAXTRANS integer]}

ALTER TABLE hr.employees
PCTFREE 20
PCTUSED 60
STORAGE (NEXT 100K MINEXTENT 5 MAXEXTENT 50);

MINEXTENT

:

Can be changed to any value <= to the current no. of extents. Is has no immediate effect but will be used if the table is TRUNCATED.

MAXEXTENT

:

Can be changed to any value >= to the current no. of extents. It can also be set to UNLIMITED.

Restrictions:

• INITIAL value can't be modified.

• The value of NEXT will be rounded to a value that is a multiple of the block size >= to the value specified.

• Place tables in separate tablespace from those holding undo segments, indexes & temporarily segments.

• Use locally managed tablespaces to avoid fragmentation

• Use few standard extent sizes to reduce tablespace fragmentation

:

Rows only visible within transaction.

:

Rows visible during entire session.

PCTFREE

:

Average increase in row_size *100

Average row Size

i.e. The free space should be able to accommodate average row size increases

Note: Higher PCTFREE is used to accommodate updates within a database block. Higher PCTFREE results in lower block density- fewer rows per block.

Use higher value for:

• Columns that are initially NULL and latter filled up

• Columns that are likely to increase in size with updates.

PCTUSED

:

100 – PCTFREE - Average row size

i.e. Whenever there is space to accommodate a single new row.

Note: Set so that the block is returned to the free list only when there is sufficient space to accommodate an average row. If it does not, then Oracle Server looks up the 2^nd block on the free list. This linear scan continues until a block with sufficient space is found or the end of list is reached. The value of average row can be found using ANALYZE TABLE command.

Row Migration

:

When PCTFREE is low, there may not be sufficient space in the block to accommodate a row growth due to update. In such a situation Oracle server moves the entire row to a new block & leaves a pointer from the original block to the new location. This causes decrease in I/O performance associated with this row due to two data block scans.

Chaining

:

Row chaining occurs when a row is too large to fit into any block. Oracle server divides the row into smaller chunks called row pieces. Each row piece is stored in a block along with necessary pointers to retrieve and assemble the entire row. Row chaining can be minimised by choosing a higher block size, or by splitting a table into multiple tables with fewer columns.

Syntax

Example

ALTER TABLE [schema.] tablename
ALLOCATE EXTENT [([SIZE integer [K|M]]
[DATAFILE 'filename'] )]

ALTER TABLE hr.employees
ALLOCATE EXTENT (SIZE 400K
DATAFILE 'd:/Oracle/ora90/emp1.dbf');

Purpose:

• To control distribution of extents across files.

• Before loading data in bulk to avoid dynamic extension of tables.

Notes:

• If SIZE is omitted NEXT_EXTENT from DBA_TABLES is used.

• If DATAFILE clause is not used, Oracle will allocate the extent in one of the files in the tablespace containing the table.

• Specified DATAFILE must belong to the tablespace to which the table belongs. Otherwise an error is thrown.

• The NEXT_EXTENT value in DBA_TABLES will not be affected by the manual extent allocation. Oracle server will not recalculate the size of the next extent when this command is executed.

ALTER TABLE hr.employees
MOVE TABLESPACE sktab;

Using this command a non-partitioned table can be moved without using EXPORT or IMPORT utilility. In addition, storage parameters can be changed too.

Purpose:

• Moving a table from one tablespace to another.

• Reorganizing the table to eliminate row migration.

Note: Moving the tables requires rebuilding of indexed otherwise 'index in unusable state' error is thrown.

Syntax:

Example:

TRUNCATE TABLE [schema.] tablename
[{DROP|REUSE} STORAGE];

TRUNCATE TABLE hr.employees;

Characteristics:

• All rows in the table are deleted.

• No undo data is generated and the command commits implicitly, TRUNCATE TABLE being a DDL command.

• Corresponding indexes are truncated too.

• A table which is reference by a foreign key can't be truncated.

• The delete trigger don't fire when this command is used.

Syntax:

Example:

DROP TABLE [schema.] tablename
[CASCADE CONSTRAINTS];

DROP TABLE hr.departments
CASCADE CONSTRAINTS ;

Characteristics:

• When a table is dropped, the extents used by it are released. If they are contiguous they may be coalesced either automatically or manually at a latter stage.

• CASCADE CONSTRAINTS is necessary if the table is a parent table is a foreign key relationship.

ALTER TABLE hr.employees
DROP COLUMN comments
CASCADE CONSTRAINTS CHECKPOINT 1000;

Characteristics:

• Dropping a column cleans unused and potentially space demanding columns without export or import of data and recreate indexes & constraints.

• It can take significant amount of time as all the data for the column is deleted from the table.

• Requires a lot of undo space. CHECKPOINTS can be specified to minimize the use of undo space. The the above example, a checkpoint occurs every 1000 rows.

• The table is marked invalid until the operation continues.

• If the instance fails during the operation, the table remains invalid on startup, and the operation needs to be completed. To resume interrupted drop operation:

  ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

  The above command will generate an error if the table is in a valid state.

• Before 8i it was not possible to drop a column.

ALTER TABLE hr.employees
SET UNUSED COLUMN comments
CASCADE CONSTRAINTS;

ALTER TABLE hr.employees
DROP UNUSED COLUMNS CHECKPOINT 1000;

ALTER TABLE hr.employees
DROP COLUMNS CONTINUE CHECKPOINT 1000;

Characteristics:

• Marking a column unused is relatively quick as it does not reclaim unused disk space as the data is not removed. Unused columns act as if they are not part of the table. They don't show up in the DESCRIBE command. A user can add a new column with the same name as an unused column.

• Can be especially useful when more than one columns needs to be dropped. Dropping each will require all rows of the table to be updated twice. Mark them as unused and then drop in one go.

• Dropping unused columns can be accomplished during off-peak hours to minimize load on the system.

Restrictions on dropping a Column.

Can't do the followings:

• drop a column from an object type table

• drop a column from nested tables

• drop all columns in a table

• drop a partition key column

• drop a column from tables owned by SYS

• drop a column from Index-Organized-Tables if the column is a primary key.

• A LONG or LONG RAW column that is unused but not dropped will prevent an add of a LONG or LONG RAW column to the table.

DBA_UNUSED_COL_TABS

DBA_PARTIAL_DROP_TABS

DBA_TABLES

DBA_OBJECTS