Inserting Into Row-Partitioned Tables
The section lists the rules for inserting rows into row-partitioned tables.
Inserting rows into empty row partitions is optimized to avoid transient journaling
of each row inserted into those partitions. This optimization can only be applied
to tables that are not defined with referential integrity relationships.
The outcome of partitioning expression evaluation errors, such as divide‑by‑zero errors,
depends on the session mode.
In this session mode …
|
Expression evaluation errors roll back this work unit …
|
ANSI
|
request that contains the aborted request.
|
Teradata
|
transaction that contains the aborted request.
|
When inserting rows into a single-level row-partitioned table for which the partitioning
expression is not a RANGE_N or CASE_N function, the partition expression must result
in a nonnull value between 1 and 65,535 after casting to INTEGER, if the data type
is not INTEGER.
When inserting rows into a row-partitioned table, any of the partitioning expressions
(which must be RANGE_N or CASE_N functions) for that row must result in a nonnull
value.
When inserting rows into a base table that cause an insert into a row-partitioned
join index, any of the partitioning expressions for that join index must result in
a nonnull value.
When inserting rows into a base table that cause an insert into a single‑level row‑partitioned
join index, for a partitioning expression that is not RANGE_N or CASE_N, the result
of the partitioning expression must be a nonnull value between 1 and 65,535 after
casting to INTEGER, if the data type is not INTEGER.
When inserting rows into a base table that cause an update of a join index row in
a row‑partitioned join index, any of the partitioning expressions for that updated
index row must result in a nonnull value.
When inserting rows into a base table that cause an update of a join index row in
a row‑partitioned join index with single-level row partitioning where the partitioning
expression is not RANGE_N or CASE_N, the result of the partitioning expression must
be a nonnull value between 1 and 65,535 after casting to INTEGER, if the data type
is not INTEGER.
You cannot assign either a value or a null to the system-derived columns PARTITION
or PARTITION#L1 through PARTITION#L62 in an insert operation.
The session mode and session collation at the time the row-partitioned table was created
does not have to match the current session mode and collation for the insert operation
to succeed. This is because the partition a row is to be inserted into is determined
by evaluating the partitioning expression on partitioning column values using the
table’s session mode and collation.
Collation has the following implications for inserting rows into tables defined with
a character partitioning:
If the collation for a row-partitioned table is either MULTINATIONAL or CHARSET_COLL,
and the definition for the collation has changed since the table was created, Teradata
Database aborts any request that attempts to insert a row into the table and returns
an error to the requestor.
If a noncompressed join index with character partitioning under either an MULTINATIONAL
or CHARSET_COLL collation sequence is defined on a table, and the definition for the
collation has changed since the join index was created, the system aborts any request
that attempts to insert a row into the table and returns an error to the requestor
whether the insert would have resulted in rows being modified in the join index or
not.
If a partitioning expression for a table or noncompressed join index involves Unicode
character expressions or literals, and the system has been backed down to a release
that has Unicode code points that do not match the code points that were in effect
when the table or join index was defined, Teradata Database aborts any attempts to
insert rows into the table and returns an error to the requestor.