Re: Doing a query with string vs bind variables.

On Sep 17, 3:42 pm, Luch <DarthL..._at_gmail.com> wrote:
> We have a PowerBuilder application where we started noticing the
> performance of a query is worse if you do it with bind variables then
> if you do the same query as a string. We made a small example and sent
> to Sybase (makers of PB). We noticed that the bind variables query
> will not use the same index as the string query, even though the two
> queries are exactly the same.
>
> Example
>
> String query:
> SELECT DISTINCT "ORDER_HEADER"."ORDER_TYPE" ,
> "ORDER_HEADER"."ORDER_NUMBER" ,
> "RELATION_MASTER"."NAME" ,
> "ORDER_HEADER"."RELATION_CODE" ,
> "ORDER_HEADER"."WO" ,
> "ORDER_HEADER"."SHIPPED_FROM_LOCATION" ,
> "ORDER_HEADER"."AUTHORIZATION" ,
> "ORDER_HEADER"."ACCESS_CONTROL" ,
> "ORDER_HEADER"."RELEASE_FOR_AUTHORIZATION" ,
> "ORDER_HEADER"."TOTAL_COST" ,
> "ORDER_HEADER"."CURRENCY" , "ORDER_HEADER"."PRIORITY"
> FROM "ORDER_HEADER" , "RELATION_MASTER" WHERE
> ( "ORDER_HEADER"."RELATION_CODE" =
> "RELATION_MASTER"."RELATION_CODE" (+)) and
> ( ( "ORDER_HEADER"."STATUS" = 'OPEN' ) and
> ("ORDER_HEADER"."ACCESS_CONTROL" = 'ADM' or
> ( "ORDER_HEADER"."ACCESS_CONTROL" = 'ALL') ) and
> ( "ORDER_HEADER"."ORDER_TYPE" = 'RO' ) )
>
> Bind variable query:
> SELECT DISTINCT "ORDER_HEADER"."ORDER_TYPE" ,
> "ORDER_HEADER"."ORDER_NUMBER" ,
> "RELATION_MASTER"."NAME" ,
> "ORDER_HEADER"."RELATION_CODE" ,
> "ORDER_HEADER"."WO" ,
> "ORDER_HEADER"."SHIPPED_FROM_LOCATION" ,
> "ORDER_HEADER"."AUTHORIZATION" ,
> "ORDER_HEADER"."ACCESS_CONTROL" ,
> "ORDER_HEADER"."RELEASE_FOR_AUTHORIZATION" ,
> "ORDER_HEADER"."TOTAL_COST" ,
> "ORDER_HEADER"."CURRENCY" , "ORDER_HEADER"."PRIORITY"
> FROM "ORDER_HEADER" , "RELATION_MASTER" WHERE
> ( "ORDER_HEADER"."RELATION_CODE" =
> "RELATION_MASTER"."RELATION_CODE" (+)) and
> ( ( "ORDER_HEADER"."STATUS" = 'OPEN' ) and
> ("ORDER_HEADER"."ACCESS_CONTROL" = :s_user or
> ( "ORDER_HEADER"."ACCESS_CONTROL" = 'ALL') ) and
> ( "ORDER_HEADER"."ORDER_TYPE" = :s_order_type ) )
>
> In the Bind-variable query, we pass the same parameters as we are hard-
> coding in the equivalent string version.
>
> Why would the bind variable query use a different index, and why it
> would it be a "worse" index then the one the string-query uses?
>
> The puzzling part is that if I take the Bind-variable query and run it
> in Oracle SQL Developer (it will prompt me for the values when run) it
> will also perform well... It is only in running through Sybase
> PowerBuilder that the bind-variable query runs slow. Sybase stated
> they have no control over which index the database will use.
>
> Another note... the above example is only one case... this same
> situation is happening with many queries throughout our application.
>
> any thoughts?

Which version of Oracle are you using? It's possible (if I remember correctly from an explanation of a similar situation by Jonathan Lewis) that the string is evaluated as a constant and can correctly use the 'proper' index, where the bind variables are unknowns and introduce a 5% selectivity into the CBO calculations. I'm certain Jonathan can explain it better.

Post the version of Oracle (all four numbers); it's difficult to assist you without this information. And post the explain plans if you have them available.

David Fitzjarrell Received on Mon Sep 17 2007 - 16:18:21 CDT