PARTITION INFORMATION

UDA
Enthusiast

PARTITION INFORMATION

Does anyone know where in the DBC partition information, that is not a PPI, is kept? I am trying to find the columns associated with a given partition on a table progamatically. A PPI's columns are kept in DBC.INDICES, but I can't find partition information on non-PPI partitions.

R
3 REPLIES
Senior Apprentice

Re: PARTITION INFORMATION

As a partition might be defined usng some cmplex calculation based on several columns, there's no column information, but if you query dbc.indexconstraints you'll find the definition in column ConstraintText.

Dieter
Enthusiast

Re: PARTITION INFORMATION

Hello,
I have been looking for answers to a basic question on table partitioning in Teradata. Can someone please shed more light. Here it is -

If we do Range Partitioning on a table based on Numeric values and then different jobs load data corresponding to different partition ranges in parallel - then do we get into a resource contention scenario OR do all parallel running jobs hit specific partition and only acquire lock on a particular partition. In short - the question is - while loading data into a partitioned table [in this case Range Partition], do we acquire a lock at the partition level and multiple programs can run in parallel acquiring lock at individual partitions OR the lock is acquired at whole table level and the parallel jobs get into a contention mode.

Ex: An error table where each program loads specific error information that is encountered based on error code. If the table is range partitioned on error codes of range of 1000s, will individual programs when run in parallel hit their specific partition acquiring only lock to it or they will acquire complete table lock and release them for subsequent jobs when error load is done.

Please advice.
Enthusiast

Re: PARTITION INFORMATION

Will the following SQL help?

select * from dbc.MultiColumnStats t
where databaseName = user
and tableName = 'your table name'
and exists (
select 1 from dbc.MultiColumnStats f
where f.DatabaseName = t.DatabaseName
and f.TableName = t.TableName
and f.StatisticsId = t.StatisticsId
and f.ColumnName = 'PARTITION'
)
order by StatisticsId, ColumnPosition
;