Archives of the TeradataForum

Message Posted: Mon, 11 May 2009 @ 09:26:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Monday, May 11, 2009 05:02 -->

Hi,

Thanks for the advice.

But probably I had to be more specific. The partitioning column at this client place is not part of the PI as such. So dbc.indices is also ruled out.

A sample DDL is like below :

     CREATE MULTISET TABLE   DBNAMe.Table_Name    ,NO FALLBACK    ,NO BEFORE
     JOURNAL    ,NO AFTER JOURNAL
     (
               Column1   INTEGER      NOT NULL      ,
               Column2   INTEGER            ,
               Column3   INTEGER            ,
               Column4   INTEGER         COMPRESS(NULL,1)   ,
               Column5   CHAR(8)      NOT NULL   COMPRESS('264')   ,
               Column6   CHAR(8)         COMPRESS(NULL)   ,
               Column7   DECIMAL(11,0)      NOT NULL
     )
     PRIMARY INDEX   NUPI_1   (      Column2  )
     PARTITION BY            RANGE_N(Column1  BETWEEN 200612  AND 200712
     EACH 1 )
     UNIQUE INDEX    USI_1   (      Column1, Column7   )
     ;

As you can see Column1 is part of Partitioning expression but not part of PPI. If I try to compress Column1, I get an Error :3623: The user cannot use COMPRESS on a primary index column or partitioning expression column.

So if there is any way to identify if a Column is part of Partitioning Expression, (without scanning through ConstraintText of dbc.indexconstraints), it would be quite helpful.

Best Regards

	<Prev	Next>		<<First	<Prev	Next>	Last>>