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

an NPPI initialized to 0 (zero).
a PPI set to 1.

Note: The table header size limit is 64K. (This can be reached quickly when the compiled partitioning expression is large.)

System-Derived PARTITION Column

A table with a PPI has available a system-derived column named PARTITION to review the identity of the partition associated with a row. PARTITION values take no space in the table. To report a partition number, the internal partition number for the row is extracted from the relevant PARTITION field and converted to the external number.

Note: If there is no such external number for the internal partition number, an error is generated. If you receive this error, contact the TSC immediately.

The PARTITION value is equivalent to a value-expression that is the same as the partitioning expression defined for the PPI (or zero, if the table has an NPPI), with the column references qualified as needed. Thus, if you alter the partitioning expression for a PPI, the values of PARTITION also may change.

Referencing the PARTITION Column

You can specify PARTITION as a column name in a SELECT statement in order to determine the partition to which various rows in a table belong. Also, you may reference it in the ALTER TABLE . DROP RANGE WHERE clause.

The default title for the system-derived column is 'PARTITION'. However, a query accepts any capitalization, and you can use the AS clause to define how you want the name returned. For example:

sel PArtition as parTITION from TableX;

*** Query completed. 2 rows found. One column returned. *** Total elapsed time was 1 second.

parTITION

2 1

The rules of how a reference to PARTITION is interpreted depend on the table definition and the reference syntax, as follows:

2 - 64 Teradata RDBMS Database Administration

Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

IF you want to .

THEN .

define a column explicitly named Partition for a table

the system-derived column PARTITION will not be accessible for that table. Any reference to PARTITION will be a reference to the column explicitly defined for the table, not to the system-derived column.

retrieve a column named PARTITION in a query

The system-derived column PARTITION is not included in the list of columns returned by *; you must specify it. You may access the system-derived PARTITION column if: The view definition specifies a column named PARTITION The table definition does not include a column PARTITION

Any reference may be qualified by a databasename and a tablename. The rules of qualification and access are as follows:

IF the query. THEN .
Targets only one table That table has no column named PARTITION a reference to PARTITION accesses the system-derived column.
Targets only one table That table has an explicit column named PARTITION any reference to PARTITION accesses only the explicitly defined column.
Targets several tables Only one table has a column explicitly named Partition an unqualified reference to PARTITION accesses only that explicit column.
Targets multiple tables None has an explicit column named Partition Any reference to PARTITION must be fully qualified The column accessed is the system-derived column
Targets multiple tables More than one has an explicit column named Partition any reference to PARTITION must be fully qualified; access is: For any table with a column named Partition, the explicit column For all other tables, the system-derived column

use the system-derived

PARTITION as a defined column

an error will result, because it is not in the Data Dictionary as a column definition. Thus, it is not returned by a HELP or SHOW TABLE, HELP COLUMN, or SELECT * . statement, and cannot be updated or included in the column set of an index or partition.

2 - 64

Teradata RDBMS Database Administration Chapter 2: Building the Teradata RDBMS

Converting Non-Relational Data Structures

Controlling Memory Usage with PPICacheThrP

Use the PPICacheThrP field of the DBS Control Record to change the percent of FS cache to be used during PPI operations involving joins, aggregation, and merge spooling. The amount of available cache determines the total number of partitions the AMPs can keep open at one time.

You can specify the maximum number of partitions to process as a set for a particular table. The number actually processed depends on the number of non-empty partitions. If a table has fewer non-empty partitions than the specified set, all the non-empty partitions are processed together at the same time. Teradata RDBMS keeps multiple partitions open on the PPI table during join processing to minimize the impact of partitioning on the join performance. (These open partitions might be referred to as contexts in your EXPLAIN text.) Each partition open is another context.

When PPI tables are involved in a merge join, the Optimizer decides, based on the size of each table, how many of the total contexts are to be allocated to the left table and how many to the right table. Each of the left table partitions are joined with all the right table partitions, and all the allocated partitions are kept open as contexts during the join step. Keeping multiple contexts open allows the system to do a join with a minimum number of I/Os.
Previous << 1 .. 32 33 34 35 36 37 < 38 > 39 40 41 42 43 44 .. 218 >> Next