Hi All,
I came across following situations for PARTITION accidently but am not able to get through it or unable to understand the error. There seems some ambiguity. Please help me to get out of it 
When I am trying to execute the following statement, I am getting error “The number of partitions for level 2 is less than two”
CREATE MULTISET TABLE table_1
(
col1 INTEGER,
col2 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
col3 INTEGER NOT NULL,
col4 TIMESTAMP(0),
col5 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
col6 INTEGER
)
PRIMARY INDEX ( col1 ,col5 )
PARTITION BY (
CASE_N( col6<200,col6<300,col6 <400),
RANGE_N (col5 BETWEEN 'a' AND 'z' )
);
Questions:
- For RANGE partition (which I think is level 2 as told in error) will give me more than two partitions. Am I right?
- Are there any precautions I should take while dealing with CHAR or VARCHAR data in partitions?
But again, following is getting into the same: so for both the question (1 and 2) are getting nullified
CREATE MULTISET TABLE table_1
(
col1 INTEGER,
col2 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
col3 INTEGER NOT NULL,
col4 TIMESTAMP(0),
col5 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
col6 INTEGER,
col7 INTEGER
)
PRIMARY INDEX ( col1 ,col5 )
PARTITION BY (
CASE_N( col6<200,col6<300, NO CASE, UNKNOWN),
RANGE_N (col7 BETWEEN 1 AND 600 )
);
And then, just using NO RANGE and UNKNOWN partitions for level 2 partitions it is working.
CREATE MULTISET TABLE table_1
(
col1 INTEGER,
col2 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
col3 INTEGER NOT NULL,
col4 TIMESTAMP(0),
col5 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
col6 INTEGER
)
PRIMARY INDEX ( col1 ,col5 )
PARTITION BY (
CASE_N( col6<200,col6<300),
RANGE_N (col5 BETWEEN 'a' AND 'z' ,NO RANGE, UNKNOWN)
);
CREATE MULTISET TABLE table_1
(
col1 INTEGER,
col2 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
col3 INTEGER NOT NULL,
col4 TIMESTAMP(0),
col5 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
col6 INTEGER,
col7 INTEGER
)
PRIMARY INDEX ( col1 ,col5 )
PARTITION BY (
CASE_N( col6<200,col6<300, NO CASE, UNKNOWN),
RANGE_N (col7 BETWEEN 1 AND 600, NO RANGE , UNKNOWN )
);
Questions:
- So is it mandatory to use NO RANGE/ NO CASE and UNKNOWN partitions for inner partitions?
- If it is for other levels, it should be mandatory for first level of partition as well. Because it is said that each partition can be addressed independently, even in MULI LEVEL PARTITION.
One more:
Following statement will work. In both the levels I am using same column.
CREATE MULTISET TABLE table_1
(
col1 INTEGER,
col2 VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
col3 INTEGER NOT NULL,
col4 TIMESTAMP(0),
col5 VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
col6 INTEGER,
col7 INTEGER
)
PRIMARY INDEX ( col1 ,col5 )
PARTITION BY (
CASE_N( col6<200,col6<300, NO CASE, UNKNOWN),
RANGE_N (col6 BETWEEN 1 AND 600, NO RANGE , UNKNOWN )
);
Questions:
- It will definitely be wrong practice but if is allowed, it is introducing ambiguity. Please comment if there are some scenarios for which this will be useful
----------------------------------------------
Teradata database version:
VERSION 15.10.03.02
RELEASE 15.10.03.02
-----------------------------------------------
