Question
Verizon
IN
Last activity: 7 Feb 2018 8:02 EST
Actual query being executed after bind variables are replaced with values
Hi All,
I would like to understand how the bind variables are replaced with actual values when Pega passes the query to the database.
Let me explain the scenario. We have a property (xyz) which is defined as Integer Data Type in Pega and is mapped to a column (xyz)in DB of VARCHAR2 data type. We also have a database index created on this column.
We have a report definition which when executed results in the following query: select count(pyID) from schema.tablename where xyz = ?
where ? is the value passed. Say the value being passed is 1234.
When this query is executed, will the bind variable be replaced like
(1). select count(pyID) from schema.tablename where xyz = '1234'
or
(2). select count(pyID) from schema.tablename where xyz = 1234
In (1) the database index would be used. In (2) it will not be used.
In Production, this query randomly(not often) pops up in our AWR report and our DBA says that it is because Integer value is passed to a varchar column and Oracle sometimes treats it as Integer and doesn't apply the index.
Pega Version : 7.2
Database : Oracle 12c
***Edited by Moderator Marissa to update categories***
Pegasystems Inc.
US
If the values are integers, would it not make sense to make the DB column an integer?
In Oracle there is a conversion that happens implicitly to convert the value supplied as integer to character (this is what you are saying when the 1234 does not have quotes). In this case ORACLE is not using an index. When this conversion happens you are saying ORACLE does not use an index?
Does property xyz sometimes generate '1234' and other times 1234???
If so, then is xyz defined multiple times in the class hierarchy?
Verizon
IN
Hi JimKelly,
Thanks for the response.
Cannot make the db column as integer as we expect numeric/alphanumeric values.
xyz is defined only once in the class hierarchy,
Does property xyz sometimes generate '1234' and other times 1234??? -- Property value is always without the single quotes. Either numeric or alphanumeric.
Pegasystems Inc.
US
If the property value is a text then the bind values within the preparedStatement will be replaced using the API method setString and thus should be quoted. If it was an integer than setInt will be used and it should not be quoted. If you are saying that the query sometimes shows up without the quotes then you should ask Oracle why. Oracle does do implicit conversion so it might be assuming it should treat it as a number instead of a string.