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 .. 22 23 24 25 26 27 < 28 > 29 30 31 32 33 34 .. 218 >> Next


2 - 10

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Tables

Use this statement ... To .
CREATE [SET/MULTISET] temporaryjype TABLE create a temporary table with unique or duplicate rows, where temporary_type is one of: GLOBAL TEMPORARY (see ""Global Temporary Tables" on page 2-17) VOLATILE (see "Volatile Tables" on page 2-18) You can control row handling and TJ logging of transaction activity with the phrases: ON COMMIT DELETE/PRESERVE ROWS NO LOG Options are available as for a permanent table, except: No referential constraints No identity column No PPI
ALTER TABLE Change certain PI and/or partitioning parameters (see "Creating and Modifying Indexes" on page 2-40) Revalidate the PI (commonly used after an ARC RESTORE, especially when referential integrity is defined on the table) Change one or more of the following options: - Referential or other constraints at the table level - Whether to check or ignore a referential constraint - Fallback protection - Permanent journaling - Data block size - Cylinder freespace percent - Primary or foreign key Drop inconsistent references (commonly used after an ARC RESTORE if DROP FOREIGN KEY does not work) Add or drop one or more columns Add a column and specify one or a list of values to compress. (You cannot modify the compression of an existing column.) Change one or more column definitions (being careful about external references to the original column name), including: - Name - Data type - Data storage - Referential or other constraints at the column level On a global temporary table, change the option for: - Logging transactions - Retaining rows on transaction commit
RENAME TABLE change the name of a re-created table to the original table name (after you drop the original), or change the name of an existing table (being careful about references to the original table name).

2 - 10 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Tables

Copying Tables

Use the AS option of the CREATE TABLE statement to copy some or all of an existing table. Several choice combinations are possible:

Use this statement

To copy an existing table as .

CREATE

[[SET/MULTISET] [VOLATILE]] TABLE

AS [tablename/query_expression] ... WITH [NO] DATA

a permanent table or a volatile table. You choose what columns you want to copy and whether the table should be populated automatically, as follows:

IF you want the table to inherit . . . THEN specify . . .
all of the column definitions plus the contents tablename WITH DATA and (for a volatile table) ON COMMIT PRESERVE ROWS
all of the column definitions but none of the contents tablename WITH NO DATA
a subset of the column definitions plus the contents (query_expression) WITH DATA and (for a volatile table) ON COMMIT PRESERVE ROWS
a subset of the column definitions but none of the contents (query_expression) WITH NO DATA

CREATE [SET/MULTISET] GLOBAL TEMPORARY TABLE AS [tablename/query_expression] . . WITH NO DATA

a global temporary table. Use WITH NO DATA, because global tables are not populated until they are materialized by being referenced in a query.

IF you want the table to inherit . . . THEN specify . . .
all of the column definitions tablename WITH NO DATA
a subset of the column definitions (query_expression) and WITH NO DATA

Dropping Tables

Use the following statements to drop tables:

Note: Teradata automatically drops all temporary tables at session end.

IF you want to remove a . THEN use this statement .
permanent data table (being careful DROP TABLE
about any join indexes, hash indexes,
views, macros, and stored procedures
that may reference it)

2 - 10

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Tables

IF you want to remove a . THEN use this statement .
materialized global temporary table, DROP TEMPORARY TABLE
before session end
volatile table before session end DROP TABLE
global temprary table definition DROP TABLE

Changing a Table Definition

You make changes to the definition of a global temporary table, a journal table, or a permanent data table with these Teradata SQL statements:

IF you want to . THEN use .
change a default PI to a defined PI CREATE TABLE, INSERT. ..SELECT, DROP TABLE, and RENAME TABLE, followed as necessary by GRANT privilege ON newtablename. For the procedure, see "Recreating a Table" on page 2-9.
drop one or more partitioning columns
rename or change the data type or nullability of partitioning columns
change the data type or COMPRESS specification of an existing column
redefine the PI or PPI name, columns, and/or partitioning of an empty table ALTER TABLE (for rules, see "Creating and Modifying Indexes" on page 2-40)
impose a PRIMARY KEY or UNIQUE constraint on a PI or PPI of an empty table (if a USI is not defined on the same columns) Note: A USI will be implicitly defined on the PI columns.
Previous << 1 .. 22 23 24 25 26 27 < 28 > 29 30 31 32 33 34 .. 218 >> Next