Archive for the ‘Tables’ Category

Table and Index Partitioning

Oracle Partitioning Quick Primer

Partitioned Tables and Indexes

Advertisements

Managing Tables

Creating a Table

The CREATE TABLE command is used to create relational tables or object tables.

Relational table: This is the basic structure to hold user data.

Object table: Is a table that uses an object type for a column definition. An object table is a table that is explicitly defined to hold the object instance of a particular type.

 

The example below creates a DEPARTMENTS table in a data dictionary managed tablespace.

SQL> CREATE TABLE hr.departments( department_id NUMBER(4),

department_name VARCHAR2(30), manager_id NUMBER(6), location_id NUMBER(4))

STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 5)

TABLESPACE data;

STORAGE clause:

The STORAGE clause specifies storage characteristics for the table. The storage allocated for the first extent is 200 KB. When a second extent is required it will be created at 200 KB also defined by the NEXT value. When a third extent is required, it will be created at 200 KB because the PCTINCREASE has been set to zero. The maximum amount of extents that can be used is set at five, with the minimum set to one.

•MINEXTENTS: This is the minimum number of extents that is to be allocated.

•MAXEXTENTS: This is the maximum number of extents to be allocated. If MINEXTENTS is specified with a value greater than one and the tablespace contains more than one datafile, the extents will be spread across the different datafiles.

•PCTINCREASE: This is the percent of increase in extent size after NEXT extent and thereafter.

 

PCTFREE must be a value from zero to ninety-nine. A value of zero means that the entire block can be filled by inserts of new rows. The default value is 10. This value reserves 10% of each block for updates to existing rows and allows inserts of new rows to fill a maximum of 90% of each block.

•PCTUSED: Specifies the minimum percentage of used space that is maintained for each data block of the table. A block becomes a candidate for row insertion when its used space falls below PCTUSED. PCTUSED is specified as integer from zero to ninety-nine and defaults to 40.

The combination of PCTFREE and PCTUSED determines whether new rows will be inserted into existing data blocks or into new bocks. The sum of these two must be equal to or less than 100. These parameters are used to utilize space within a table more efficiently.

Note: PCTUSED, FREELISTS, and FREELIST GROUPS are deprecated with the Oracle9i feature Automatic Segment-Space Management. Refer to the “Storage Structures and Relationship” lesson for details regarding this feature.

•INITRANS: Specifies the initial number of transaction entries allocated within each data block allocated to the table. This value can range from 1-255 and default to one INITRANS: Ensures that a minimum number of concurrent transactions can update the block. In general, this value should not be changed from its default.

•MAXTRANS: Specifies the maximum number of concurrent transaction that can update a data block allocated to the table. This limit does not apply to queries. The value can range from 1-255 and the default is a function of the data block size.

 

Creating Temporary Tables

Temporary tables can be created to hold session-private data that exists only for the duration of a transaction or session.

The CREATE GLOBAL TEMPORARY TABLE command creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction, while for session-specific temporary tables, data exists for the duration of the session. Data in a session is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The clauses that control the duration of the rows are:

  • ON COMMIT DELETE ROWS: To specify that rows are only visible within the transaction
  • ON COMMIT PRESERVE ROWS: To specify that rows are visible for the entire session

You can create indexes, views, and triggers on temporary tables and you can also use the Export and Import utilities to export and import the definition of a temporary table. However, no data is exported, even if you use the ROWS option. The definition of a temporary table is visible to all sessions.

Setting PCTFREE

A higher PCTFREE affords more room for updates within a database block. Set a higher value if the table contains:

  • Columns that are initially NULL and later updated with a value
  • Columns that are likely to increase in size as a result of an update

A higher PCTFREE will result in lower block density—each block can accommodate fewer rows. The formula specified above ensures that there is enough free space in the block for row growth.

Setting PCTUSED

Set PCTUSED to ensure that the block is returned to the free list only when there is sufficient space to accommodate an average row. If a block on the free list does not contain sufficient space for inserting a row, the Oracle server looks up the next block on the free list. This linear scan continues until either a block with sufficient space is found or the end of the list is reached. Using the formula given reduces the time taken to scan the free list by increasing the probability of finding a block with the required free space.

Changing Storage and Block Utilization Parameters

Some of the storage parameters and any of the block utilization parameters can be modified by using the ALTER TABLE command.

Syntax:

ALTER TABLE hr.employees

PCTFREE 20

PCTUSED 60

STORAGE NEXT (500k MINEXTENTS 2 MAXEXTENTS 100);

