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 .. 31 32 33 34 35 36 < 37 > 38 39 40 41 42 43 .. 218 >> Next

create a JI or an HI define either a NUPI or a UPI. A PPI is not allowed for a join or hash index. "CREATE INDEX" in Teradata RDBMS SQL Reference, Volume 4 "Memory Limitations Related to Partitioned Primary Indexes" in Teradata RDBMS Database Design
create a JI and/or an HI on a base table that has a PPI An HI is allowed on the table, as long as you define the HI with one of the following: - ORDER BY HASH (columnname_list) - ORDER BY VALUES (columnname_list) If you want to create more than one index, plan for the increase in memory consumption as explained under "Multiple Join Indexes and Memory Considerations" on page 2-39.
populate a table using Teradata load utilities that perform standard SQL row inserts, updates, and deletes (such as TPump, BTEQ, MultiLoad, FastLoad, and Teradata SQL Assistant - formerly known as Queryman) a PPI is allowed for the table. NCR recommends that values be supplied for all index columns, especially with TPump. Appendix B: "Import/Export Utilities"
specify the percentage for calculating the PPI Cache Threshold (PCT) of memory to be used during operations such as aggregations, merge spools, and joins on partitioned tables edit the PPICacheThrP field of the DBS Control record. The amount of available cache determines the total number of partitions the AMPs can keep open at one time. "PPICacheThrP" under "DBS Control Utility" in Teradata RDBMS Utilities
review the index definitions of a partitioning expression use the following system views: DBC.Indices DBC.TableConstraints DBC.IndexConstraints Teradata RDBMS Data Dictionary

2 - 22

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Secondary Indexes (SI)

IF you want to . THEN . For more information, see .
revalidate PPI table To detect errors, the CheckTable LEVEL 3 command: Teradata RDBMS
headers after: checktable level 3 Utilities
An ARC copy and To regenerate table headers, the ALTER TABLE ... Teradata RDBMS
restore operation REVALIDATE PRIMARY INDEX statement SQL Reference,
You changed decimal To regenerate table header, partition number, and Volume 4, especially:
rounding via the DBS row hash values, the ALTER TABLE . REVALIDATE
Control field PRIMARY INDEX null_partition_handler WITH - "ALTER TABLE"
RoundHalfwayMagUp DELETE/INSERT[INTO] statement - "General Rules for
Query responses Caution: REVALIDATE does not correct rows that are the REVALIDATE
indicate incorrect out of order by their assigned row ID. Contact the TSC. PRIMARY INDEX
internal partition Option"
numbers or row IDs "Revalidating Primary Indexes" on page 2-33

2 - 22 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Working with PPIs

When deciding whether to use a PPI, consider the trade-offs and benefits relative to your applications. For example:

More partitions may reduce the number of rows that must be read, thereby improving performance of full-table scan applications

Fewer partitions may improve join performance

When joining two tables with PPIs, the penalty for partitioning is minimal as long as:

The partitioning expressions are identical in both tables

The join is on the PPI columns

For more information on choosing the best method, see "Comparison of Partitioned and Nonpartitioned Primary Index Access for Some Typical Operations" in Teradata RDBMS Database Design

Row IDs of Partitioned Tables

With a PPI, rows of the table are:

Distributed to the AMPs by the hash value of the primary index columns (as with any PI)

Stored on each AMP based on the columns you defined for partitioning

Ordered within that partition by uniqueness within the hash value

A partitioned primary index has the same space usage and guidelines as a primary index, except that:

Each row has an additional two bytes for the internal partition number

Each row is stored on the AMP within its partition

Rows in the partition are kept in order by row ID

Within the row ID, partitoning is indicated by a flag bit and an unsigned two-byte partition number, as follows:

IF the row is in a . THEN the RowID partition flag is . AND the RowID unsigned two-byte partition number is .
NPPI table initialized to 0 (zero) logical and physical 0 (maintained internally).
PPI table set to 1 within the range 1-65535, based on the value of the partitioning columns. (If an insert, merge, or update causes the partition number to exceed this range, an error is returned.) The row sort order is: partition number; hash value within partition; uniqueness within hash value.

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Table Header Partition Indicator Bit

Also, partitioning is indicated in a bit in the table header of a PPI table as follows:

IF the PI of the table is . THEN the partitioning bit in the table header is .
Previous << 1 .. 31 32 33 34 35 36 < 37 > 38 39 40 41 42 43 .. 218 >> Next