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 it 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 would be a row-partitioned table because:
- For a nonpartitioned table, PARTITION is always 0.
- For a column-partitioned table without row partitioning, PARTITION is always 1.
Example: PARTITION Values Not Returned Because PARTITION Not Specified in the Select List
In this example, the value of PARTITION is not returned as one of the column values, even though it is specified in the WHERE clause, because it was not explicitly specified in the select list for the query:
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;