Monday, April 15, 2013

Can you compress a column which is already present in table using ALTER?

Can you compress a column  which is already present in table using ALTER?

No,   We cannot use ALTER command to compress the existing columns in the table.
A new table structure has to be created which includes the Compression values and data should be inserted into Compress column table.

Please note - ALTER can be used only to add new columns with compression values to table.

Sunday, November 4, 2012

Few tricky questions on Explain plan

These are some tricky questions asked about explain to see if an candidate has really worked on query tuning and how much he would know about optimizer & explain 
1.            What does ” Pseudo Table” Locks mean?
It is a false lock which is applied on the table to prevent  two users from getting conflicting locks with all-AMP requests.
PE will determine an particular AMP to manage all AMP LOCK requests for given table and Put Pseudo lock on the table.

To put in Simple terms , its like an University with 10 gates and at a given time you can enter the university through one gate ( or main gate) for security check.

 2.            What is residual condition?
Residual condition means a filter is applied on particular table to limit the number of rows fetched into Spool.
Say for example
Sel * from EMP.Employee where emp_sal > 10000;

Here , residual condition will act upon to fetch only employees with salary greater than 10000

3.            Spool “ Last use”
The particular spool file used in the step will be used for last time and the spool space will be released since it is no longer used in further steps of the query execution.

Tuesday, February 14, 2012

Why is BTET transaction processing overhead in Teradata?

- BTET makes all the queries running between BT and ET as single transaction . If any of query fails then the entire set of queries will not be committed.

- BTET also has an overhead with locking , since it holds locks on tables till the ET is occured or all the queries have executed successfully

- DDL statements cannot be used  everywhere in BTET processing , but these statements can be given towards the end of BTET transaction.

- Using large number of BTET caused transient Journal to grow and has tendancy for System Restarts

Monday, December 19, 2011

What are the options that are not available for global temporary tables ?

GTT  definitions are created in Data dictionary.  These table provide separate instance to each user who refers to them .

The following options are not available for global temporary tables:
- Any kind of constraints like check/referential cannot be applied on table
- Identity columns since data in GTT are materialized only for session
- Permanent Journaling cannot be done as data in tables are instances only to user in that session
- PPI cannot be applied as data does not get stored in PERM , only TEMP space is utilized here.

What are the options not available for volatile tables in teradata ?

The following options are not available for volatile tables because table definition is not stored in data dictionary
- Default values for columns 
- Title clause for columns
- Named Indexes for table
- Compression on columns/table level since table data are spooled
- Stats cannot be collected since data is materialized only for session 
- Identity columns as these again would need entry in IDcol tables
- PPI cannot be done on tables
- Any kind of constraints like check/referential cannot be applied on table