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 .. 36 37 38 39 40 41 < 42 > 43 44 45 46 47 48 .. 218 >> Next

define an SI on a JI CREATE INDEX The ALL option specifies that the NUSI retains row ID pointers for each logical row of the JI, which enables the NUSI to cover the JI. Thus, ALL is recommended when a covering NUSI could reduce the cost of access. However, compare the savings in access time against the possible consumption of additional index storage space.

2 - 64 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

IF you want to . THEN use . Comments
define a JI CREATE JOIN INDEX If none of the tables have triggers, you can create a JI on a single data table or on two or more data tables. You can define multi-table join indexes on columns that are common to high-volume queries. Note: If the index is to act as a partial-covering index (joined with the base table to satisfy queries), include the base table UPI or the ROWIDa keyword, or the NUPI plus either USI or the ROWID keyword. (See "Prototyping a Single-Table, Partial-Covering Index" on page 2-55.) A join index is similar to a base table in that you can: Use FALLBACK protection (but join indexes are not rebuilt during recovery processing) Create PIs and NUSIs on top of a join index Collect statistics (Optimizer form) on the PI and NUSI columns. (For usage considerations, see "Re-Collecting Statistics" on page 2-47) For a partial-covering index, collect statistics (Optimizer form) on the base table or tables
define an HI CREATE HASH INDEX Hash indexes have some of the attributes of a JI and some of an SI. You can define an HI only on a single table that has no triggers. You can define an HI on a PPI table as long as you use a full ORDER BY clause with an explicit column list. An HI can be fallback protected, but is not rebuilt during recovery. An HI cannot carry other indexes on top of it. NCR recommends you collect statistics on the joining columns of the base table. If statistics are not available on HI columns, the Optimizer uses any statistics on base table columns. Note: For a discussion on the effects of using a hash index and when and how to collect statistics on hash index joining columns, see "Collecting Optimizer Statistics" on page 2-45 and "Hash Indexes" in Teradata RDBMS SQL Reference, Volume 1.

a. You can only specify ROWID in the outermost SELECT of the CREATE JOIN INDEX statement. See "CREATE JOIN INDEX" in Teradata SQL Reference, Volume 4.

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)

Dropping Indexes

The following statements are used to drop indexes.

IF you want to remove a . THEN use . For more information, see .
PI definition Note: A table must have a PI. You can only change a current PI, or remove the definition and let Teradata create a default PI. (A default PI may not be in your best interests. For details, see "Teradata Indexes: The Basics" in Teradata RDBMS Database Design.) the following statements To remove the definition completely, or make changes not allowed with ALTER TABLE, recreate the table with the statements - CREATE TABLE - INSERT...SELECT, - DROP TABLE - RENAME TABLE For other definition changes, you can use ALTER TABLE To recreate the table, the procedure under "Recreating a Table" on page 2-9 To alter the definition, check the rules under "Creating and Modifying Indexes" on page 2-40.
SI DROP INDEX You can use SHOW JOIN INDEX, SHOW HASH INDEX, or HELP
JI DROP JOIN INDEX
HI DROP HASH INDEX [JOIN/HASH] INDEX to review the PI or column definitions before changing or dropping an index.

2 - 22 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Costing and Validating Indexes

Tradeoffs

When considering the choice of indexes and partitioning, carefully consider these tradeoffs:

Any query without conditions on defined indexes requires a full table scan.

Secondary indexes, hash indexes, and join indexes incur overhead costs in space and maintenance performance during inserts, updates, and deletes.

Verifying Behavior

When your choice is made, verify the behavior for the desired purposes. For example:

If you chose a UPI to provide even distribution, verify that the rows are evenly distributed.

If you chose a PPI to provide efficient access, validate that the PPI actually improves query performance.

For guidelines and suggested tools, see:

"Collecting Optimizer Statistics" on page 2-45

"Prototyping Indexes" on page 2-50

"COLLECT STATISTICS (Optimizer Form)" in Teradata RDBMS SQL Reference, Volume 4

In Teradata RDBMS SQL Reference, Volume 2:

"Query Capture Facility"

"Database Foundations for the Teradata Index Wizard"

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Collecting Optimizer Statistics
Previous << 1 .. 36 37 38 39 40 41 < 42 > 43 44 45 46 47 48 .. 218 >> Next