I am new to this Forum and Preparing for TE0-141 Exam. I have came across few questions on the internet and their answers are bit confusing. I request your help on the below questions and the explanation on the Correct Answer for the same. As per my understanding I kept my answers in Bold.
Q1) For a tablelevel lock, which two lock type combinations are compatible? (Choose two.)
A. Read and Read B. Read and Share C. Read and Write D. Access and Write
Q2) Why would the Optimizer choose a Full Table Scan (FTS) rather than Index access?
A. FTS is estimated to spool more data than the index.
B. FTS is estimated to use fewer resources than the index. C. FTS is estimated to have less user input than the index.
D. FTS is estimated to redistribute more data across all AMPs than the index.
Any help would be really appreciable.
Hi, For both the questions i think your answers are correct
Q1) There is no such lock as Share and you cant have read and write lock at the same time(But if you want to read and write(Not locks) at the same time then you can have access lock and write lock).
Q2)Optimizer always goes for the efficient option, so i think option B is correct .
regarding the first question ur answer is correct as the teradata lock copatiblity goes like below:
Exclusive lock- no write, no read, no access
Write lock- only Access lock acceptable
Read lock- only Read and Access acceptable
Access lock - Read,Write and Acess lock are acceptable
Regarding the 2nd question:
If we have UPI,NUPI or USI then optimizer will always prefer using Index rather than full table scan because it use 1 amp,1amp and 2 amp respectivly. But In the case of weakly selective NUSI sometime when the data are poorly distributed the optimizer prefer full table scan rather than using index.