ALLOCATING EXTENTS MANUALLY

Extents may need to be allocated manually:

  • To control the distribution of extents of a table across files
  • Before loading data in bulk to avoid dynamic extension of tables

ALTER TABLE hr.employess

ALLOCATE EXTENT (SIZE 500K  DATAFILE ‘C:\ORACLE\DATA01.DBF’);

Moving Table To Different Tablespace

A nonpartitioned table can be moved without having to run the Export or Import utility. In addition, it allows the storage parameters to be changed. This is useful when:

  • Moving a table from one tablespace to another
  • Reorganizing the table to eliminate row migration

ALTER TABLE hr.employees move tablespace data1;

 

Adding a Table Column and Check Constraint: Example

The following statement adds a column named duty_pct of datatype NUMBER and a column named visa_needed of datatype VARCHAR2 with a size of 3 and a CHECK integrity constraint:

 ALTER TABLE countries 
   ADD (duty_pct     NUMBER(2,2)  CHECK (duty_pct < 10.5),
        visa_needed  VARCHAR2(3)); 

 

Renaming a Column: Example

ALTER TABLE customers

   RENAME COLUMN credit_limit TO credit_amount;

TRUNCATE TABLE

TRUNCATE TABLE hr.employees;

  • The effects of using this command are as follows:
  • All rows in the table are deleted.
  • No undo data is generated and the command commits implicitly because TRUNCATE TABLE is a DDL command.
  • Corresponding indexes are also truncated.
  • A table that is being referenced by a foreign key cannot be truncated.
  • The delete triggers do not fire when this command is used.

DROP TABLE

DROP TABLE hr.employees CASCADE CONSTRAINT;

When a table is dropped, the extents used by the table are released. If they are contiguous, they may be coalesced either automatically or manually at a later stage. The CASCADE CONSTRAINTS option is necessary if the table is the parent table in a foreign key relationship.

 

Dropping a Column

Dropping a column can take a significant amount of time because all the data for the column is deleted from the table.

Dropping a column can be time consuming and requires a large amount of undo space. While dropping columns from large tables, checkpoints can be specified to minimize the use of undo space. In the example in the slide, a checkpoint occurs every 1,000 rows. The table is marked INVALID until the operation completes.

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

If the instance fails during the operation, the table remains INVALID on start up, and the operation will have to be completed.

SQL> ALTER TABLE hr.employees DROP COLUMNS CONTINUE;

Using the UNUSED Option

Instead of removing a column from a table, the column can be marked as unused and then removed later. This has the advantage of being relatively quick, as it does not reclaim the disk space because the data is not removed. Columns that are marked as unused can be removed at a later time from the table when there is less activity on the system.

Unused columns act as if they are not part of the table. Queries cannot see data from unused columns. In addition, the names and data types of those columns are not displayed when a DESCRIBE command is executed. A user can add a new column with the same name as an unused column. Marking column COMMENTS as unused:

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

Dropping UNUSED column

ALTER TABLE hr.employees DROP UNUSED COLUMNS CHECKPOINT 1000;

The following query shows that the table EMPLOYEES owned by HR has one unused column:

SQL > SELECT * FROM dba_unused_col_tabs;

OWNER TABLE_NAME COUNT

—– ————– ——

HR EMPLOYEES 1

To identify tables that have partially completed DROP COLUMN operations the DBA_PARTIAL_DROP_TABS view can be queried.

SQL > SELECT * FROM dba_partial_drop_tabs;

OWNER TABLE_NAME COUNT

—– ————– ——

no rows selected

Obtaining Table Information

Information about tables can be obtained from the data dictionary.

  • DBA_OBJECTS
  • DBA_TABLES

To obtain the data object number and the location of the table header for all tables owned by HR, use the following query:

SQL > SELECT table_name FROM dba_tables WHERE owner = ‘HR’;

 

 
 
For details see Alter table link.

Row Format

Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. If an entire row can be inserted into a single data block, then Oracle stores the row as one row piece. However, if all of a row’s data cannot be inserted into a single data block or if an update to an existing row causes the row to outgrow its data block, then Oracle stores the row using multiple row pieces. A data block usually contains only one row piece for each row. When Oracle must store a row in more than one row piece, it is chained across multiple blocks.

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

Each row piece, chained or unchained, contains a row header and data for all or some of the row’s columns. Individual columns can also span row pieces and, consequently, data blocks. Figure 5-3 shows the format of a row piece:

Figure 5-3 The Format of a Row Piece

Description of Figure 5-3 follows
Description of "Figure 5-3 The Format of a Row Piece"

