Re: Using Host Variables in ODBC -Oracle Enviornment

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote

> beg to differ -- it could be that 90% of the EXECUTION of that query is spent
> parsing.

<snipped>

Agreed Thomas. Trust you to beat me over the head when I do not state my opinions clear enough. ;-)

My Oracle perspective is a data warehouse one. It is strange. I got into Oracle on MPPs with OPS. I have never done "real OLTP" on Oracle. Batch processing systems, data warehouses and decision support mostly. Add to that the large amounts of caffiene I digest daily, my perspective is probably a bit skewed.

A few seconds overheard in a SELECT statement in negligible when dealing with a SELECT on large volumes of data. So in decision support type databases, you can get away with not using bind variables. Which is what I was trying to say. But then I also said, that bind variables is a Good Idea (tm).

When dealing with OLTP where execution time is measured in ms and not minutes and hours, then you are very correct when saying that there are a huge difference and that bind variables are crucial.

BTW, one advantage (if you can call it that) of not using bind variables, is that you get to see exactly what the user is trying to do with these queries from hell when monitoring the user processes. The only decision support commercial software I have seen that does not use bind variables (going back a few years now) was Cognus' Impromptu package (it uses OCI). But then Impromptu was a different kettle of fish too.. with is optional DSS SQL functions that requires joins to be done locally.. resulting in the users trying to pull down the entire VLT fact table row by row to join and aggregate on their PCs... and then have them argue with you that it is faster that way. :-)

--
Billy