Download (direct link):
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.
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 .
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 AdministrationChapter 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.