Access rights and their abbreviations

Access rights and their abbreviations, (partial list):

DATABASE = CREATE (CD) + DROP (DD)

USER = CREATE (CU) + DROP (DU)

TABLE = CREATE (CT) + DROP (DT)

VIEW = CREATE (CV) + DROP (DV)

MACRO = CREATE (CM) + DROP (DM)

TRIGGER = CREATE (CG) + DROP (DG)

PROCEDURE = CREATE (PC) + DROP (PD)

FUNCTION = CREATE (CF) + DROP (DF)

INDEX Table Column IX

REFERENCES Table Column RF

SELECT/RETRIEVE Table or View R

UPDATE Table or View U

INSERT Table or View I

DELETE Table or View D

EXECUTE Macro E

CHECKPOINT Journal Tables CP

DUMP Data and/or Journal Tables DP

RESTORE Data and/or Journal Tables RS

CREATE ROLE Create a role CR

DROP ROLE Drop a role DR

CREATE PROFILE Create a profile of user attributes CO

DROP PROFILE Drop a profile of user attributes DO

CREATE PROCEDURE Stored procedure PC

DROP PROCEDURE Stored procedure PD

EXECUTE PROCEDURE Stored procedure PE

SET SESSION RATE SS

SET RESOURCE RATE SR

ABORT SESSION AS

MONITOR RESOURCE MR

MONITOR SESSION MS

Note:-
The RESTORE statement also allows the recipient to execute ROLLBACK, ROLLFORWARD, and DELETE JOURNAL commands in the ARC facility.

The DROP allows COMMENT ON and COLLECT STATISTICS on the object.

Space allocation information

DBC.DiskSpace[X] This view gives AMP vproc information about disk space usage for any database or account. It gets this information from the ALL table.

DBC.TableSize[X] This view gives AMP vproc information about disk space usage (excluding spool) for any table or account.

DBC.AllSpace[X] This view gives AMP vproc information about disk space usage (including spool) for any database, table, or account. It also includes ALL table which should be excluded before summing.

Where and AND clause filter with JOIN

Execution order of WHERE and AND clause

inner join:
1. WHERE clause is applied first and then JOIN is performed
2. Additional AND clause is filtered first and then JOIN is performed

outer join:
1. Additional WHERE clause filtered last after the JOIN is performed.
2. Additional AND clause is applied along with JOIN performed

Table Skewness and PI Distribution Check

locking rows for access
select avg(RC) as avg_rows
,max(RC) as Max_rows
,max(RC)/avg(RC) as Skew_Factor
,count(*) as distict_AMPs
,sum(RC) as RC
from
(select hashamp(hashbucket(hashrow(PI))) ,  cast(count(*) as float) as RC
 from  dbname.tablename
 group by 1
) dt;

What Confidence Level mean in EXPLAIN?

High Confidence
1. PI collect stats done.
2. Filter criteria given (range or equality) with PI and collect stats done on that column/Index. Preferabley single selection criteria.
3. Not part of join (usually)

Low Confidence
1. Stale stats exist.
2. Filter has NUSI criteria with NO collect stats
3. In case of join, stats should be there on all columns involved.

Index Join Confidence
1. Its for join step only
2. Collect stats done on index column involved.

No Confidence
1. No stats done.
2. Filter has non-equality criteria (often lead to full table scan)
3. Selection of NON Indexed columns
4. Predecessor step had no confidence as well.

COLLECT STATs quick reference guide

COLLECT STATS are always done at Column/Multi-column/Index level. Table level stats only refresh the earlier defined column/index level stats. It doesn't mean that it will do collect stats on all columns unless it was earlier done for all columns individually once.


Collect stats just derives the data demographics of the table. These demographics are useful for optimizer to decide the execution of given query which in turn improves performance. Collected statistics are stored in DBC.TVFields or DBC.Indexes tables. However, these two tables cannot be queried .
For Perm tables at column or index level:
COLLECT STATS ON TABLE_NAME COLUMN/INDEX (COLUMN_NAME);

For GT tables:
COLLECT STATS ON TEMPORARY TABLE_NAME COLUMN/INDEX (COLUMN_NAME)

For Table level collect stats refresh:
COLLECT STATS ON TABLE_NAME;


It collects the information like:

• total row counts of the table,
• how many distinct values are there in the column,
• how many rows per value, is the column indexed,
• if so unique or non unique etc.

To know if collect stats done on table:
HELP STATISTICS TABLE_NAME ;
When to refresh COLLECT STATS:
1.    Typical guideline is  roughly 10% of the data has changed. (By measuring delta in perm space since last collected.) 
2.    Recollect based on stats that have aged 60-90 days. (say last time stats collected was 2 months ago) .
Note:
1. If you delete the table , the old stats still exists as there is no automatic stats updation in Teradata.
2. If you drop the table, then stats will also be dropped