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

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 38 39 40 41 42 43 < 44 > 45 46 47 48 49 50 .. 218 >> Next

The parser then:

Determines what columns and/or indexes of the table already have a statistics structure in the data dictionary

Re-collects statistics on those columns and indexes

Note: This statement form does not support USING SAMPLE. If you need to re-collect statistics for a SAMPLE clause, submit another COLLECT STATISTICS statement with explicit references.

Data Dictionary Statistics Maintenance

Statistics collected with the COLLECT STATISTICS (Optimizer form) statement are maintained in the Data Dictionary as indicated below.

Note: You can review table statistics with SQL queries against the system views and with the STATS module of Teradata Manager (see Chapter 4: "Using Data Dictionary Tables and Views" and Teradata Manager User Guide.)

IF you collected statistics on ... THEN .
A list of columns An index defined on the same column set stored statistics are associated with the index ID.
an index, and then you drop that index the statistics already collected are retained, but associated with a pseudo index ID (that is, as multi-column statistics) rather than a real index ID.
Teradata RDBMS Database Administration 2 - 47 Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

IF you collected statistics on . THEN .
a list of columns, and then you add a new index based on the same column set the pseudo index ID is changed to the real index ID so the statistics already collected can be used.
several indexes defined on the same column set statistics are stored in the dictionary only once.

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Prototyping a Database

Prototyping is the process of empirically testing a database design.

After organizing information into databases, you can create an online prototype of any group of related tables, or of an entire database, and test it to determine how well it meets requirements.

Prototyping gives you real experience with Teradata RDBMS, allowing you to:

See results immediately

Learn from mistakes

Envision new possibilities

Teradata RDBMS offers several tools that simplify prototyping:

IF you want to . THEN you can use .
easily create, change, and recreate databases, tables, indexes, views, and procedures BTEQ scripts, Teradata Manager tools such as the Windows Data Dictionary Interface (WinDDI), and the Index Wizard recommendations.
fine-tune your prototype database definitions Teradata SQL statements such as SHOW, HELP, REPLACE VIEW, MODIFY DATABASE, and ALTER TABLE.
quickly reload and relocate data TPump, the BTEQ .REPEAT command, the SQL COPY TABLE ... WITH DATA statement, and SQL CREATE TABLE with embedded INSERT ... SELECT statements.
test a database schema HASHBUCKET, HASHROW, HASHAMP functions to confirm that each PI will distribute the rows of its table evenly across the disks. BTEQ batch jobs, using uncomplicated applications (without exceptional cases or custom-tailored reports) Query Capture Facility (QCF) and the associated statistics tools, such as the Teradata Statistics Wizard for statistical candidates, the Teradata SQL COLLECT STATISTICS statement, and the Teradata Index Wizard to analyze the use of secondary indexes ResUsage reports to: - Make sure join indexes and hash indexes are properly constructed - Develop processing guidelines for your baseline profiles

2 - 64 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

IF you want to ... THEN you can use .
test performance on the kinds of operations on data that will be used in your normal working environment All the database analysis tools, to automatically evaluate the use and effectiveness of table attributes and indexes, with TSC personnel running actual jobs using the Teradata System Emulation Tool (TSET) Query logging with DBQL, to measure query completion times against specific time and/or threshold parameters Refine and finalize your baseline profiles

Prototyping Indexes

Prototyping helps you determine which columns make the best primary index for a table, and the type and number, if any, of secondary, join, and/or hash indexes to create for a table.

Index prototyping involves the following steps:

Step Action
1 Create (or copy, if the table already exists) the base definition of each table you plan to index. For each table created, define only one primary index. You can use an identity column as the UPI if you need system-generated unique values.
2 Load the tables with: The MultiLoad or FastLoad client utility (but do not include a table with an identity column) The TPump client utility COPY TABLE . . . INSERT statements BTEQ scripts running SQL INSERT statements with: - SQL USING clauses or SELECT subqueries - The BTEQ .REPEAT command
3 Submit COLLECT STATISTICS (Optimizer form) on all PI or PPI columns of tables being prototyped.
Previous << 1 .. 38 39 40 41 42 43 < 44 > 45 46 47 48 49 50 .. 218 >> Next