RE: compiler?

[Shankar Unni]

> when the PreparedStatement is executed, the DBMS can just run 
> the PreparedStatement 's SQL statement without having to 
> compile it first". what does it mean? sqlplus of oracle is 
> interperter?

SQLPLUS is not even an interpreter - it merely collects the statement
and sends it on to Oracle to interpret. Just like the "mysql" program
does to mysqld.

Now to what it means to "compile" a statement. Typically, a database
will take a statement and prepare an "execution plan" for it. This
involves parsing the statement, analyzing the indexes to use, etc.. This
step is usually independent of the actual values used in the WHERE
clause, and depends only on the expressions themselves (i.e. what
columns you use, how you join the tables, what expressions you invoke on
the columns, etc.)

This is what makes PreparedStatements attractive: if you plan to execute
the same statement over and over again (e.g. "get the article named ?",
or "insert an article with values ?, ?, ?", etc.), then you can, in
theory, prepare the statement once, and then execute the prepared
statement repeatedly with different "bind values" for the ? parameters.
At best, this will save the database the bother of reparsing and
re-analyzing the statement, and it can just directly use the previously
computed execution plan and just plug in the values to execute it with.

Now in practice, things are not always so smooth. Depending on the
database, the prepared statement may be valid only for the connection it
was prepared on (which means you would have to hold the connection for
the duration of the use). On the other hand, for some databases (Oracle,
etc. - don't know how MySQL does this), the execution plan is cached in
a statement cache on the server side, independent of the connection, so
any connection can re-use the previously prepared execution plan, and so
PreparedStatements make excellent sense for Oracle. 

Personally, I always use PreparedStatements wherever I can, because they
are more often efficient than not.
--
Shankar.