This is due to the way the different relational database systems, and maybe
even the SQL standards, have implemented the conversion hierarchy when
implicit data conversion is needed. In your case, you have columns declared
in *varchar* against which you’re receiving bind variable values declared
as *N**varchar. *In this case, the implicit conversion is done *from the
column to the bind variable value* which represents the worst scenario.
You can override this implicit conversion by explicitly converting the bind
variable values to varchar as shown in the following example:
create table t1(n1 number, v1 varchar2(10), d1 date);
insert into t1
select
rownum n1
, rownum v1
, sysdate + dbms_random.value(0,365)
from
dual
connect by level <= 1e3;
create index t1_n1_idx on t1(n1);
create index t1_v1_idx on t1(v1);
create index t1_d1_idx on t1(d1);
exec dbms_stats.gather_table_stats (user, 't1');
SQL> var v2 nvarchar2(10)
SQL> exec :v2 :='1'
PL/SQL procedure successfully completed.
SQL> select count(1) from t1 where v1 =:v2;
COUNT(1)
----------
1
---------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
---------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 4 |
|* 2 | TABLE ACCESS FULL| T1 | 10 | 40 |
---------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYS_OP_C2C("V1")=:V2)
SQL> select count(1) from t1 where v1 = *to_char(:v2);*
COUNT(1)
----------
1
-------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
-------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | SORT AGGREGATE | | 1 | 4 |
|* 2 | INDEX RANGE SCAN| T1_V1_IDX | 1 | 4 |
-------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V1"=SYS_OP_C2C(:V2))
In the following article, you can read more details about the data type
implicit conversion hierarchy.
https://www.red-gate.com/simple-talk/databases/oracle-databases/oracle-data-type-implicit-conversion-hierarchy/
The bottom line is that there is no complaint when the implicit conversion
is made from the data type of the bind variable to that of the column
(because it will be done once and it doesn’t preclude the index usage when
this is adequate). But the performance problems appear when the conversion
is done from the data type of the column to that of the bind variable.
Regards
Mohamed
Le lun. 13 déc. 2021 à 18:16, Vinay Kumar Narisetty <
narisetty.vinay@xxxxxxxxx> a écrit :
Hi All,
We have several custom databases defined with VARCHAR2 text fields. We
interface with an ERP system that is changing to NVARCHAR2 text fields.
There are many applications that retrieve data from the ERP system and join
to a custom database to get the complete picture.
If we do nothing, all our queries will have performance problems. You
cannot join a varchar2 with nvarchar2 without wrapping it in a function
such as
Where to_nchar(t1.orderno) = t2.orderno
Using the above where clause, we would then need to create a function
based index on t1.orderno.
There are so many applications and SQL to change it does not seem feasible
to rewrite all the code. Or should we consider converting all our custom
applications to use NVARCHAR2 fields? In that way we have the same
character set in all databases. Is there a risk doing mass conversions from
VARCHAR2 to NVARCHAR2?
We are hoping to get some direction on the best way to tackle this and
avoid any conversion problems.
Thanks,
Vinay Narisetty
