Question

3
Replies
207
Views
Mahendra_Kumar Member since 2011 12 posts
Tata Consultancy Services
Posted: 2 years ago
Last activity: 2 years 8 months ago
Closed

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***

Reporting Data Integration
Moderation Team has archived post
Share this page LinkedIn