Hello Tom,
We have configure the client this way:
SQL> select parameter, value from nls_session_parameters where parameter in ('NLS_COMP','NLS_SORT','NLS_LANGUAGE');
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE SPANISH
NLS_SORT SPANISH
NLS_COMP BINARY This is the content of the table:
SQL> select * from orden order by val1;
VAL1
--------------------------------------------------
A
B
C
1
2
3
6 filas seleccionadas.
The following query just fetch 'B' and 'C' and ignore the numbers.
SQL> select * from orden where val1>'A' order by val1;
VAL1
--------------------------------------------------
B
C
With the parameter to LINGUISTIC we fetch also the numbers.
SQL> alter session set nls_comp='LINGUISTIC';
Sesión modificada.
SQL> select * from orden where val1>'A' order by val1;
VAL1
--------------------------------------------------
B
C
1
2
3
Oracle told us that changing the parameter NLS_COMP to LINGUISTIC will change the execution plans and can ignore normal indexes. Would there be any other option to get the result of the last query without changing the parameter NLS_COMP?
Thank you,
Tomás
Yes, if you set the NLS_COMP to LINGUISTIC, you'll see some interesting things in the predicate section of a plan:
alter session set nls_language = 'SPANISH';
alter session set nls_sort = 'SPANISH';
create table t (
val1 varchar2(1)
);
insert into t values ('A');
insert into t values ('B');
insert into t values ('C');
insert into t values ('1');
insert into t values ('2');
insert into t values ('3');
commit;
alter session set nls_comp='LINGUISTIC';
select * from t
where val1 > 'A'
order by val1;
VAL1
B
C
1
2
3
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE'));
PLAN_TABLE_OUTPUT
EXPLAINED SQL STATEMENT:
------------------------
select * from t where val1 > 'A' order by val1
Plan hash value: 948745535
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
|* 2 | TABLE ACCESS FULL| T |
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NLSSORT("VAL1",'nls_sort=''SPANISH''')>HEXTORAW('14000100'
))Notice that NLSSORT stuff?
It means the database is applying a function. So it won't use a regular index on val1.
Luckily, you're on 12.2. So, provided you've set max_string_size = extended, you can specify collations at the column level!
This enables the database to do this conversion at the column level:
alter session set nls_comp='BINARY';
select * from t
where val1 > 'A'
order by val1;
VAL1
B
C
alter table t modify ( val1 collate spanish );
select * from t
where val1 > 'A'
order by val1;
VAL1
B
C
1
2
3
But there's still a question you need to answer:
What exactly do you mean by "greater than A"?
Should this include lowercase values? What about lowercase a?
insert into t values ('a');
insert into t values ('b');
select * from t
where val1 > 'A'
order by val1;
VAL1
a
B
b
C
1
2
3 If this will be an issue for you, you could switch to a case-insensitive collation:
alter table t modify ( val1 collate spanish_ci );
select * from t
where val1 > 'A'
order by val1;
VAL1
B
b
C
1
2
3
Or - if you're really brave - use regular expressions:
alter table t modify ( val1 collate spanish );
select * from t
where regexp_like ( val1, '^[B-Z0-9]' )
order by val1;
VAL1
B
b
C
1
2
3