Accessing Partition ID for a Table

Database
Enthusiast

Accessing Partition ID for a Table

Hi

Do we have any functions available to access partition id or row id for a table in Teradata like we have hash functions e.g. hashbucket,hashamp.

Thanks
9 REPLIES
Enthusiast

Re: Accessing Partition ID for a Table

I beleive, teradata identifies the partitions by partition numbers.In order to access a row when there is a valid condition on partition column and primary index in your where clause, teradata would use a row key (partition number/id+rowhash) to fetch the row

if Iam not mistaken you can use the keyword PARTITION in your select query to get the partition number in which your rows resides.
Senior Apprentice

Re: Accessing Partition ID for a Table

Well, yes...

PARTITION and ROWID, but ROWID is usually switched off, so you can't use it anymore.

Dieter
Enthusiast

Re: Accessing Partition ID for a Table

Thanks.

Also do we such values present in any of the DBC tables or stored elsewhere in PE or BYNET?

Re: Accessing Partition ID for a Table

select partition,count(*) from the tablename
group by 1

would give the number of rows in the partition
Enthusiast

Re: Accessing Partition ID for a Table

When I executed the below query

"select partition, count(*) from
tablename
group by 1"

I got this error.

5879: Invalid Partition field.
Enthusiast

Re: Accessing Partition ID for a Table

Whats the Teradata version you are using?

I think the system derived column "Partition" is available only from V2R6.1

Regards,
Annal T
Senior Apprentice

Re: Accessing Partition ID for a Table

PARTITION exists since V2R5, but you select from a view:

5879 Invalid Partition field.
Explanation: Views and derived tables do not have a
Partition field.
Generated By: RES Module.
For Whom: End-User
Remedy: Rewrite the query without using the Partition
field and re-submit the query.

Dieter
Enthusiast

Re: Accessing Partition ID for a Table

Yes. Partition exists from V2R5.

But i thought usage of Partition column for purposes like statistics collection etc
was offered to the users only from V2R6.

Regards,
Annal T
Enthusiast

Re: Accessing Partition ID for a Table

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.