FAQ

Lots of empty partitions

Q: Is there any problem defining lots of empty partitions on a Partitioned Primary Index (PPI) table? At my site they carry hundreds of empty partitions up to the year 2010 to avoid having to use the command ALTER TABLE. We don't do any appreciable primary index (PI) access, but there is PI join access to other tables, which may or may not be partitioned. I've noticed that the optimizer sometimes produces a join plan that unnecessarily accesses all partitions.

A: The optimizer does not recognize if a partition is empty or not. In Teradata Database V2R6.1 you will have the ability to collect statistics on the column "partition," and that will eliminate the problem you are experiencing here. But join-planning in particular is at risk for a less than optimal plan when a PPI table with a high percentage of empty partitions is involved.

Meanwhile, the recommended approach is to carry at most a few empty partitions and do periodic ALTER TABLEs.

Read-only administrators

Q: Can you set up user privileges so that a group of DBAs can only access information in Teradata Manager, but not change any settings? We have one group of application DBAs who would like to be able to monitor things as they develop their workload management "plan," but there is a hesitancy to give them full administrator privileges to go in and change rules and settings.

A: Yes, you can create different "profiles" (not the same as a database profile) for each Teradata Manager user. The profile determines which items are available in the menus and restricts certain operations like aborting a query.

Also you can adjust the monitor privileges that are granted to the user to prevent them from issuing requests to abort a query or change the query account string.

Internal format of MLOAD/FEXP data files

Q: Is there a way to take an "internal format" data file and somehow output it back to VARTEXT mode, perhaps using one of the utilities?

A: Please run FEXP again using MODE RECORD and FORMAT TEXT with a SELECT that does a TRIM of a CAST to CHAR data with a literal separator (i.e. ':') concatenated between each column.

Teradata is actually quite fast at this, and you will find this is much easier than converting and sorting a different file.

Partitions and indexes

Q: Please explain the theory behind why a Unique Primary Index (UPI) cannot be defined if the partitioning column is not included as a part of the primary index (PI).

A: Teradata has to check for duplicate UPIs when inserting rows. If all partition columns are not included in UPI, then Teradata may have to search in all partitions for existing rows with the same UPI values that may conflict with a new incoming row.

Execute a macro with multiple criteria

Q: Is it possible to execute a macro with multiple criteria for a variable?

For example:

          replace macro my_macro(variable integer) as (sel * from table1 where field in :variable;);

          execute my_macro( (1,2,3) );

A: The commas will cause you a problem. We have found that using a single varchar works best, instead of using some number of parameters. Then, instead of using an IN, use a POSITION to determine whether or not the values match:

          execute my_macro( ('1 2 3') );

          my_macro (parm_value varchar(150))
          SEL ...
          where POSITION(column_value IN :parm_value) > 0;

Whether you are using char or numeric data, the space separator is important.

For instance, if you were checking states with the command

          exec my_macro ('HIAL')

you would get results for Hawaii (HI), Alabama (AL) and Iowa (IA) because there isn't a space between HI and AL.

In the example above, you would get 1, 2, 3, 12, 23 and 123 without the space.

The downside of using this technique for numeric values is that the actual column value will need to be converted to char before the comparison can be made. If you are referencing thousands or more rows, this will slow the processing to a degree. T