How can you determine if an index needs to be dr

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

How can you determine if an index needs to be dropped and rebuilt?

Level: Intermediate
Expected answer: Run the ANALYZE INDEX command on the index to validate its structure and then calculate the ratio of LF_BLK_LEN/LF_BLK_LEN+BR_BLK_LEN and if it isn?t near 1.0 (i.e. greater than 0.7 or so) then the index should be rebuilt. Or if the ratio
BR_BLK_LEN/ LF_BLK_LEN+BR_BLK_LEN is nearing 0.3.

(Continued on next question...)

Other Job Interview Questions

• What is the use of hidden column?
• How can you gather statistics on a table?
• How can we implement roles?
• What use of command line parameter cmd file?
• What are the Coordination Properties in a Master-Detail relationship?
• Lot of users are accessing select sysdate from dual and they getting some millisecond differences. If we execute SELECT SYSDATE FROM EMP; what error will we get. Why?
• With which function of summary item is the compute at options required?
• What is a SQL * NET?
• Can objects of the same Schema reside in different tablespaces?
• What are disadvantages of having raw devices?
• More...