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:
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:
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:
----------------------------------------------
Teradata database version:
VERSION 15.10.03.02
RELEASE 15.10.03.02
-----------------------------------------------
Well, the error message is correct, both RANGE_N (col5 BETWEEN 'a' AND 'z' ) and RANGE_N (col7 BETWEEN 1 AND 600 ) define a single partition.
To fix it add EACH for n:umeric partitions
RANGE_N (col7 BETWEEN 1 AND 600 EACH 1)
For character partitioning it's a bit more complicated, e.g.
RANGE_N (col5 BETWEEN 'a','b','c','d', 'e', .... 'z' and 'AND 'zzzzz' )
Thanks dnoeth...cheers