Slow performance at Oracle Server when Federated SQL queries are executed with remote sql having RTRIM() or RPAD()

Symptom

Oracle and DB2 have different semantics in varchar string comparison. Oracle will use non-blank padding comparison semantics while DB2 will use blank padding comparison semantics. Below is an example:
Blank padding comparison example:
‘abc’ = ‘abc ‘ => true
Non-blank padding comparison example:
‘abc’ = ‘abc ‘ => false

Federation Server has VARCHAR_NO_TRAILING_BLANKS as both SERVER option and COLUMN option to specify whether varchar data in remote data source was padded with trailing blanks '0x20'.

In default, DB2 use '0x20' as padding character while Oracle use '0x00' as padding character. Since it can not ensure that varchar data does not have blanks padded, for Oracle wrapper, server option VARCHAR_NO_TRAILING_BLANKS is set to 'N' by default. However, user can set server option to 'Y' if they can ensure that all varchar data in Oracle is not padded with blanks, or set column option to 'Y' if only certain columns can be ensured to have no trailing blanks.

As described above, Oracle and DB2 have different semantics in varchar string comparison. To get equal result with DB2 native operations, Federation Compiler may add RTRIM() or RPAD() in remote statement when VARCHAR_NO_TRAILING_BLANKS was set to 'N'. Operations for varchar string comparison and IN predicates may be affected.

Sometimes user could not get high performance while their VARCHAR_NO_TRAILING_BLANKS option was set to 'N' for the reason explained below.

Below is a sample to illustrate the problem. When Oracle statement includes RTRIM(), Oracle will use table scan instead of index scan, and of course lower the performance.

Testing scenario:
We have two tables t1_s and t2_s with below structure:
SQL> desc t1_s;
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NUMBER(10)
C2 CHAR(10)
C3 VARCHAR2(10)

SQL> desc t2_s;
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NUMBER(10)
C2 CHAR(10)
C3 VARCHAR2(10)

And with below 4 sqls, we could find that RTRIM() will cause table scan instead of index scan.

SQL0
SQL> delete from plan_table; **Delete all rows from plan table before executing explain**

SQL> explain plan for select * from t1_s, t2_s where t2_s.C3 in ('Year', 'five') AND t1_s.C1 = t2_s.C1;

Explained output:

SQL> select substr(lpad(' ', level-1)||operation||' ('||options||')',1,30) "Operation", object_name "Object" from plan_table start with id=0 connect by prior id=parent_id;

Operation Object
------------------------------ ------------------------------
SELECT STATEMENT ()
TABLE ACCESS (BY INDEX ROWID) T1_S
NESTED LOOPS ()
INLIST ITERATOR ()
TABLE ACCESS (BY INDEX ROW T2_S
INDEX (RANGE SCAN) IND_T2_C3
INDEX (RANGE SCAN) IND_T1_C1

7 rows selected.

SQL1
SQL> delete from plan_table; **Delete all rows from plan table before executing explain**

SQL> explain plan for select * from t1_s, t2_s where RTRIM(t2_s.C3) in ('Year', 'xyz') AND t1_s.C1 = t2_s.C1;

Explained output:

SQL> select substr(lpad(' ', level-1)||operation||' ('||options||')',1,30) "Operation", object_name "Object" from plan_table start with id=0 connect by prior id=parent_id;

Operation Object
------------------------------ ------------------------------
SELECT STATEMENT ()
TABLE ACCESS (BY INDEX ROWID) T1_S
NESTED LOOPS ()
TABLE ACCESS (FULL) T2_S
INDEX (RANGE SCAN) IND_T1_C1

SQL2
SQL> delete from plan_table; **Delete all rows from plan table before executing explain**

SQL> explain plan for select * from t2_s where RTRIM(t2_s.C3) in ('six','seven');

Explained output:

SQL> select substr(lpad(' ', level-1)||operation||' ('||options||')',1,30) "Operation", object_name "Object" from plan_table start with id=0 connect by prior id=parent_id;

Operation Object
------------------------------ ------------------------------
SELECT STATEMENT ()
TABLE ACCESS (FULL) T2_S

SQL3
SQL> delete from plan_table; **Delete all rows from plan table before executing explain**

SQL> explain plan for select * from t2_s where t2_s.C3 in ('four','three');

Explain output:

SQL> select substr(lpad(' ', level-1)||operation||' ('||options||')',1,30) "Operation", object_name "Object" from plan_table start with id=0 connect by prior id=parent_id;

Operation Object
------------------------------ ------------------------------
SELECT STATEMENT ()
INLIST ITERATOR ()
TABLE ACCESS (BY INDEX ROWID T2_S
INDEX (RANGE SCAN) IND_T2_C3