A rowkey-based merge join for single-level and multilevel partitioning requires equality conditions on all the primary index columns and partitioning columns of the two relations.
To be eligible for a rowkey-based merge join, both relations must also have the same partitioning. Otherwise, one of the relations must be spooled and partitioned to impose equal partitioning between the two.
Vantage does not support full outer rowkey-based merge joins with partition remapping for row-partitioned PI tables having 8-byte partitioning.
Also see Necessary Conditions for a Rowkey-Based Merge Join With a Character-Partitioned Relation below for additional restrictions that apply for character PPIs.
Supported Join Methods for Rowkey-Based Merge Join
- Fast path inner, left and full outer merge join.
- Slow path inner and left outer merge join.
- Fast path inner and outer inclusion/exclusion merge join.
- Slow path inner and outer inclusion/exclusion merge join.
- Fast path correlated inclusion/exclusion merge join.
- Slow path correlated inclusion/exclusion merge join.
Note that this type of join can only occur between a table and a spool, and not directly between two tables.
Right outer merge join is not supported for a rowkey-based merge join. Note that the Optimizer can switch the relations so that the join type is a left outer merge join.
- Slow path left outer merge join.
- Slow path inner merge join.
A rowkey-based merge join can be considered for a join between a primary-indexed table and another table whose primary index is not a join condition. To make such a join, the system builds the rowkey for the other table in spool based on the partitioning expressions of the row-partitioned PI table on the other side of the join.
Unsupported Rowkey-Based Merge Join Partition Elimination Feature
Vantage does not support dynamic row partition elimination for full outer rowkey-based merge joins with partition remapping for 8-byte partitioning.
Making a Row-partitioned PI-to-Spool Join
In this example, the database can select rows from the dimension tables and join the intermediate results set to form a spool that is sorted by a rowkey matching that of the row-partitioned PI table. Then the system can do a rowkey-based merge join from the PPI table to the spool.
This method replaces having to redistribute the spool and then sorting it on the join columns, with the other tables joined and spool also sorted on the join columns, and then joining the two spools using a rowhash merge join or some other join plan. Ultimately, the Optimizer selects the plan that it estimates to be the least costly of its available options.
Definition DDL request text for the markets table.
CREATE TABLE markets ( productid INTEGER NOT NULL, region BYTEINT NOT NULL, activity_date DATE FORMAT 'yyyy-mm-dd' NOT NULL, revenue_code BYTEINT NOT NULL, business_sector BYTEINT NOT NULL, note VARCHAR(256)) PRIMARY INDEX (productid, region) PARTITION BY ( RANGE_N(region BETWEEN 1 AND 9 EACH 3), RANGE_N(business_sector BETWEEN 0 AND 49 EACH 10), RANGE_N(revenue_code BETWEEN 1 AND 34 EACH 2), RANGE_N(activity_date BETWEEN DATE '1986-01-01' AND DATE '2007-05-31' EACH INTERVAL '1' MONTH));
Definition DDL request text for the products table is as follows:
CREATE TABLE products ( productid INTEGER NOT NULL, product_name CHARACTER(30), description VARCHAR(256)) PRIMARY INDEX (productid);
Definition DDL request text for the regions table is as follows:
CREATE TABLE regions ( region INTEGER NOT NULL, region_name CHARACTER(30), description VARCHAR(256)) PRIMARY INDEX (region_name);
Definition DDL statement text for the business_sectors table is as follows:
CREATE TABLE business_sectors ( productid INTEGER NOT NULL, business_sector_name CHARACTER(30), description VARCHAR(256)) PRIMARY INDEX (business_sector_name);
Definition DDL statement text for the revenue_codes table is as follows:
CREATE TABLE revenue_codes ( revenue_code INTEGER NOT NULL, revenue_code_name CHARACTER(30), description VARCHAR(256)) PRIMARY INDEX (revenue_code_name);
Definition DDL statement text for the activity_calendar table is as follows:
CREATE TABLE activity_calendar ( quarter CHARACTER(6), activity_date DATE FORMAT 'yyyy-mm-dd' NOT NULL, description VARCHAR(256)) PRIMARY INDEX (quarter);
Example query that joins all these tables is as follows:
SELECT p.product_name, r.region_name, b.business_sector_name,
rc.revenue_code_name, a.quarter, m.activity_date, m.note
FROM markets AS m, products AS p, regions AS r,
business_sectors AS b, revenue_codes AS rc,
activity_calendar AS a
WHERE p.productid BETWEEN 4000
AND 4999
AND r.region_name = 'West'
AND b.business_sector_name IN ('Cosmetics','Snack Food','Hats')
AND rc.revenue_code_name IN ('Web', 'Catalog')
AND a.quarter IN ('2006Q1', '2005Q1')
AND m.productid = p.productid
AND m.region = r.region
AND m.business_sector = b.business_sector
AND m.revenue_code = rc.revenue_code
AND m.activity_code = a.activity_code;
Necessary Conditions for a Rowkey-Based Merge Join With a Character-Partitioned Relation
- The relations must have the same collation.
- The relations must have the same server character set for their character partitioning columns.
- The character partitioning columns of the relations must have the same data type, including column length and case specific attribute.
Unless all of these conditions are true for both of the relations to be joined, one of them must be spooled and then repartitioned to match the partitioning of the other partitioned relation in the join.
- The estimated cost of the join must be less than the estimated cost of all other join types.
- There must be no more than one character partitioning column at each partitioning level.
- If the session collation or table collation is MULTINATIONAL or CHARSET_COLL, and if any comparison or string function involving any non-constant expression in the partitioning expression at any partitioning level is case-insensitive, the session collation must match the table collation.The following functions and attribute are case-insensitive:
- LOWER function
- SOUNDEX function
- UPPER function
- UPPERCASE attribute
The following functions are case-sensitive:- CHAR2HEXINT
- TRANSLATE
- TRANSLATE_CHK
- TRIM
- VARGRAPHIC
Presence of the concatenation operator (||) marks the expression as having the property of being both case sensitive and case-insensitive.
The following functions follow the same rules as comparison operators, function input arguments, and the default case sensitivity for the session mode that was in effect when the character partitioning was created or modified, and the system examines them all to determine case sensitivity.- INDEX
- MINDEX
- POSITION
Specifying the SUBSTRING function does not affect case sensitivity.
- If the WHERE clause predicate that qualifies a partitioning level for a rowkey-based merge join is case insensitive, then all comparisons and all string functions in any non-constant expressions in the partitioning expression for that level must also be case-insensitive.
The WHERE clause predicate is considered to be case insensitive if any of the comparisons or string functions involving non-constant expressions in the condition is case-insensitive.
- If the character set of the character partitioning column in an equality join condition is different than character set of the expression involving that column in the other relation in the join condition, then you must store the partitioning column data using the Unicode server character set.
This is because the system handles comparisons between non-constant character expressions by implicitly converting both expressions to Unicode before making the comparison.
For the Optimizer to specify a rowkey-based merge join, the collation for the current session need not match the collation for the table when all equality join terms on character partitioning columns are case sensitive.
If you create a character-partitioned table in an ANSI mode session, the system implicitly casts the partitioning columns compared in CASE_N-based partitioning expressions or generalized partitioning expressions to CASESPECIFIC unless the constant expression in the comparison is explicitly CAST as NOT CASESPECIFIC.
This has an effect on the types of join terms that can be evaluated using rowkey-based merge join with such a partitioned primary index (see the example of a case where a rowkey-based merge join cannot be used later in this topic).
In this example, 2 character-partitioned tables are joined on their primary indexes and partitioning expressions. Assume the following table definitions, both of which were created under the same session collation:
CREATE SET TABLE test1, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL,CHECKSUM = DEFAULT ( i INTEGER, j CHARACTER(4) CHARACTER SET UNICODE CASESPECIFIC) PRIMARY INDEX (i) PARTITION BY RANGE_N(j BETWEEN 'AAAA','ZZZZ','aaaa','yyyy' AND 'zzzz'); CREATE SET TABLE test11, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( i INTEGER, j CHARACTER(4) CHARACTER SET UNICODE CASESPECIFIC) PRIMARY INDEX (i) PARTITION BY RANGE_N(j BETWEEN 'AAAA','ZZZZ','aaaa','yyyy' AND 'zzzz');
Assume you submit the following SELECT request that joins these tables:
SELECT * FROM test1 INNER JOIN test11 WHERE test1.i = test11.i AND test1.j = test11.j;
- The relations have the same session collation for their character partitioning columns.
- The relations have the same server character set for their character partitioning columns.
In this case, the matching server character set is Unicode for character partitioning column j in both relations.
- The relations have the same data type, column length, and case specific attribute for character partitioning column j.
In this case, the data types (CHARACTER), their lengths (4 characters), and their case specificity (CASESPECIFIC) are the same for character partitioning column j in both relations.
In this example, 2 tables, only one of which has character partitioning, are joined on their primary indexes. Assume the following table definitions, both of which were created under the same session collation:
CREATE TABLE markets ( product_name VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC, region BYTEINT NOT NULL, activity_date DATE FORMAT 'yyyy-mm-dd' NOT NULL, revenue_code BYTEINT NOT NULL, business_sector BYTEINT NOT NULL, note VARCHAR(256)) PRIMARY INDEX (productname) PARTITION BY (RANGE_N(productname BETWEEN 'A','B','C','D','E','F', 'G','H','I','J','K','L', 'M','N','O','P','Q','R', 'S','T','U','V','W','X', 'Y','Z' AND 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ', NO RANGE, UNKNOWN)); CREATE TABLE products ( product_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC, description VARCHAR(256)) PRIMARY INDEX (productname);
Note that markets is a character-partitioned relation, while products is not.
Assume you submit the following SELECT request that joins these tables:
SELECT * FROM products INNER JOIN markets WHERE products.product_name = markets.product_name;
- Only one of the relations in the join, markets, has partitioning.
- The relations do not have the same case-specific attribute for the join column product_name.
In this case, the case specificity is CASESPECIFIC for column product_name in relation markets, but NOT CASESPECIFIC for character partitioning column product_name in relation products.
Instead of using a rowkey-based merge join, the system selects rows from the products table to form a spool that it then sorts by a rowkey that is the same as that for the character-partitioned relation markets, and it then makes a rowkey-based merge join from the character-partitioned relation markets to the spooled relation products.
The session collation need not match the character-partitioned table collation because all comparisons in the partitioned table are case specific, but if the table collation definition changes after the table was created (this is applicable to the MULTINATIONAL and CHARSET_COLL collations only), then the Optimizer does not specify a rowkey-based merge join.
The following CREATE TABLE requests are submitted in ANSI session mode:
CREATE MULTISET TABLE tt1( a INTEGER, b CHARACTER(30) NOT CASESPECIFIC) PRIMARY INDEX (a) PARTITION BY CASE_N(b BETWEEN 'A' AND 'Z', b BETWEEN 'a' AND 'z', NO CASE OR UNKNOWN); CREATE MULTISET TABLE tt2( a INTEGER, b CHARACTER(30) NOT CASESPECIFIC) PRIMARY INDEX (a);
The following SELECT request joining these tables is submitted in Teradata session mode:
SELECT * FROM tt1 INNER JOIN tt2 WHERE tt1.a = tt2.a AND tt1.b = tt2.b;
The system cannot use a rowkey merge join for this request because the comparisons in the partitioning expression are case-sensitive, but the join conditions in the SELECT request are case-insensitive.
The following CREATE TABLE requests are submitted in Teradata session mode:
CREATE MULTISET TABLE tt3( a INTEGER, b CHARACTER(30) NOT CASESPECIFIC) PRIMARY INDEX (a) PARTITION BY CASE_N(TRIM(LEADING 'a' FROM b) BETWEEN 'A' AND 'Z', TRIM(LEADING 'a' FROM b) BETWEEN 'a' AND 'z', NO CASE OR UNKNOWN); CREATE MULTISET TABLE tt2( a INTEGER, b CHARACTER(30) NOT CASESPECIFIC) PRIMARY INDEX (a);
The following SELECT request joining these tables is submitted in Teradata session mode:
SELECT * FROM tt3 INNER JOIN tt2 WHERE tt3.a = tt2.a AND tt3.b = tt2.b;
The Optimizer cannot select a rowkey merge join for this case because the comparisons in the partitioning expression for tt3 are case-sensitive because of the TRIM function, but the join conditions in the SELECT request are case insensitive.