Examples: SELECT and PARTITION - Teradata Vantage

Example: Partition Lock for a Row-Partitioned Table

In this example, the query retrieves rows from table slppit1 with a WHERE condition on the partitioning column that qualifies a single partition. An all-AMPs partition lock is used to lock the single partition being accessed and a proxy lock is used to serialize the placement of the all-AMPs partition range lock.

The table definition for this example is as follows:

  CREATE TABLE HLSDS.SLPPIT1 (PI  INT, PC INT, X INT, Y INT)
    PRIMARY INDEX (PI)
    PARTITION BY (RANGE_N(PC BETWEEN 1 AND 10 EACH 1));

An EXPLAIN of the SELECT statement shows the partition lock:

  Explain SELECT * FROM HLSDS.SLPPIT1 WHERE PC = 5;
  1) First, we lock HLSDS.slppit1 for read on a reserved RowHash in a 
      single partition to  prevent global deadlock.
  2) Next, we lock HLSDS.slppit1 for read on a single partition.
  3) We do an all-AMPs RETRIEVE step from a single partition of HLSDS.slppit1 
      with a condition of ("HLSDS.slppit1.Pc = 5") with a residual condition 
      of ("HLSDS.slppit1.Pc = 5") into Spool 1 (group_amps), which is built 
      locally on the AMPs. The size of Spool 1 is estimated with no confidence 
      to be 1 row (65 bytes). The estimated time for this step is 0.07 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved in 
      processing the request.

Example: SELECT Statements That Specify a System-Derived PARTITION Column in Their Select Lists

You can specify a system-derived PARTITION column in the select list of a statement. This example specifies an unqualified PARTITION column because its use is unambiguous:

     SELECT orders.*, PARTITION
     FROM orders
     WHERE orders.PARTITION = 10
     AND   orders.o_totalprice > 100;

PARTITION must be qualified in this SELECT statement in the select list and in the WHERE clause because the statement joins two tables and specifies the PARTITION columns of both in the WHERE clause:

     SELECT orders.*, lineitem.*, orders.PARTITION
     FROM orders, lineitem
     WHERE orders.PARTITION = 3
     AND   lineitem.PARTITION = 5
     AND   orders.o_orderkey = lineitem.1_orderkey;

You must specify PARTITION explicitly in the select list. If you specify * only, then PARTITION or PARTITION#L n column information is not returned.

Usually, the table is a row-partitioned table because PARTITION is 0 for a nonpartitioned table and 1 for a column-partitioned table without row partitioning.

Example: PARTITION Values Not Returned Because PARTITION Not Specified in the Select List

The following statement does not return PARTITION as a column value, even though the WHERE clause specifies PARTITION, because PARTITION is not specified in the select list.

     SELECT *
     FROM orders
     WHERE orders.PARTITION = 10
     AND   orders.o_totalprice > 19.99;

Example: Qualification of PARTITION Not Necessary Because Specification Is Unambiguous

PARTITION does not have to be qualified in this example because its use is unambiguous:

     SELECT orders.*, PARTITION
     FROM orders
     WHERE orders.PARTITION = 10
     AND   orders.o_totalprice > 100;

Example: Qualification of PARTITION Necessary to Avoid Ambiguity

PARTITION must be qualified in the two following examples to distinguish between PARTITION values in the orders table and PARTITION values in the lineitem table:

     SELECT *
     FROM orders, lineitem
     WHERE orders.PARTITION = 3
     AND   lineitem.PARTITION = 5
     AND   orders.o_orderkey = lineitem.l_orderkey;

     SELECT orders.*, lineitem.*, orders.PARTITION
     FROM orders, lineitem
     WHERE orders.PARTITION = 3
     AND   lineitem.PARTITION = 5
     AND   orders.o_orderkey = lineitem.l_orderkey;

Example: Selecting All Active Partitions from a Table

The following two examples provide a list of the populated row partitions in the orders table (assuming the maximum combined partition number for a populated row partition is 999 or 127, respectively, for the orders table):

     SELECT DISTINCT PARTITION (FORMAT '999')
     FROM orders
     ORDER BY PARTITION;

     SELECT DISTINCT CAST (PARTITION AS BYTEINT)
     FROM orders
     ORDER BY PARTITION;

Example: Using PARTITION with an Aggregate Function

The following example counts the number of rows in each populated row partition:

     SELECT PARTITION, COUNT(*)
     FROM orders
     GROUP BY PARTITION
     ORDER BY PARTITION;