How the Oracle responds to the query "SELECT * FROM EMP" internally ?
asked
How does oracle respond to a Query ?
That is my favourite sort of interview question, since it is so simple yet the answer can show exactly how much the interviewee understands about Oracle internals.
It can be answered to almost any level of detail from explaining a simple "Parse, Execute, Fetch" model, through explanations of query optimization and where rollback segments might be used, down to describing exactly what latches will be needed on which blocks and why.
Ideally the interviewer will take whatever explanation is offered, then push a little in each area to really stretch the candidate. There's always some lower level of explanation that can be asked for. They might then ask what would be different if EMP were partitioned, or if the database is a RAC system, or EMP is actually a view not a table.
I'm afraid it's the sort of question that can't really be satisfactorily answered on a Q&A forum, a complete answer would take a book.
However, at a fairly high level, as usual the Concepts manual gives a good overview of SQL processing.
