Skip to content

Avoid translated columns in WHERE clause

Avoid using translated values in where conditions, instead use the database values.

It is always better to use tables whenever possible for querying the data. If there is a need to use views for querying data, use the database columns instead of the client values in the SQL query where conditions.

When a client value is used for comparison a PLSQL method call will be executed each time to get the translated client value based on the database column. To achieve this oracle needs to switch between the SQL engine to process the SQL statement and PLSQL engine to process the PLSQL method call and then back to SQL engine. This context switching will lead to high cost in performance. Use of database values in where conditions will avoid such performance overhead.

When SQL is based on views always check the column used for where conditions for database columns to use for where conditions.

Two main scenarios include:

  • When using state based attributes in where conditions. (e.g rowstate/rowtype)

    Solution : Use objstate/ objtype column which is the database column and avoid using state which is the translated value of the rowstate database column.

  • When using enumeration based attributes in where conditions.

    Solution : Use columnXX_db instead of the translated columnXX for where statemnets of the query.

    View can have database column as well as client translated column values.

SELECT company company,
       invoice_id invoice_id,
       identity identity,
       Party_Type_API.Decode(party_type) party_type,
       party_type party_type_db,
       series_id series_id,
       invoice_no invoice_no,
       rowstate objstate,
       Invoice_API.Finite_State_Events__(rowstate) objevents,
       Invoice_API.Finite_State_Decode__(rowstate) state,
       rowkey objkey,
       ROWTYPE objtype,
       TO_CHAR(rowversion) objversion,
       ROWID objid
FROM   invoice_tab;

e.g When a query is based on the view use database values ( e.g party_type_db, objstate) as given below in the where condition.

SELECT invoice_address_id
FROM   invoice_utility_inv_head_pub
WHERE  company = company_
AND    identity = identity_
AND    party_type_db = party_type_db_
AND    series_id = series_id_
AND    invoice_no = invoice_no_
AND    objstate = 'PostedAuth';