Help needed in understanding somethings related to PARTITIONS

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
N/A

Help needed in understanding somethings related to PARTITIONS

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 Smiley Happy

 

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:

  1. For RANGE partition (which I think is level 2 as told in error) will give me more than two partitions. Am I right?
  2. 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:

  1. So is it mandatory to use NO RANGE/ NO CASE and UNKNOWN partitions for inner partitions?
  2. 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:

 

  1. 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

-----------------------------------------------

 

2 REPLIES
Highlighted
N/A

Re: Help needed in understanding somethings related to PARTITIONS

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' )

 

N/A

Re: Help needed in understanding somethings related to PARTITIONS

Thanks dnoeth...cheers