How does oracle respond to a Query ?

How the Oracle responds to the query "SELECT * FROM EMP" internally ?

more ▼

asked Feb 05, 2010 at 07:39 AM in Default

OracleApprender gravatar image

771 70 73 75

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Feb 05, 2010 at 08:06 AM

Andrew Mobbs gravatar image

Andrew Mobbs
1.5k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 05, 2010 at 07:39 AM

Seen: 895 times

Last Updated: Feb 05, 2010 at 07:39 AM