Sunday, May 29, 2011

how do you you implement Multi valued compression in an existing table?


MVC can be implemented on following conditions
1. A new column with multi valued compression can be added to an existing table, but cannot modify existing compressed column.
2. Create a new table with column which has MVC and do insert .. select from original table
    Or
  CREATE TABLE... as with column designated MVC.

Friday, May 27, 2011

list Built-in functions used in teradata ?


The main functionality of built in functions is  that they dont need any arguments or paramaters and can be used directly with select to return system values.
Some of them are


• SESSION: – Returns a number for the session for current user .
• TIME: – this function provides the current time based on a 24-hour day
• USER: –  This one gives the user name of the current user.
• ACCOUNT: – display's your Teradata Account information
• CURRENT_DATE: – Returns the current system date
• CURRENT_TIME: - This function returns the current system time and current session ‘Time Zone’ displacement.
• CURRENT_TIMESTAMP: - Returns the current system timestamp  with TimeZone
• DATABASE: –  It  returns the name of the default database for the current user.
• DATE: – same as Current_DATE   and is teradata built in .

Thursday, May 26, 2011

How do you whether table is locked or not?

Just run the following query on the table.

Lock Table DBNAME.TABLENAME write nowait
Select * from DBNAME.TABLENAME;


If this table is locked currently then , then the above statement would fail as Nowait will not wait for locks to be released on that  table .


Wednesday, May 18, 2011

What are advantages of compression on tables?

- They take less physical space then uncompressed columns hence reducing space cost
- They improve  system performance as less data will be retrieved per row fetched , more data is fetched per data block thus increasing data loading speed
- They reduce overall I/O