Social Icons

Interview Questions Part-2

Diff between database and user?
Both may own objects such as tables, views, macros, procedures, and functions. Both users and databases may hold privileges.
However, only users may log on, establish a session with the Teradata Database, and submit requests.

A user performs actions where as a database is passive. Users have passwords and startup strings; databases do not. Users can log on to the Teradata Database, establish sessions, and submit SQL statements; databases cannot.

Creator privileges are associated only with a user because only a user can log on and submit a CREATE statement. Implicit privileges are associated with either a database or a user because each can hold an object and an object is owned by the named space in which it resides

When a PI is not mentioned on a table, how will Teradata consider the PI for that table?

If you don't specify a PI at table create time then Teradata must chose one. For instance, if the DDL is ported from another database that uses a Primary Key instead of a Primary Index, the CREATE TABLE contains a PRIMARY KEY (PK) constraint. Teradata is smart enough to know that Primary Keys must be unique and cannot be null. So, the first level of default is to use the PRIMARY KEY column(s) as a UPI.
If the DDL defines no PRIMARY KEY, Teradata looks for a column defined as UNIQUE. As a second level default, Teradata uses the first column defined with a UNIQUE constraint as a UPI.
If none of the above attributes are found, Teradata uses the first column defined in the table as a NON-UNIQUE PRIMARY INDEX (NUPI).

What is covered query in Teradata?

If a SELECT query covers all the columns that are defined in the JOIN INDEX as join columns, such type of queries are called as COVERED query.
Multi-Column NUSI Columns used as a Covered Query
What is the use of  WITH CHECK OPTION in Teradata?
In Teradata, the additional key phase: WITH CHECK OPTION, indicates that the WHERE clause conditions should be applied during the execution of an UPDATE or DELETE against the view. This is not a concern if views are not used for maintenance activity due to restricted privileges.

What is identity column?
Teradata has a concept of identity columns on their tables beginning around V2R6.x. These columns differ from Oracle's sequence concept in that the number assigned is not guaranteed to be sequential. The identity column in Teradata is simply used to guaranteed row-uniqueness.
       (START WITH 1
        INCREMENT BY 20)
Granted, ColA may not be the best primary index for data access or joins with other tables in the data model. It just shows that you could use it as the PI on the table.

How to implement UPSERT logic in Teradata using SQL?

We have MERGE-INTO option available in Teradata data which works as an UPSERT logic in teradata. If the condition matches then update else insert.

Example: MERGE into dept_table1 as Taregt

                        USING (SELECT dept_no, dept_name, budget

                                    FROM dept_table where dept_no = 20) Source

                                    ON (Target.dept_no = 20)

                        WHEN MATCHED then

                                    UPDATE set dept_name = ëBeing Renamedí

                        WHEN NOT MATCHED then

                                    INSERT (dept_no, dept_name, budget)

                                    VALUES (source.dept_no, source.dept_name, source.budget);

What is value ordered NUSI?
When we define a value ordered NUSI on a column the rows in the secondary subtable get  sorted based on the secondary index value. The columns should be of integer or date type.
This is used for range queries and to avoid full table scans on large tables.

What are the DBQL tables.

Database Query Log tables are the tables present in DBC database which store the history of all the operations performed on the tables present in the databases.
The history could get very large so these tables should be purged when the data is no longer needed.

What is the difference between sample and top?

The Sampling function (SAMPLE) permits a SELECT to randomly return rows from a Teradata database table.
It allows the request to specify either an absolute number of rows or a percentage of rows to return. Additionally, it provides an ability to return rows from multiple samples.

SELECT * FROM student_course_table SAMPLE 5;

The TOP clause is used to specify the number of records to return. The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.


What is difference between ZEROIFNULL and NULLIFZERO ?
The  ZEROIFNULL function: will pass zero when data coming as null
The  NULLIFZERO function: will pass null when data coming as zero.

What is Range_N?
Range_N is defined on a partition primary index to specify the range of values of a column that should be assigned to a partition.
The number of partitions = the number of ranges specified + no case + unknown
no case ñ> if the value does not belong to any range
unknown -> for the values like nulls, spaces etc

What is Casting in Teradata ?

It will convert the data type
The casting is similar to DDL:
Can we do collect stats on a table when the table is being updated?
No.We never collect stats on a table when the table is being updated. It will go for dead lock.

When will you go for hash index?

A hash index organizes the search keys with their associated pointers into a hash file structure.
We apply a hash function on a search key to identify a bucket, and store the key and its associated pointers in the bucket (or in overflow buckets).
Strictly speaking, hash indices are only secondary index structures, since if a file itself is organized using hashing, there is no need for a separate hash index structure on it.

Difference between Global and Volatile Temporary Table? Where they can be used and how they can be used? 

Global Temporary Tables

  • Materialize in users temporary space
  • Base definitions are permanent in Data Dictionary tables
  • Definitions can be materialized by any user with the necessary DML privileges
  • Can be defined for COLLECT STATISTICS
  • Can survive a Teradata Database restart
  • Up to 2000 materialized tables per session
  • Materialized instance is local to a session
  • If not dropped manually, instance is dropped automatically at session end
  • An instance an be dropped manually any time during a session with DROP TEMPORARY TABLE
  • Materialized contents are not shareable with other sessions
  • A very large number of global tables can prolong logoff, because materialized global tables are dropped automatically at session end.

Volatile Tables

  • Occupy space in users SPOOL allocation
  • Definitions are kept in cache and go away at session end or during a Teradata Database restart
  • Private to the session in which they are created
  • Can be defined for COLLECT STATISTICS(TD14 feature)
  • Do not survive a Teradata Database reset
  • Up to 1000 tables per session
  • Local to a session
  • If not dropped manually, dropped automatically at session end
  • Can be dropped manually any time during a session with DROP TABLE
  • Contents are not sharable with other sessions
  • A very large number of volatile tables can prolong logoff, because volatile tables are dropped automatically at session end.
Related Posts:
Interview Questions Part-1                                                                     Interview Questions Part-3

No comments:

Post a Comment