The row header precedes the data and contains information about:

  • Row pieces

  • Chaining (for chained row pieces only)

  • Columns in the row piece

  • Cluster keys (for clustered data only)

A row fully contained in one block has at least 3 bytes of row header. After the row header information, each row contains column length and data. The column length requires 1 byte for columns that store 250 bytes or less, or 3 bytes for columns that store more than 250 bytes, and precedes the column data. Space required for column data depends on the datatype. If the datatype of a column is variable length, then the space required to hold a value can grow and shrink with updates to the data.

To conserve space, a null in a column only stores the column length (zero). Oracle does not store data for the null column. Also, for trailing null columns, Oracle does not even store the column length.

Note:

Each row also uses 2 bytes in the data block header’s row directory.

Clustered rows contain the same information as nonclustered rows. In addition, they contain information that references the cluster key to which they belong.

See Also:

Rowids of Row Pieces

The rowid identifies each row piece by its location or address. After they are assigned, a given row piece retains its rowid until the corresponding row is deleted or exported and imported using Oracle utilities. For clustered tables, if the cluster key values of a row change, then the row keeps the same rowid but also gets an additional pointer rowid for the new values.

 

Row Migration and Chaining

Row Migration

If PCTFREE is set to a low value, there may be insufficient space in a block to accommodate a row that grows as a result of an update. When this happens, the Oracle server will move the entire row to a new block and leave a pointer from the original block to the new location. This process is referred to as row migration. When a row is migrated, input/output (I/O) performance associated with this row decreases because the Oracle server must scan two data blocks to retrieve the data.

Row Chaining

Row chaining occurs when a row is too large to fit into any block. This might occur when the row contains columns that are very long. In this case, the Oracle server divides the row into smaller chunks called row pieces. Each row piece is stored in a block along with the necessary pointers to retrieve and assemble the entire row. Row chaining can be minimized by choosing a higher block size or by splitting the table into multiple tables with fewer columns, if possible.

ROWID Format

ROWID Format

Extended ROWID

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

Data object number: Is assigned to each data object, such as table or index when it is created, and it is unique within the database

Relative file number: Is unique to each file within a tablespace

Block number: Represents the position of the block, containing the row, within the file

Row number: Identifies the position of the row directory slot in the block header

Internally, the data object number needs 32 bits, the relative file number needs 10 bits, block number needs 22 bits, and the row number needs 16 bits, adding up to a total of 80 bits or 10 bytes.

An extended ROWID is displayed using a base-64 encoding scheme, which uses six positions for the data object number, three positions for the relative file number, six positions for the block number, and three positions for the row number. The base-64 encoding scheme uses characters A-Z, a-z, 0-9, and /. This is a total of 64 characters, as in the following example:

SQL> SELECT department_id, rowid FROM hr.departments;

DEPARTMENT_ID ROWID

————- ——————

10 AAABQMAAFAAAAA6AAA

20 AAABQMAAFAAAAA6AAB

30 AAABQMAAFAAAAA6AAC

40 AAABQMAAFAAAAA6AAD

50 AAABQMAAFAAAAA6AAE

60 AAABQMAAFAAAAA6AAF

In this example:

•AAABQM is the data object number

•AAF is the relative file number

•AAAAA6 is the block number

•AAA is the row number for the department with ID = 10

Locating a row using ROWID:

Because a segment can only reside in one tablespace, by using the data object number, the Oracle server can determine the tablespace that contains a row.

The relative file number within the tablespace is used to locate the file, the block number is used to locate the block containing the row, and the row number is used to locate the row directory entry for the row.

The row directory entry can be used to locate the beginning of the row.

Thus, ROWID can be used to locate any row within a database.

Restricted Rowids

SELECT ROWID, last_name FROM employees 
    WHERE department_id = 30; 

can return the following row information:

ROWID              ENAME 
------------------ ---------- 
00000DD5.0000.0001 KRISHNAN 
00000DD5.0001.0001 ARBUCKLE 
00000DD5.0002.0001 NGUYEN 

As shown, a restricted rowid’s VARCHAR2/hexadecimal representation is in a three-piece format, block.row.file:

  • The data block that contains the row (block DD5 in the example). Block numbers are relative to their datafile, not tablespace. Therefore, two rows with identical block numbers could reside in two different datafiles of the same tablespace.
  • The row in the block that contains the row (rows 0, 1, 2 in the example). Row numbers of a given block always start with 0.
  • The datafile that contains the row (file 1 in the example). The first datafile of every database is always 1, and file numbers are unique within a database.