oracle 11g nvarchar 索引不生效
create table storagefile (
storagefileid number(9) ,
filename nvarchar2(512)
)
create index ix_filename on storagefile (filename)
filename 重复性小 结构为/PRODUCT/LEVEL1/XML/LIB/2014-06-30/XML-203-312-20140630_jpg
其中/PRODUCT/LEVEL1/XML/LIB/ 重复度很高
记录数140万条
select storagefileid,filename from storagefile where filename = '/PRODUCT/LEVEL1/XML/LIB/2014-06-30/XML-203-312-20140630_jpg '
解释计划
TABLE ACCESS FULL msp storagefile 14639 293730 51696480
如查询时,filename为前缀重复度不高的字符串时,解释计划会走索引
select storagefileid,filename from storagefile where filename = '/PRODUCT2/LEVEL1/XML/LIB/2014-06-30/XML-203-312-20140630_jpg '
解释计划
TABLE ACCESS BY INDEX ROWID msp STORAGEFILE 5 1 176
INDEX RANGE SCAN msp STORAGEFILE 4 1
filename 前16个字符为重复度不高的字符串时,查询会走索引。