The key guideline for determining the optimum granularity for the partitions of a partitioned table or join index is the nature of the workloads that most commonly access the object. The higher the number of partitions you define for an object, the more likely an appropriate range query against the table will perform more quickly, given that the partition granularity is such that the Optimizer can eliminate all but one partition or a few partitions.
On the other hand, it is generally best to avoid specifying too fine a partition granularity. For example if query workloads never access data at a granularity of less than one month, there is no benefit to be gained by defining partitions with a granularity of less than one month. Furthermore, unnecessarily fine partition granularity is likely to increase the maintenance load for a partitioned table, which can lead to degradation of overall system performance. In the end, even though too fine a partition granularity itself does not itself introduce performance degradations, the underlying maintenance on such a table can indirectly degrade performance.
Determining the Maximum Number of Partitions for a Partitioned Database Object
When you define a partitioned database object, the question of how many partitions to define per partitioning level must be addressed.
If the table or join index is defined with multiple partitioning levels, you should explicitly specify the number of partitions per partitioning level using the ADD option (see “CREATE TABLE” in SQL Data Definition Language). If you do not, Teradata Database reserves any excess partitions for partitioning level 1, and these partitions cannot be added to any other partitioning level once the table or join index is created.
The maximum number of partitions that are available for the level 1 partition from excess partitions depends on whether you have defined the object with 2byte partitioning or with 8byte partitioning.
 For a columnpartitioning level, if
 You do not specify an ADD clause for the columnpartitioning level
 There is also row partitioning
AND
 At least one of the rowpartitioning levels does not specify an ADD clause
Then the maximum number of partitions for the columnpartitioning level is the number of column partitions defined plus 10.
 For a columnpartitioning level, if
 You do not specify an ADD clause for the columnpartitioning level
 There is also row partitioning
 All of the rowpartitioning levels specify an ADD clause
AND
 Using the number of column partitions defined plus 10 as the maximum number of column partitions, the partitioning would be 2byte partitioning
Then the maximum number of partitions for the columnpartitioning level is the largest value that does not cause the partitioning to be 8byte partitioning.
Otherwise, the maximum number of partitions for the columnpartitioning level is the largest value that does not cause the maximum combined partition number to exceed 9,223,372,035,854,775,807.
For singlelevel partitioning, the maximum combined partition number is the same as the maximum partition number for the partitioning level.
 For a columnpartitioning level, the maximum partition number is 1 more than the maximum number of partitions for that level. This is so that at least 1 unused column partition number is always available for altering a column partition.
 For each rowpartitioning level without an ADD clause in level order, the maximum number of partitions for the rowpartitioning level is the largest value that does not cause the partitioning to be 8byte partitioning if using the number of row partitions defined as the maximum for this and any lower rowpartitioning level without an ADD clause the partitioning would be 2byte partitioning, is the largest value that does not cause the partitioning to be 8byte partitioning.
Otherwise, the maximum number of partitions for this level is the largest value that does not cause the maximum combined partition number to exceed 9,223,372,035,854,775,807.
 The maximum number of partitions for a rowpartitioning level is at least 2.
Otherwise, Teradata Database aborts the request and returns an error to the requestor. This error occurs when only one partition is defined for a rowpartitioning level with an ADD 0 or with no ADD option and the maximum is not increased to at least 2.
 Having a maximum number of partitions for a partitioning level that is larger than the number of defined partitions for that level might enable the number of defined partitions for that level to be increased using an ALTER TABLE request.
The maximum number of partitions for a level cannot be increased.
 For a rowpartitioning level, the maximum partition number is the same as the maximum number of partitions for that level.
 The maximum combined partition number is the product of the maximum partition number determined for each partitioning level. The value cannot exceed 9,223,372,036,854, 775,807; otherwise, Teradata Database aborts the request and returns an error to the requestor.
For singlelevel partitioning, the maximum combined partition number is the same as the maximum partition number for the partitioning level.
 The number of combined defined partitions is the product of the number of defined partitions for each partitioning level.
 The maximum number of combined partitions is the product of the maximum number of partitions determined for each partitioning level.
For singlelevel partitioning, the maximum number of combined partitions is the same as the maximum number of partitions for the partitioning level.
If there is column partitioning, the maximum number of combined partitions is smaller than the maximum combined partition number or it is the same.
 If the maximum combined partition number is greater than 65,535, the partitioning is 8byte partitioning; otherwise, it is 2byte partitioning.
Singlelevel column partitioning with no ADD option is 2byte partitioning.
 The rules listed in the following table apply to 2byte partitioning and to 8byte partitioning for both rowpartitioned and columnpartitioned tables and join indexes.
For this partitioning …  The maximum number of partitions for the first level is increased to …  And the maximum number of partitioning levels is … 

2byte  the largest value that does not cause the maximum combined partition number to exceed 65,535 if it does not already do so. If there is at least one level with an explicit ADD clause, there is at least one level that consists solely of a RANGE_N function with BIGINT data type, or there is columnpartitioning, this is repeated for each of the other levels, if any, from the second level to the last level. 
15 
8byte  the largest value that does not cause the maximum combined partition number to exceed 9,223,372,036,854,775, 807 if it does not already do so. This is repeated for each of the other levels from the second level to the last level. 
62 
You can alter any level to have between 1 and the maximum number of partitions allowed for that partitioning level.
Assigning Excess Combined Partitions to Partitioning Levels
Teradata Database assigns leftover partitions to different partitioning levels depending the following set of rules.
IF the partitioning is …  THEN Teradata Database adds as many leftover combined partitions as possible to the …  

singlelevel  first and only row or column partitioning level and the ADD value is overridden if one is specified.  
multilevel and …  
all the row partitioning levels have an ADD clause, but there is a column partitioning level without an ADD clause  columnpartitioning level, which does not need to be at the first partitioning level.  
a column partitioning level and at least one of the row partitioning levels does not specify an ADD clause, including the case where none of the row partitioning levels specify an ADD clause  first rowpartitioning level without an ADD clause after using a default of ADD 10 for the columnpartitioning level. This is repeated for each of the other row partitioning levels without an ADD clause in partitioning level order. 

a column partitioning level has an ADD clause and at least 1 of the row partitioning levels does not specify an ADD clause  first row partitioning level without an ADD clause. This is repeated for each of the other row partitioning levels without an ADD clause in partitioning level order 

there is no column partitioning level and at least 1 of the row partitioning levels does not have an ADD clause, including the case where none of the row partitioning levels specify an ADD clause  
all the partitioning levels specify an ADD clause or after applying leftover combined partitions  first row or column partitioning level and the ADD clause for the first partitioning level, if specified, is overridden. If there at least 1 level that specifies an explicit ADD clause, there is at least 1 level that consists only of a RANGE_N function with BIGINT data type, there is column partitioning, or the partitioning is 8byte, this is repeated for each of the other levels from the second level to the last level. 