| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL Vs Static SQL using Pro*C..
A copy of this was sent to michael twaddell TWA <175501_at_dallas.dseg.ti.com> (if that email address didn't require changing) On Fri, 22 May 1998 06:50:00 -0500, you wrote:
>Thomas Kyte wrote:
>>
>> So dynamic sql does not decrease performance but not using bind variables will
>> (in general). There are cases when you don't want to use bind variables (data
>> warehouses that rely heavily on the CBO come to mind) but for most applications
>> bind variables are the way to go...
>
>Thomas,
>Could you expand on this for me? I thought that we should always use
>bind
>variables. What is it about Data Warehouse and the Cost Based Optimizer
>that
>make this different? Admittedly, I don't work much with Data Warehouse
>applications at the present, but this intrigued me. I've also looked in
>the
>Administrator's and Application Developer's guides, but cannot find a
>reference
>to this. I really appreciate your input to this newsgroup and would be
>grateful
>for any information you can provide. Thanks in advance.
Ok, lets say you have a table. Its really big. Col_A has only 3 values in it, 1, 2, and 3. 90% of the data has a value of 3. 7% of the data has a value of 2. 3% of the data has a value of 1. You have a bitmapped index on it. You issue:
select * from BigTable where col_A = 1;
That should use the index to access the table (assuming you've analyzed and given the optimizer the opportunity to discover the massively skewed distribution of values in col_A). But later you issue:
select * from BigTable where col_A = 3;
that should full scan since most of the table will be returned anyway. That will happen in the CBO using the above 'hard wired' queries. Use the CBO with:
select * from Bigtable where col_a = :x;
and you'll find that it always full scans. The reason is that it does not know at COMPILE/PARSE time that :x is discriminating enough (it can't know that, :x may or may not be discriminating) to get only a small percentage of the table. Therefore it punts and full scans. Good for some, bad for others.
Also consider a query like:
select * from BigTable where col_a like :x;
The optimizer will assume :x could be '%' in the worst case -- a full scan will happen. OTOH
select * from BigTable where col_a like 'ABC%'
will typically (depending on the values in col_a again) index range scan and then table access by rowid.
So in general for all OLTP type applications -- bind variables are the right answer. This is because in general, the parse time for these queries is LARGER then the actual execute time. For reporting -- where queries take a long time to run, not for short lookup queries -- sometimes it makes sense to not use bind variables when using the CBO to give it more information to go on....
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 22 1998 - 00:00:00 CDT
 |
 |