Q1. Explain Teradata
Architecture ?
Ans1. Teradata Architecture majorly consists of Parsing Engine (PE) , BYNET, Access Module Processors (AMP) and VDISK. MORE.
Ans1. Teradata Architecture majorly consists of Parsing Engine (PE) , BYNET, Access Module Processors (AMP) and VDISK. MORE.
Q2. What is Primary Index in
Teradata and how it is different from Primary Key ?
Ans2. Primary Index is used for determining the access path to retrieve or store data into any table in Teradata. MORE
Ans2. Primary Index is used for determining the access path to retrieve or store data into any table in Teradata. MORE
Q3. How Teradata distributes
rows in AMP’s ?
Ans3. Teradata uses ROWHASH on PRIMARY INDEX column to determine which AMP should store the data. MORE
Ans3. Teradata uses ROWHASH on PRIMARY INDEX column to determine which AMP should store the data. MORE
Q4. How to determine the size
of table in Teradata?
Ans4. In Teradata, we can use DBC.TABLESIZE view to check the storage space used by any Table. MORE
Ans4. In Teradata, we can use DBC.TABLESIZE view to check the storage space used by any Table. MORE
Q5. How to check SQL history in
Teradata?
Ans5. In Teradata we can use DBC.QRYLOG to check SQL history. MORE
Ans5. In Teradata we can use DBC.QRYLOG to check SQL history. MORE
Q6. How to find the number of
AMPs in any system?
Ans6. SELECT HASHAMP()+1 ; [ We have to do +1 because the HASHAMP number starts with 0]
Ans6. SELECT HASHAMP()+1 ; [ We have to do +1 because the HASHAMP number starts with 0]
Q7. Which one is better IN or
BETWEEN?
Ans7. If we have to search for range of values, it is always advisable to use BETWEEN rather than list of values in IN clause. BETWEEN tells the optimizer that it is range of values we are going to handle rather than individual values. Hence, BETWEEN is much faster than IN.
Ans7. If we have to search for range of values, it is always advisable to use BETWEEN rather than list of values in IN clause. BETWEEN tells the optimizer that it is range of values we are going to handle rather than individual values. Hence, BETWEEN is much faster than IN.
Q8. What are disadvantages of
Secondary Index?
Ans8. Secondary Indexes need more I/O. Secondary Index slows down the process because of Sub-table maintainence. Secondary Index requires more Physical Storage.
Ans8. Secondary Indexes need more I/O. Secondary Index slows down the process because of Sub-table maintainence. Secondary Index requires more Physical Storage.
Q9. What is default session
transaction mode in Teradata?
Ans9. Teradata has two session transaction mode : BTET(Default) and ANSI. To change session mode we use following command before establishing session: .SET SESSION TRANSACTION (ANSI/BTET)
Ans9. Teradata has two session transaction mode : BTET(Default) and ANSI. To change session mode we use following command before establishing session: .SET SESSION TRANSACTION (ANSI/BTET)
Q10. What is MSR (Multi
Statement Request) in Teradata?
Ans10. Sometimes we may have to insert data into table by treating several INSERT blocks as one transaction. In that case, we use MSR. It can be implemented by putting a semi colon “;” in front of next line rather than putting it on end of existing line. eg:
INSERT INTO EMPLOYEES(NAME,AGE) VALUES(‘WILL’,26)
;INSERT INTO EMPLOYEES(NAME,AGE) VALUES(‘SMITH’,28);
Ans10. Sometimes we may have to insert data into table by treating several INSERT blocks as one transaction. In that case, we use MSR. It can be implemented by putting a semi colon “;” in front of next line rather than putting it on end of existing line. eg:
INSERT INTO EMPLOYEES(NAME,AGE) VALUES(‘WILL’,26)
;INSERT INTO EMPLOYEES(NAME,AGE) VALUES(‘SMITH’,28);
Q11. Which is better GROUP BY
or DISTINCT to remove duplicates present in a table?
Ans11. It depends on the tablesize. If the records are more than half a million then GROUP BY is much better than DISTINCT. However if the records are very less than DISTINCT performance is better than GROUP BY.
Ans11. It depends on the tablesize. If the records are more than half a million then GROUP BY is much better than DISTINCT. However if the records are very less than DISTINCT performance is better than GROUP BY.
Q12.How can you apply access
lock on table rather than read lock?
Ans12. We can override default read lock on any table by explicitly mentioning the ACCESS LOCK before using the table. Syntax is:
LOCK TABLE EMPLOYEES FOR ACCESS
SELECT * FROM EMPLOYEES;
Ans12. We can override default read lock on any table by explicitly mentioning the ACCESS LOCK before using the table. Syntax is:
LOCK TABLE EMPLOYEES FOR ACCESS
SELECT * FROM EMPLOYEES;
Q13. Different method of
releasing the FASTLOAD lock on the table ?
Ans13. Execute the FASTLOAD script by removing the DML block , i.e only BEGIN LOADING and END LOADING in the FASTLOAD script should be kept. Another method can be to drop the table and create it again.
Ans13. Execute the FASTLOAD script by removing the DML block , i.e only BEGIN LOADING and END LOADING in the FASTLOAD script should be kept. Another method can be to drop the table and create it again.
Q14. What does SLEEP command do
?
Ans14. SLEEP command is used to specify the waiting time Teradata will take before re-trying for establishing the connection or session.
Ans14. SLEEP command is used to specify the waiting time Teradata will take before re-trying for establishing the connection or session.
Q15. What does Tenacity command
do ?
Ans15. Tencacity command is used to specify the total time Teradata will continue trying to establish the connection. After which Teradata won’t make any more attempts for establishing the connection.
Ans15. Tencacity command is used to specify the total time Teradata will continue trying to establish the connection. After which Teradata won’t make any more attempts for establishing the connection.
Q16. How to skip 5 rows while
reading data from file using Teradata Utilities?
Ans16. In BTEQ, we can use SKIP command and other utilities we can use RECORD command to specify from which record, Teradata should start reading data from file.
Ans16. In BTEQ, we can use SKIP command and other utilities we can use RECORD command to specify from which record, Teradata should start reading data from file.
Q17. How many Join Strategies
are available in Teradata?
Ans17. There are five Join Strategies available in Teradata : Merge, Nested, Hash, Product, Exclusion.
Ans17. There are five Join Strategies available in Teradata : Merge, Nested, Hash, Product, Exclusion.
Q18. Different levels of
COnfidence Optimizer may have while creating EXPLAIN Plan?
Ans18. HIGH CONFIDENCE, LOW CONFIDENCE, NO CONFIDENCE.
Ans18. HIGH CONFIDENCE, LOW CONFIDENCE, NO CONFIDENCE.
Q19. Can
we load duplicate data using FASTLOAD?
Ans19. No, we cannot load duplicate data via FASTLOAD. FASTLOAD property is to discard the duplicate records. When we restart the FASTLOAD, it send the data again from last checkpoint. In that case, it may send some data again. So FASTLOAD identify such records as duplicate and hence discard it. Q20. Can we have several Primary Index on a table?
Ans20. No we cannot have multiple Primary Index on a table. However we can create 32 Secondary Indexes on a table.
Ans19. No, we cannot load duplicate data via FASTLOAD. FASTLOAD property is to discard the duplicate records. When we restart the FASTLOAD, it send the data again from last checkpoint. In that case, it may send some data again. So FASTLOAD identify such records as duplicate and hence discard it. Q20. Can we have several Primary Index on a table?
Ans20. No we cannot have multiple Primary Index on a table. However we can create 32 Secondary Indexes on a table.
21. How to save a string with
single quote (‘) as part of the value?
Ans21. INSERT INTO CUSTOMER(COMMENTS) VALUES(‘I”m Good’);
Ans21. INSERT INTO CUSTOMER(COMMENTS) VALUES(‘I”m Good’);
Q22. How to select 5th row from
a table?
Ans22. SELECT * FROM TABLENAME QUALIFY ROW_NUMBER() OVER (ORDER BY COLUMN1) = 5;
Ans22. SELECT * FROM TABLENAME QUALIFY ROW_NUMBER() OVER (ORDER BY COLUMN1) = 5;
Q23. Where is password stored
in Teradata?
Ans23. User can view the password in DBC.DBASE view however it will be in encrypted format.
Ans23. User can view the password in DBC.DBASE view however it will be in encrypted format.
Q24. How to find Teradata
Release and Version details?
Ans24. SELECT * FROM DBC.DBCINFO;
Ans24. SELECT * FROM DBC.DBCINFO;
Q25. How many phases are in
MultiLoad?
Ans25. MultiLoad has 5 phases : Preliminary Phase, DML Transaction Phase, Acquisition Phase, Application Phase and Clean-Up phase.
Ans25. MultiLoad has 5 phases : Preliminary Phase, DML Transaction Phase, Acquisition Phase, Application Phase and Clean-Up phase.
No comments:
Post a Comment