Re: NVARCHAR2 datatype conversion problems
Date: Wed, 15 Dec 2021 16:01:04 +0100
Message-ID: <CAJu8R6hg4207hBKsD5agx3ePL3tmzbVcedtqvCKCs3ew=izDkw_at_mail.gmail.com>
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.
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_at_gmail.com> 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 > > > >
-- Houri Mohamed Oracle DBA-Developer-Performance & Tuning Visit My - Blog <http://www.hourim.wordpress.com/> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>* My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri <https://twitter.com/MohamedHouri> -- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 15 2021 - 16:01:04 CET