Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 26 27 28 29 30 31 < 32 > 33 34 35 36 37 38 .. 218 >> Next


Populating Tables

COMPRESS: State of Presence Bits per Column

The system sequentially stores values for a column, as encountered, in the area of the row allocated for non-compressed field values. However, if compression is being used, presence bits are added to the row header of each row to specify how compression is used for that row.

These bits essentially point to the compressed values that are stored in the table header. For example, assume the column JobTitle in a CREATE TABLE statement is defined as:

CREATE TABLE Employee (EmpNo INTEGER ...

JobTitle CHAR(30) COMPRESS ('clerk', 'manager' , 'programmer') ...);

If a row to be inserted contains "clerk" in the JobTitle field, that value is compressed to zero space, a zero bit is written into the appropriate presence bit position, and two compress bits are written followed by the presence bit with a value of 1.

If you specify another compressible column (for example, Column X) and place it before JobTitle in the CREATE TABLE statement, the system checks the status of the COMPRESS presence bits for column X before it accesses a non-compressed value for JobTitle.

Thus, the state of the presence bits in each row depend on the attributes of the column and the value in that row.

Query the DBC.Columns view to check the column definition of a table or view. The Columns.Compressible field displays YES if the column being reported is to be compressed. In this case, the Columns.CompressValue field displays the value or list of values defined for compression. If the reported value is:

A NULL, only NULLS are compressed

A value, both NULLs and the value are compressed

IF you want more information on . THEN see .
using NULLs "Default Value Control Phrases" in Teradata RDBMS SQL Reference, Volume 3
using the COMPRESS phrase "COMPRESS" under "Default Value Control Phrases" in Teradata RDBMS SQL Reference, Volume 3 "Value Compression" in Teradata RDBMS Database Design
whether reduced storage outweighs the row overhead of value compression "Database-Level Considerations" in Teradata RDBMS Database Design

2 - 10 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Populating Tables

IF you want more information on . THEN see .
planning, creating, and indexing your data tables Teradata RDBMS SQL Reference, all volumes Teradata RDBMS Performance Optimization
dictionary tables and system views Chapter 4: "Using Data Dictionary Tables and Views" Teradata RDBMS Data Dictionary

2 - 10

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Global Temporary and Volatile Tables

Global Temporary and Volatile Tables

This section provides information on global temporary tables and volatile tables and offers comparison summaries of the various types of temporary tables. It discusses:

Materializing global temproray tables

Volatile tables

Volatile tables versus derived tables

Global tables versus volatile tables

Note: You can collect statistics on global temporary tables, materialized or not. However, you cannot collect statistics on volatile tables. (For details on the COLLECT STATISTICS statement and its variety of capabilities, see"Collecting Optimizer Statistics" on page 2-45 and Teradata RDBMS SQL Reference, Volume

4.)

Global Temporary Tables

Global temporary tables allow a query result to be stored in a temporary table for use in subsequent queries. This helps improve system performance by:

Reducing the number of times the system needs to re-execute the query.

Reducing the number of times the user needs to create and drop tables.

Use the CREATE GLOBAL TEMPORARY TABLE statement to create a global table. Global temporary tables have a persistent definition that is stored in the Data Dictionary, but their contents do not persist across sessions.

Also, their contents are not saved when a transaction completes, unless you specify it when you create the table. To save table rows after a transaction ends, specify ON COMMIT PRESERVE ROWS as the last keywords in the CREATE statement.

This syntax is the similar to CREATE VOLATILE TABLES (see "Volatile versus Derived Tables" on page 2-19).

The following options are not available for global temporary tables:

Referential Constraints

Permanent Journaling

Identity column

Partitioning

Space usage is charged to the login user's temporary space.

You can materialize up to 2,000 instances of global temporary tables in a single session, as long as your login user has adequate temporary space.

Teradata RDBMS Database Administration

2 - 17 Chapter 2: Building the Teradata RDBMS

Global Temporary and Volatile Tables

Materializing Global Temporary Tables

You materialize a global temporary table locally by referencing it in an SQL data manipulation language (DML) statement. To do this, you must have the appropriate privilege on the base temporary table or on the containing database, as required by the statement that materializes the table.
Previous << 1 .. 26 27 28 29 30 31 < 32 > 33 34 35 36 37 38 .. 218 >> Next