query compilation

It was difficult initially to accept that sql queries are compiled.

What had made strong impressions on my mind was the assumption that the queries could be treated just like commands, like shell commands. Commands that need good amount of parsing may be. The pre-compiled procedure for executing the query might be doing the job well enough. [And having 'language' in the name doesn't qualify it for compilation]

A friend told me that queries need to be compiled because of the optimization thats necessary. Where does optimization entail the need for compilation? A detailed discussion let me through.

Say if we have a query containing a join of tables T1, T2 and T3. If you go by executing it the way i thought it would (close to interpreting), it goes as (T1 join T2) join T3. But in sql querying, lot of optimization can be achieved by simply reordering the joins. So T1 join (T2 joint3) may be a better execution strategy. That can only be done if the process executing the query, passes through the entire query statement, once or more to find optimization opportunities like this, and develop a plan for executing it. And this is very close to what a compiler does. :)

The need for query optimizations can be understood from what Dr. Umesh Dayal (HP Fellow) talks about business intelligence. "... in business intelligence scenarios, you are typically faced with queries that are very large ... the largest ones you might have written may be around five pages in length (me ?? five lines!) ... but here we 're talking about queries normally running into twenty pages ...". Imagine what optimization-less querying can cause to a database.

Thanks to my sql-mx friends who made me realize that.