UPDATE - Teradata Database

UPDATE

Purpose  

Modifies column values in existing rows of a table.

There are five forms of the UPDATE statement.

The Basic form can be specified with or without a FROM clause.

The Join Condition form updates rows from a table when the WHERE condition directly references columns in tables other than the one from which rows are to be updated; that is, if the WHERE condition includes a subquery or references a derived table.

Syntax - Basic Form, No FROM Clause

Syntax - Basic Form, FROM Clause

Syntax - Joined Tables Form

where:

ANSI Compliance

UPDATE is ANSI SQL:2011-compliant.

Required Privileges

The following privilege rules apply to the UPDATE statement.

For example, in the following request, READ access is required by the WHERE condition.

     UPDATE table_1 

     SET column_1 = 1 

     WHERE column_1 < 0;

Similarly, the following request requires READ access because you must read column_1 values from table_1 in order to compute the new values for column_1.

     UPDATE table_1 

     SET field_1 = column_1 + 1;

The following request does not require SELECT privileges:

     UPDATE table_1 

     SET column_3 = 0 ALL;

Locks and Concurrency

An UPDATE operation sets a WRITE lock for the table or row being updated.

The lock set for SELECT subquery operations depends on the isolation level for the session, the setting of the AccessLockForUncomRead DBS Control flag, and whether the subquery is embedded within a SELECT operation or within an UPDATE request.

For More Information

Activity Count

The activity count in the success response (or ACTIVITY_COUNT system variable for a stored procedure) for an UPDATE request reflects the total number of rows updated. If no rows qualify for update, then the activity count is zero.

Duplicate Rows and UPDATE

It is not possible to distinguish among duplicate rows in a MULTISET table. Because of this, when a WHERE condition identifies a duplicate row, all of the duplicate rows are updated.

Duplicate Row Checks

Unless a table is created as MULTISET (and without UNIQUE constraints) to allow duplicate rows, the system always checks for duplicate rows during the update process. The order in which updates are executed can affect the result of a transaction.

Consider the following example:

     CREATE SET TABLE t1 (

       a INTEGER, 

       b INTEGER) 

     PRIMARY INDEX (a);

     INSERT INTO t1 VALUES (1,1);

     INSERT INTO t1 VALUES (1,2);

     UPDATE t1 

     SET b = b + 1 

     WHERE a = 1; /* fails */

     UPDATE t1 

     SET b = b - 1 

     WHERE a = 1; /* succeeds */

The first UPDATE request fails because it creates a duplicate row.

If the order of the UPDATE requests is reversed, then both UPDATE requests succeed because the UPDATE does not result in duplicate rows.

     CREATE SET TABLE t1 (

       a INTEGER, 

       b INTEGER) 

     PRIMARY INDEX (a);

     INSERT INTO t1 VALUES (1,1);

     INSERT INTO t1 VALUES (1,2);

     UPDATE t1 

     SET b = b - 1 

     WHERE a = 1; /* succeeds */

     UPDATE t1 

     SET b = b + 1 

     WHERE a = 1; /* succeeds */

This mode is characteristic of both simple and join updates. Updates that affect primary or secondary index values, on the other hand, are implemented as discrete delete and insert operations.

Large Objects and UPDATE

The behavior of truncated LOB updates differs in ANSI and Teradata session modes. The following table explains the differences in truncation behavior.

UPDATE Processing Time

Processing time for an UPDATE operation is longer under the following conditions:

You can shorten the processing time for an UPDATE operation by using an indexed column in the WHERE clause of the UPDATE request.

Processing time can also vary between different syntaxes used to perform the identical update operation. Use the EXPLAIN modifier to determine which syntax form produces optimal processing time.

Rules for Embedded SQL and Stored Procedures

The following rules apply to the searched form of the UPDATE statement:

Update values are set in the corresponding row column values according to the rules for host variables.

Queue Tables and UPDATE

The best practice is to avoid using the UPDATE statement on a queue table because the operation requires a full table scan to rebuild the internal queue table cache. You should reserve this statement for exception handling.

An UPDATE statement cannot be in a multistatement request that contains a SELECT and CONSUME request for the same queue table.

For more information on queue tables and the queue table cache, see SQL Data Definition Language.

Rules for Updating Partitioning Columns of a PPI Table

The following rules apply to updating the partitioning columns of a PPI table:

a The system checks the WHERE clause condition for its truth value after the update to the row.

b The system evaluates the new result of the partitioning expression for the updated row.

