oracle中国家语言支持的索引问题

ORACLE数据库中可以使用国家语言支持，但是好像索引效率有点问题，在估算选择基数的时候会估算错误，甚至有可能会产生错误的执行计划。

create table t1 (v1 nvarchar2(20)) ;
create index i1 on t1(v1);
insert into t1 select lpad(rownum,20,0) from all_objects;
create index i1 on t1()

create table t2 (v1 varchar2(20))
create index i2 on t1(v1);
insert into t2 select lpad(rownum,20,0) from all_objects;

dbms_stats.gather_table_Stats(user,'T1',cascade=>true,method_opt=>'for all columns size 254');

dbms_stats.gather_table_Stats(user,'T2',cascade=>true,method_opt=>'for all columns size 254');

然后：

set autot traceonly explain

select * from t1 where v1='00000000000000000001'；

这时得到的成本是3，基数是100

select * from t2 where v1='00000000000000000001'；

这时得到的成本是1，基数是1

运行select * from user_tab_histograms where table_name='T1';
select * from user_tab_histograms where table_name='T2';

比较endpoint_actual_value,可以发现对t1和t2的不同，t1只对前16个字符进行了记录。因此在索引扫描的时候会得出错误的选择率。（有99个值满足条件，但是索引有两层，因此基数是100）

测试发现ORACLE只有在使用nchar,nvarchar2这些类型的列时才会出现这种问题。即使把数据库字符集也改成UTF8编码，数据库在处理char,varchar2这些类型的列时，依然不会出错。看来ORACLE是根据列类型来进行判断，而跟数据库字符集没关系。

在过滤条件里，可以看到v1=U'00000000000000000001',可能是ORACLE对这个字符串做了处理，将其转化为UTF8编码。（我没用启动10053事件，不确定ORACLE具体做了什么）

如果所建的索引是唯一索引：

select * from t1 where v1='00000000000000000001'；

这时得到的成本是2，基数是1

select * from t2 where v1='00000000000000000001'；

这时得到的成本是1，基数是1

这时基数是正确的，但是成本还是高（成本高的原因可能在于字符编码的转化）。

再看以下情况：

create table t3 (v1 varchar2(40));
create index i3 on t1(v1);
insert into t3 select lpad(rownum,40,0) from all_objects;

dbms_stats.gather_table_Stats(user,'T3',cascade=>true,method_opt=>'for all columns size 254');

这时得到的成本是64，基数是38744(整个表里的数据)。如果大概估算的话，可能ORACLE只会对前32b的数据进行统计。

select * from t2 where v1='0000000000...0000000001'；

如果建唯一索引的话，就又可以得到成本是2，基数是1。这也是唯一索引的好处，在等值查询时可以唯一扫描。不过我们不能指望唯一索引的这种特性，运行下列查询：


select * from t2 where v1 between '0000000000...0000000001' and '0000000000...0000000010'；

这时得到的成本是64，基数是38744。

在建表的时候选择合适的数据类型真的很重要！