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 .. 27 28 29 30 31 32 < 33 > 34 35 36 37 38 39 .. 218 >> Next


Any number of different sessions can materialize the same table definition, but the contents change depending on the DML statements applied during the course of a session.

Access rights are not checked on the materialized instances of any global temporary tables because those tables exist only for the duration of the session in which they are materialized.

Volatile Tables

You need to create a volatile table using the CREATE VOLATILE TABLE statement during each session in which you use it.

A volatile table does not have a persistent definition; the definition is stored in memory cache only for the duration of the current session.

Note: Volatile tables do not survive a Teradata reset. Restart processing destroys both the contents and the definition of a volatile table.

The journaling option default is LOG, which specifies that a transient (before image) journal is maintained. NO LOG allows for better performance, but provides less data protection in the case of a transaction abort.

The create default is DELETE ROWS, which deletes all table rows after the transaction completes.

To retain rows across transactions, specify ON COMMIT PRESERVE ROWS as the last keywords in the CREATE statement. For example:

CREATE VOLATILE TABLE vt_deptsal, NO LOG

(deptno smallint

,avgsal dec(9,2)

,maxsal dec(9,2)

,minsal dec(9,2)

,sumsal dec(9,2)

,empcnt smallint)

ON COMMIT PRESERVE ROWS;

Space usage is charged to the spool space of the login user. If your SPOOL allocation is large enough, you can create up to 1000 volatile tables per session.

2 - 18

Teradata RDBMS Database Administration 2 - 17 Chapter 2: Building the Teradata RDBMS

Global Temporary and Volatile Tables

The following are not available for volatile tables:

Referential Constraints

Check Constraints

Permanent Journaling

Compressed Column

DEFAULT Clause

TITLE Clause

Named Indexes

COLLECT STATISTICS

Access rights checking (because volatile tables are private to the session in which they are created)

Identity Column

Partitioning

Volatile versus Derived Tables

Volatile tables compare with derived tables in the following ways:

Volatile tables are. because.
different from Local to the session, not the query
derived tables Can be used with multiple queries in same session
Table definitions kept in cache
Dropped manually any time, or automatically at session end
Require CREATE VOLATILE TABLE statement
similar to derived Materialized in spool
tables No Data Dictionary access or transaction locks
Designed for optimal performance

Global versus Volatile Tables

The following summary compares global tables and volatile tables:

They are. because global temporary tables . and volatile tables.
different Materialize in user's TEMP space Base definitions are permanent in Data Dictionary tables Definitions can be materialized by any user with the necessary DML privileges Can be defined for COLLECT STATISTICS Can survive a Teradata restart Up to 2000 materialized tables per session Occupy space in user's SPOOL allocation Definitions are kept in cache and go away at session end or during a Teradata reset Private to the session in which they are created Cannot be defined for COLLECT STATISTICS Do not survive a Teradata reset Up to 1000 tables per session

Teradata RDBMS Database Administration

2 - 17 Chapter 2: Building the Teradata RDBMS

Global Temporary and Volatile Tables

They are. because global temporary tables . and volatile tables.
similar Materialized instance is local to a session If not dropped manually, instance is dropped automatically at session end An instance an be dropped manually any time during a session with DROP TEMPORARY TABLE Require CREATE GLOBAL TEMPORARY TABLE statement Have NO LOG and ON COMMIT PRESERVE/DELETE ROW options Materialized contents are not shareable with other sessions Start out empty for a session A very large number of global tables can prolong logoff, because materialized global tables are dropped automatically at session end. Local to a session If not dropped manually, dropped automatically at session end Can be dropped manually any time during a session with DROP TABLE Require CREATE VOLATILE TABLE statement Have NO LOG and ON COMMIT PRESERVE/DELETE ROW options Contents are not sharable with other sessions Start out empty A very large number of volatile tables can prolong logoff, because volatile tables are dropped automatically at session end.

2 - 20

Teradata RDBMS Database Administration 2 - 17 Chapter 2: Building the Teradata RDBMS

Indexes

Indexes

Indexes are used to distribute and store the rows of a table, as a means of joining tables, and to enable direct-path access. An index also can be used to enforce row uniqueness. An index is defined as a set of one or more table columns. Teradata supports the following types of indexes:
Previous << 1 .. 27 28 29 30 31 32 < 33 > 34 35 36 37 38 39 .. 218 >> Next