I am trying to do Linguistic sorting (specific to language)in Oracle(enterprise edition 8.1.6.0.0)on NT 4.0 service pack 4.0.
But i am getting results based on binary sort.List of commands tried are as under:
Database character set – UTF-8
National character set – UTF-8
create table prototest (
empid number(38) primary key ,
empname varchar2(40) not null,
joining date not null,
salary number(20,2) not null ,
age integer not null ,
remarks varchar2(200) not null
);
CREATE INDEX nls_index ON prototest (NLSSORT(empname, ‘NLS_SORT = French’));
insert into prototest
(
EMPID,
EMPNAME,
JOINING,
SALARY,
AGE,
REMARKS
)
values
(
1,
‘peach’,
to_date(‘2000-09-01′,’YYYY-MM-DD’),
3000.00,
30,
‘French’
)
;
EMPNAME field has following values for other rows:
‘p?ch?’,’p?che’,’sin’
Here the binary order is
peach
p?ch?
p?che
sin
whereas the French order is
peach
p?che
p?ch?
sin
1.SELECT * FROM prototest WHERE
NLSSORT(empname,’NLS_SORT=French’) >
NLSSORT(‘p?ch?’,’NLS_SORT=French’);
2.SELECT /*+index(prototest.nls_index)*/
* FROM prototest WHERE
NLSSORT(empname,’NLS_SORT=French’) >
NLSSORT(‘p?ch?’,’NLS_SORT=French’)
No change, even when using the hint.
3.SQL> select * from prototest order by empname
gives the same result.
4.The steps were done again setting NLS_LANG parameter in the OS environment as French_France.WE8ISO8859P1 and the database character set/national character set to WE8ISO8859P1.
But no luck, yet again
5.Query Optimizer commands…
alter session set sql_trace = true;
alter session set optimizer_mode = All_Rows;
ANALYZE TABLE prototest ESTIMATE STATISTICS;
ANALYZE TABLE PROTOTEST VALIDATE STRUCTURE CASCADE;
SELECT * FROM PROTOTEST WHERE NLSSORT(EMPNAME, ‘NLS_SORT = FRENCH’) > NLSSORT(‘p?ch?’,’NLS_SORT = FRENCH’);
Please suggest a way out.
Thanks and Regards,
Manish Dhall