When you design your partitioning expressions, you should construct them in such a way that expression errors either cannot, or are very unlikely to, occur.

Rules for Updating Nonpartitioned NoPI and Column‑Partitioned Tables

The following rules apply to updating nonpartitioned NoPI tables and column‑partitioned tables:

The optimization to skip the update if the row is unchanged is not done for a column‑partitioned table or join index.

Identity Columns and UPDATE

You cannot update a GENERATED ALWAYS identity column.

Updating of GENERATED ALWAYS Identity Columns and PARTITION Columns

You cannot update the following set of system‑generated columns:

You can update a GENERATED BY DEFAULT identity column. The specified value is not constrained by identity column parameters. It is constrained by any CHECK constraints defined on the column.

Updating Distinct UDT Columns

To update a distinct type column, either of the following must be true:

By default, a distinct type has a system‑generated cast of this type.

For example, suppose you have the following table definition:

     CREATE TABLE table_1 (

      column1 euro, 

      column2 INTEGER)

     UNIQUE PRIMARY INDEX(column2);

Then the information in the following table is true:

UPDATE table_1

  SET column1 = 4.56

  WHERE column2 = 5;

UPDATE table_1

  SET column1 = CAST(4.56 AS euro)

  WHERE column2 = 5;

USING (price decimal(6,2))

  UPDATE table_1

  SET column1 = (CAST (:price AS

    euro)).roundup(0);	

UPDATE table_1

 SET column1 = column1.roundup(0);	

UPDATE table_1

 SET column1 = column3;

UPDATE table_1

  SET column1 = NULL

  WHERE column2 = 10;	

Updating Structured UDT Columns

To update a structured type column, the updated value must be of the same structured type. For example, suppose you have the following table definition:

     CREATE TABLE table_1 (

       column_1 address, 

       column_2 INTEGER

       column_3 address)

     UNIQUE PRIMARY INDEX(column2);

Then the information in the following table is true:

UPDATE table_1

  SET column_1 = 

  '17095 Via Del Campo;92127';	

USING (addr varchar(30), c2 INTEGER)

UPDATE table_1

  SET column_1 = :addr

WHERE column_2 = :c2;	

UPDATE table_1 

  SET column_1 = 

  NEW address('17095 Via Del Campo',

              '92127');

USING (street VARCHAR(20), zip   CHARACTER(5))

UPDATE table_1 

  SET column_1 = NEW   address(:street,:zip);

UPDATE table_1 

  SET column_1 = NEW address();

