Wednesday, October 27, 2010

Teradata interview questions PART 4

Teradata interview questions PART 4
Teradata interview questions PART 4 consolidated over period of time from various sources.

Explain Different types of temporary tables in Teradata
Answer:

Derived temporary tables
  # Derived tables are local to an SQL query.
  # Not included in the DBC data dictionary database, the definition is kept in cache.
  # They are specified on a query level with an AS keyword in an sql statement

Volatile temporary tables
  # Local to a session ( deleted automatically when the session terminates)
  # Not included in the DBC data dictionary database and table definition is stored in cache. However, the volatile tables need to have unique names across the session.
  # Created by the CREATE VOLATILE TABLE sql statement

Global temporary tables
  # Global tables are local to a session and deleted automatically when the session terminates
  # A permanent table definition is stored in the DBC data dictionary database (DBC.Temptables)
  # defined with the CREATE GLOBAL TEMPORARY TABLE sql


How do you find out number of AMP's in the Given system
Answer:
 
Select HASHAMP () +1;


List types of HASH functions used in teradata?
Answer:

There are HASHROW, HASHBUCKET, HASHAMP and HASHBAKAMP.
The SQL hash functions are:
  • HASHROW (column(s))
  • HASHBUCKET (hashrow)
  • HASHAMP (hashbucket)
  • HASHBAKAMP (hashbucket)

Example:
SELECT
            HASHROW ('Teradata')   AS "Hash Value"
            , HASHBUCKET (HASHROW ('Teradata')) AS "Bucket Num"
            , HASHAMP (HASHBUCKET (HASHROW ('Teradata'))) AS "AMP Num"
            , HASHBAKAMP (HASHBUCKET (HASHROW ('Teradata')))  AS "AMP Fallback Num" ;


What are the basic criteria to select Primary Index column for a given table?
Answer:
A thumb rule of ADV demographics is followed.
Access Demographics
Identify index candidates that maximize one-AMP operations.
Columns most frequently used for access (Value and Join).

Distribution Demographics
Identify index candidates that optimize parallel processing.
Columns that provide good distribution.

Volatility Demographics
Identify index candidates with low maintenance I/O.

What are the basic rules that define how PI is defined in Teradata?
Answer:

The following rules govern how Primary Indexes in a Teradata Database must be defined as well as how they function:

One Primary Index per table.
A Primary Index value can be unique or non-unique.
The Primary Index value can be NULL.
The Primary Index value can be modified.
The Primary Index of a populated table cannot be modified.
A Primary Index has a limit of 64 columns.  


How to make sure Optimizer chooses NUSI over Full Table Scan (FTS)?
Answer:

A optimizer would prefer FTS over NUSI, when there are no Statistics defined on NUSI columns.
It is always suggested to collect statistics whenever NUSI columns are defined on the table.
Verify whether index is being used by checking in Explain plan.


What are the advantages and dis-advantages of secondary Indexes?
Answer:

Advantages:
1. A secondary index might be created and dropped dynamically
2.A table may have up to 32 secondary indexes.
3. Secondary index can be created on any column. .Either Unique or Non-Unique
4. It is used as alternate path or Least frequently used cases.  ex. defining SI on non indexed column can improve the performance, if it is used in  join or filter condition of a given query.
5. Collecting Statistics on SI columns make sure Optimizer 

Disadvantages
1. Since Sub tables are to be created, there is always an overhead for additional spaces.
2. They require additional I/Os to maintain their sub tables.
3. The Optimizer may, or may not, use a NUSI, depending on its selectivity.
4. If the base table is Fallback, the secondary index sub table is Fallback as well.
5. If statistics are not collected accordingly, then the optimizer would go for Full Table Scan.


When should the statistics be collected?
Answer:
Here are some excellent guidelines on when to collect statistics:
·       All Non-Unique indices
·       Non-index join columns
·       The Primary Index of small tables
·       Primary Index of a Join Index
·       Secondary Indices defined on any join index
·       Join index columns that frequently appear on any additional join index columns that frequently appear in WHERE search conditions
·       Columns that frequently appear in WHERE search conditions or in the WHERE clause of joins.
Where does TD store transient journal?
Answer:

In perm space -> dbc.transientjournal
But that special table can grow over dbc's perm limit until the whole system runs out of perm space.
How can you find the Teradata Release and Version from Data Dictionary Table?
Answer:

SELECT * FROM DBC.DBCINFO;   
    

How can you determine I/O and CPU usage at a user level in Teradata?
Answer:

SELECT ACCOUNTNAME, USERNAME, SUM (CPUTIME) AS CPU, SUM (DISKIO) AS DISKIO
FROM DBC.AMPUSAGE
GROUP           BY 1,2
ORDER            BY 3 DESC;


How can you track Login Parameters of users in Teradata?
Answer:
SELECT LOGDATE, LOGTIME, USERNAME, EVENT FROM DBC.LOGONOFF;

4 comments:

  1. Good Job Vinay!! Keep going!!

    ReplyDelete
  2. Contains good info. for rookies like me, thank you vinay

    ReplyDelete
  3. gud work vinay..all d best!!

    ReplyDelete
  4. excellent description. I like this article very much

    ReplyDelete