As any experienced Teradata DBA will tell you, some tables in the Teradata dictionary (DBC) need housekeeping. But some customer sites that I have worked with ignore table DBC.AccessRights.
If you have ever analyzed this table on a Teradata system, you will almost certainly spot two things quickly.
- A non-history table, it can contain lots of rows.
- It is very skewed.
As this is a system table, you cannot affect the table definition, so reducing the skew is not simply changing the Primary Index. Still, as with all skewed tables, the skew will typically reduce if you can reduce the number of rows.
Further analysis of this table’s content will usually show that many rows are ‘redundant.’ By that, I mean the situation where a user has a particular Access Right at the object level (say SELECT on a table). They also have the same Access Right at the database level. Therefore, the object level Access Right serves no purpose because the user can SELECT from the table without it.
Housekeeping this table can have significant effects: one customer where a process was implemented reduced the row count by @40%, which also helped with parsing CPU consumption.
To effectively housekeep this table, some best practices have to be put into place:
- The normal batch process to identify and remove redundant Access Rights.
- Use Roles instead of direct grants of Access rights to users.
- Prevent DROP/CREATE staging tables using ETL processes (see my article “DELETE, don’t DROP“).
As a side note, the impact of skewing in this table should be reduced to TD 15.00 because the table now has a PPI on it, but the above steps will still help.