UPDATE table_1 

  SET column_1 = 

  (NEW address().street('17087 Via

  Del Campo').zip('92127'));

UPDATE table_1

  SET column_1 =

  column1.zip('92128');

UPDATE table_1 

  SET column_1 = 

  (CAST ('17095 Via Del Campo;92127'   AS address) );   	

UPDATE table_1

  SET column_1 = column3;

UPDATE table_1 

  SET column_1 = NULL;

Updating Structured UDTs Using a Mutator SET Clause

Mutator SET clauses provide a syntax for updating structured type columns. A mutator SET clause can only be used to update structured UDT columns (the specified column_name in a mutator SET clause must identify a structured UDT column). Each mutator method name you specify must be a valid mutator method name for the respective structured type value.

A mutator method name is the same name as the attribute name that it modifies. Within the mutated set clause, parentheses following the attribute name are not valid.

There is one additional restriction on mutator SET clauses.

Consider the following example:

     SET mycol.R = x,

         mycol.y = mycol.R() + 3

As implemented by Teradata, any column references in an expression refer to the value of the column in the row before the row is updated. The system converts the two example clauses to the following single equality expression:

     mycol = mycol.R(x).y(mycol.R() + 3)

This is a deviation from the ANSI SQL:2011 standard.

According to the ANSI SQL:2011 standard, the column reference to mycol in the second example equality expression of the mutator SET clause should reflect the change made to it from the first equality expression of the mutator SET clause, the assignment of x.

In other words, the two equality expressions are converted to the following single equality expression:

     mycol = mycol.R(x).y(mycol.R(x).R() + 3)

Rules for Updating Rows Using Views

To update rows using a view through which the table is accessed, observe the following rules:

The immediate owner of the view (that is, the containing database for the view) must have the UPDATE privilege on the underlying object (view, base table, or columns) whose columns are to updated, and the SELECT privilege on all tables that are specified in the WHERE clause.

Although you can generally convert the data type of a view column (for example, from VARCHAR to CHARACTER), if that converted column is a component of an index, then the Optimizer does not use that index when the base table is updated because the data type of the recast column no longer matches the data type of the index column.

The resulting all-AMP, all-row scan defeats the performance advantages the index was designed for.

Rules for Using Scalar Subqueries in UPDATE Requests

The following rules apply to using scalar subqueries in UPDATE requests:

However, Teradata Database processes any noncorrelated scalar subqueries specified in the FROM, WHERE, or SET clauses of an UPDATE statement in a row trigger as a single‑column single‑row spool instead of as a parameterized value.

Rules for Using a Scalar UDF in an UPDATE Request

A scalar UDF can be invoked from both the SET clause and the WHERE clause of an UPDATE request. See “Example 12: UPDATE Using an SQL UDF Invocation in the SET and WHERE Clauses” on page 509.

Rules for Using the DEFAULT Function With Update

The following rules apply to using the DEFAULT function with an UPDATE statement:

The resulting data type of the DEFAULT function is the data type of the constant or built-in function specified as the default unless the default is NULL. If the default is NULL, the resulting date type of the DEFAULT function is the same as the data type of the column or expression for which the default is being requested.

For example, DEFAULT(col2) evaluates to the default value of col2. This is a Teradata extension to the ANSI SQL:2011 specification.

See SQL Functions, Operators, Expressions, and Predicates for more information about the DEFAULT function.

Rules for Using a PERIOD Value Constructor With UPDATE

See SQL Functions, Operators, Expressions, and Predicates for the rules on using PERIOD value constructors. Also see “Example 17: INSERT Using a PERIOD Value Constructor” on page 411 for two examples of how to use PERIOD value constructors in UPDATE requests.

Nonvalid Uses of UPDATE

An UPDATE operation causes an error message to be returned when any of the following conditions occur:

FROM Clause

The optional FROM list included in the UPDATE syntax is a Teradata extension to support correlated subqueries and derived tables in the search conditions for UPDATE.

Specify the FROM clause for the following reasons only:

If a table is listed in the FROM clause for the UPDATE and not in the FROM clause for a subquery, then field references in the subquery are scoped at the outer level, making it a correlated subquery.

The following rules apply to the use of correlated subqueries in the FROM clause of an UPDATE request:

Also see “Correlated Subqueries” on page 134.

UPDATEs With a Join

If a row from the updated table is joined with a row from another table in the FROM clause, and the specified WHERE condition for the request evaluates to TRUE for that row, then the row in the updated table is updated with columns referenced in the SET clause from the joined row.

When an UPDATE request specifies a join operation, the join is more efficient if the WHERE condition uses values for indexed columns in the joined tables.

Be aware that the order in which join updates are executed can affect the result of a transaction. See “Duplicate Row Checks” on page 494 for more information.

Example  

The following request updates the yrsexp column in the employee table for employee Greene, who is employee number 10017:

     UPDATE employee 

     SET yrs_exp = 16 

     WHERE empno = 10017;

Example  

The following request updates the employee table to apply a 10 percent cost of living increase to the salary for all employees:

     UPDATE employee 

     SET salary = salary * 1.1 ALL ;

Example  

The following request places a null in the salary column for employee number 10001:

     UPDATE employee 

     SET salary = NULL 

     WHERE emp_no = 10001 ;

Example : UPDATE Specifying a Correlation Name for the Updated Table in the FROM Clause

Because the following UPDATE request specifies the correlation name e for employee in its FROM clause, you must specify e in place of employee immediately following the UPDATE keyword.

     UPDATE e

     FROM employee AS e, department AS d

     SET salary = salary * 1.05

     WHERE e.emp_no = d.emp_no;

Example : UPDATE With a Subquery in its WHERE Clause

The following examples perform the same UPDATE operation using either a subquery join with a derived table or a simple equality join on column_1 in table_1 and table_2.

     UPDATE table_1 

     SET column_1 = 1 

     WHERE column_1 IN (SELECT column_2

                        FROM table_2);

     UPDATE table_1 

     SET column_1 = 1 

     WHERE table_1.column_1=table_2.column_1;

Example : UPDATE With a Noncorrelated Subquery in its WHERE Clause

The following UPDATE operation uses a subquery to decrease the list price on an electroacoustic CD budget line for labels containing the string ‘erstwhile’:

     UPDATE disc

     SET price = price * .85

     WHERE label_no IN (SELECT label_no

                        FROM label

                        WHERE label_name LIKE '%erstwhile%')

                        AND   line = 'NEA';

You can obtain the same result by writing the query using a join between the disc and label tables on label_no:

     UPDATE disc

     SET price = price * .85

     WHERE disc.label_no = label.label_no

     AND   label_name LIKE '%erstwhile%'

     AND   line = 'NEA';

Example : UPDATE With a Scalar Noncorrelated Subquery in its SET Clause

You can specify a scalar subquery in the same way that you specify a column or constant in the SET clause of an UPDATE request.

The following example specifies a scalar subquery in its SET clause:

     UPDATE sales_sum_table AS sst 

     SET  total_sales = (SELECT SUM(amount) 

                         FROM sales_table AS s

                         WHERE s.day_of_sale BETWEEN sst.period_start 

                                             AND     sst.period_end);

Example : UPDATE With a Scalar Correlated Subquery in its SET Clause

When a scalar correlated subquery is specified in the SET clause, even if the UPDATE request does not have a FROM clause, Teradata Database treats the update as a joined update because of the scalar correlated subquery.

The following example requires salesumtable to be joined with salestable, and the update is done using a merge update operation via a spool.

     UPDATE sales_sum_table AS sst 

     SET  total_sales = (SELECT SUM(amount) 

                         FROM sales_table AS s

                         WHERE s.day_of_sale BETWEEN sst.period_start 

                                             AND     sst.period_end); 

Example : UPDATE With a Correlated Subquery in its WHERE Clause

The following correlated subquery is executed once for each row of the outer reference, publisher, and since two publishers have no books in the library two rows of publisher are modified.

     UPDATE publisher 

     SET pub_num = NULL

     WHERE 0 = (SELECT COUNT(*)

                FROM book 

                WHERE book.pub_num = publisher.pub_num);

Two publishers have books in the library, and two publishers do not.

“Correlated Subqueries” on page 134 describes additional examples of correlated and noncorrelated subqueries.

Example : UPDATE With a Noncorrelated Subquery in its WHERE Clause

To modify “Example 9: UPDATE With a Correlated Subquery in its WHERE Clause” on page 508 to be a noncorrelated subquery, change the subquery to include all tables it references in the inner FROM clause.

     UPDATE publisher 

     SET pubnum = NULL 

     WHERE 0 = (SELECT COUNT(*)

                FROM book, publisher

                WHERE book.pubnum = publisher.pubnum);

The request does not contain a correlated subquery and the condition in the subquery has a local defining reference. The count, determined once, is nonzero, and no rows are deleted.

“Correlated Subqueries” on page 134 describes additional examples of correlated and noncorrelated subqueries.

Example : UPDATE With a Join

The following example updates the employee table to give each employee a 5 percent salary increase using a join between the employee and department tables.

     UPDATE employee

     FROM department AS d 

     SET salary = salary * 1.05 

     WHERE employee.dept_no = d.dept_no 

     AND   salary_pool > 25000;

Example : UPDATE Using an SQL UDF Invocation in the SET and WHERE Clauses

Assume you have created an SQL UDF named value_expression. The following example updates table t1 using the result of value_expression when it is passed values for columns a1 and b1 to update columns a1 and b1 for rows where the invocation of value_expression using the values for columns c1 and d1 are greater than 5.

     UPDATE t1 

     SET b1 = test.value_expression(t1.a1, t1.b1) 

     WHERE test.value_expression(t1.c1, t1.d1) > 5;

Example : UPDATE With a Mutator SET Clause

The mutator SET clause syntax permits you to write UPDATE requests for structured UDTs in a form of shorthand notation. Consider the following structured data type definitions:

     CREATE TYPE person AS (

       last_name  VARCHAR(20),

       first_name VARCHAR(20),

       birthdate  DATE)

     … 

     ;

     CREATE TYPE school_record AS (

       school_name VARCHAR(20),

       gpa         FLOAT)

     INSTANTIABLE

     … 

     ;

     CREATE TYPE college_record AS (

       school  school_record,

       major   VARCHAR(20),

       minor   VARCHAR(20))

     INSTANTIABLE

     … 

     ;

Suppose you have the following table definition:

     CREATE TABLE student_record (

       student_id  INTEGER,

       student     person,

       high_school school_record,

       college     college_record);

Without the mutator SET clause notation, UPDATEs must be written in forms of chained or nested mutator method invocations. For example:

     UPDATE student_record

      SET student = student.Last_name('Tamura').First_name('Natsuki')

     WHERE student.First_name() = 'John'

     AND   student.Last_name() = 'Doe';

     UPDATE student_record

      SET college =

      college.school(college.school().school_name('UCLA').GPA(3.20)).      major('Computer Science');

     WHERE student.First_name() = 'Steven'

     AND   student.Last_name() = 'Smith';

Formulating the necessary chained or nested mutator method invocations can be very complex. However, you can use the mutator SET clause notation to make writing UPDATE requests for a structured type column simpler and more intuitive.

Consider these rewrites of the two previous UPDATE requests (these use the mutator SET clause notation):

     UPDATE student_record

       SET student.Last_name = 'Tamura',

           student.First_name = 'Natsuki',

     WHERE student.First_name() = 'John'

     AND   student.Last_name() = 'Doe';

     UPDATE student_record

      SET college.school.school_name = 'UCLA',

          college.school.GPA = 3.20,

          college.major = 'Computer Science'

     WHERE student.First_name() = 'Steven'

     AND   student.Last_name() = 'Smith';

Example : Update Requests Using the DEFAULT Function

You can specify a DEFAULT function without a column name as the expression in the SET clause.

The column name for the DEFAULT function is the column specified as the column name. The DEFAULT function evaluates to the default value of the column specified as column_name.

The examples below assume the following table definition:

     CREATE TABLE table_11 (

       col_1 INTEGER, 

       col_2 INTEGER DEFAULT 10,

       col_3 INTEGER DEFAULT 20,

       col_4 CHARACTER(60));

The following UPDATE requests are valid:

The following example updates the values of col3 to 20 (its default value) for all rows:

     UPDATE table_11 

       SET col_3 = DEFAULT;

The following example updates the values of Col3 to 20 (its default value) for rows where the value of col1=5.

     UPDATE table_11 

       SET col_3 = DEFAULT 

         WHERE Col1 = 5;

Assume the following table definition for the next example:

     CREATE TABLE table_12 (

       x INTEGER,

       y INTEGER);

The following example updates the values of col3 to 20 (its default value) depending on whether the WHERE condition evaluates to true or not.

     UPDATE table_11 

       SET col_3 = DEFAULT 

         WHERE 5 < ANY 

          (SELECT y 

           FROM table_12);

You can specify a DEFAULT function with a column name in the source expression. The DEFAULT function evaluates to the default value of the column name specified as the input argument to the DEFAULT function. For example, DEFAULT(col_2) evaluates to the default value of col_2. This is a Teradata extension.

The following UPDATE request is valid. The input argument to the DEFAULT function is col_2. Therefore, the DEFAULT function evaluates to the default value of the col_2 and then set col_3 to this value. Specifically, it updates the values of col_3 to 10 (the default value of col_2) for all rows.

     UPDATE table_11 

       SET col3 = DEFAULT(col_2);

The following example updates the values of col_3 to 10 (the default value of col_2) for rows where the value of col_1 is 5.

     UPDATE table_11

        SET col_3 = DEFAULT(col_2) 

         WHERE col_1 = 5;

You can specify a DEFAULT function with a column name anywhere in the update expression. This is a Teradata extension to the ANSI SQL:2011 standard.

The following UPDATE request is valid. The input argument to the DEFAULT function is col_2; therefore, the DEFAULT function evaluates to the default value of col_2. The request then updates the value of col_3 to 15 (10+5, the default value of col_2 + 5) for all rows.

     UPDATE table_11 

       SET col_3 = DEFAULT(col_2) + 5;

The following example updates the value of col3 to 15 (the default value of col2+5) for all rows.

     UPDATE table_11 

       SET col_3 = DEFAULT(col_2) + 5 ALL;

The following example updates the values of col3 to 15 (the default value of col_2 + 5) for rows where the value of col_1=20.

     UPDATE table_11 

       SET col_3 = DEFAULT(col_2)+5 

         WHERE col_1 = 20;

When there is no explicit default value associated with the column, the DEFAULT function evaluates to null.

Assume the following table definition for the examples that follow:

     CREATE TABLE table_13 (

       col_1 INTEGER,

       col_2 INTEGER NOT NULL,

       col_3 INTEGER NOT NULL DEFAULT NULL,

       col_4 INTEGER CHECK (col_4>100) DEFAULT 99 );

In the following example, col_1 is nullable and does not have an explicit default value associated with it; therefore, the DEFAULT function evaluates to null.

     UPDATE table_13 

       SET col_1 = DEFAULT;

The following UPDATE requests are equivalent. For both requests, the DEFAULT function evaluates to the default value of col_3 for rows where the value of col_1 is 5.

     UPDATE table_11 

       SET col_3 = DEFAULT(c3) 

         WHERE col_1 = 5;

     UPDATE table_11 

       SET col_3 = DEFAULT 

         WHERE col_1 = 5;

Example : UPDATE Using a PERIOD Value Constructor

The following example uses tables t1 and t2, which are defined as follows:

     CREATE TABLE t1 (

       c1 INTEGER

       c2 PERIOD(DATE))

     UNIQUE PRIMARY INDEX (c1);

     CREATE TABLE t2 (

       a INTEGER

       b DATE

       c DATE)

     UNIQUE PRIMARY INDEX (a);

The following two UPDATE requests both use a PERIOD value constructor:

     UPDATE t1

     SET c2 = PERIOD(DATE '2007-02-03', DATE '2008-02-04'));

     UPDATE t1 FROM t2

     SET c2 = PERIOD(b,c) 

     WHERE t2.a = 2;

Example : Updating a NoPI Table

The following UPDATE request updates the NoPI table nopi012_t1 aliased as t1.

     UPDATE t1 

     FROM nopi012_t1 AS t1, nopi012_t2 AS t2 

     SET c3 = t1.c3 * 1.05 

     WHERE t1.c2 = t2.c2;

Example : Application of Row-Level Security SELECT and UPDATE Constraints When User Lacks Required Privileges (UPDATE Request)

This example show how the SELECT and UPDATE constraints are applied when a user that does not have the required privileges submits an UPDATE request in an attempt to update the classification level value for a row. The SELECT constraints filter out the rows that the user is not permitted to access and the UDPATE constraints restrict the user from executing the update operation on the target row.

The classification level value is stored in the classification_level column, which is one of the constraint columns (the other constraint column is classification_categories).

An EXPLAIN statement is used to show the steps involved in the execution of the request and the outcome of the application of the constraints.

Table Definition

The statement used to create the table in this example is:

     CREATE TABLE rls_tbl(
       col1 INT,
       col2 INT, 
       classification_levels   CONSTRAINT,
       classification_categories CONSTRAINT);

User’s Session Constraint Values

The user’s sessions constraint values are:

     Constraint1Name LEVELS

     Constraint1Value 2

     Constraint3Name CATEGORIES

     Constraint3Value '90000000'xb

EXPLAIN Statement

This EXPLAIN statement is used to show the steps involved in the execution of the UPDATE request and the outcome of the application of the SELECT and UPDATE constraints.

       EXPLAIN UPDATE rls_tbl SET col1=2 where col1=1; 

EXPLAIN Text

The system returns this EXPLAIN text.

 *** Help information returned. 28 rows.

 *** Total elapsed time was 1 second.

Explanation

---------------------------------------------------------------------------

  1) First, we lock a distinct RS."pseudo table" for write on a RowHash

     to prevent global deadlock for RS.rls_tbl.

  2) Next, we lock RS.rls_tbl for write.

  3) We do a single-AMP RETRIEVE step from RS.rls_tbl by way of the

     primary index "RS.rls_tbl.col1 = 1" with a residual condition of (

     "((SYSLIB.SELECTLEVEL (2, RS.rls_tbl.levels ))= 'T') AND

     ((SYSLIB.SELECTCATEGORIES ('90000000'XB, RS.rls_tbl.categories ))=

     'T')") into Spool 1 (group_amps), which is redistributed by hash

     code to all AMPs.  Then we do a SORT to order Spool 1 by the sort

     key in spool field1.  The size of Spool 1 is estimated with low

     confidence to be 1 row (18 bytes).  The estimated time for this

     step is 0.01 seconds.

  4) We do a group-AMP MERGE DELETE to RS.rls_tbl from Spool 1 (Last

     Use) via the row id.  New updated rows constrained by (

     RS.rls_tbl.levels = SYSLIB.UPDATELEVEL (2, {LeftTable}.levels)), (

     RS.rls_tbl.categories = SYSLIB.UPDATECATEGORIES ('90000000'XB,

     {LeftTable}.categories)) are built and the result goes into Spool

     2 (all_amps), which is redistributed by hash code to all AMPs.

     Then we do a SORT to order Spool 2 by row hash.  The size is

     estimated with low confidence to be 1 row.  The estimated time for

     this step is 22.15 seconds.

  5) We do an all-AMPs MERGE into RS.rls_tbl from Spool 2 (Last Use).

     The size is estimated with low confidence to be 1 row.  The

     estimated time for this step is 1 second.

  6) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

     The total estimated time is 23.16 seconds.

Related Topics