问题背景

最近的一次SQL优化再次想起这个参数，在此做个复盘和总结，先说结论：当初始化参数BLANK_PAD_MODE=0，数据库参数SPACE_COMPARE_MODE=1时，字符类型的字段无法走索引范围定位(SSEK)，只能进行索引全扫描(SSCN)或全表扫描(CSCN)。这个参数开启的目的是在建库参数BLANK_PAD_MODE没有开启的情况下也能使查询语句中字符串的后缀空格参与比较，但索引并不受参数影响，索引存储的数据仍然按照建库参数字符串后缀空格不参与比较来排序创建的。

问题场景

1、实例初始化

--空格填充模式 BLANK_PAD_MODE 设置为0
dminit path=/dmdata PAGE_SIZE=32 EXTENT_SIZE=32 LOG_SIZE=2048 CASE_SENSITIVE=Y CHARSET=1 DB_NAME=DMDB INSTANCE_NAME=DMDB BLANK_PAD_MODE=0

2、创建表和初始化数据

CREATE TABLE TEST(COLA VARCHAR(20),COLB VARCHAR(20));
--插入数据
INSERT INTO TEST select 'A1','B1' from dual;
INSERT INTO TEST select 'A'||level||' ','B'||level from dual connect by rownum<=1000;
COMMIT;
--创建索引
create index idx_cola on test(cola);
--统计信息
CALL DBMS_STATS.GATHER_TABLE_STATS('SYSDBA','TEST',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');  

3、查看SQL正常执行计划

SQL> select cola,colb,length(cola) from test where cola = 'A1';

行号     COLA COLB LENGTH(COLA)
---------- ---- ---- ------------
1          A1   B1   3    --检索出字符串后缀空格
2          A1   B1   2

1   #NSET2: [1, 1->2, 108] 
2     #PRJT2: [1, 1->2, 108]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [1, 1->2, 108]; IDX_COLA(TEST)
4         #SSEK2: [1, 1->2, 108]; scan_type(ASC), IDX_COLA(TEST), is_global(0), scan_range['A1','A1']

Statistics
-----------------------------------------------------------------
        0	    data pages changed
        0	    undo pages changed
        3	    logical reads
        0	    physical reads
        0	    redo size
        304	    bytes sent to client
        123	    bytes received from client
        1	    roundtrips to/from client
        0	    sorts (memory)
        0	    sorts (disk)
        0	    rows processed
        0	    io wait time(ms)
        3	    exec time(ms)

正常情况下，等值条件且字段上存在索引，对于TEST表是可以进行SSEK索引定位的，实际过滤行数为2。

4、设置比较字符串后缀空格

SQL> SP_SET_PARA_VALUE(2,'SPACE_COMPARE_MODE',1);

5、开启后执行计划和查询结果

SQL> select cola,colb,length(cola) from test where cola = 'A1';

行号     COLA COLB LENGTH(COLA)
---------- ---- ---- ------------
1          A1   B1   2

1   #NSET2: [1, 1->1, 108] 
2     #PRJT2: [1, 1->1, 108]; exp_num(4), is_atom(FALSE) 
3       #BLKUP2: [1, 1->1, 108]; IDX_COLA(TEST)
4         #SLCT2: [1, 1->1, 108]; TEST.COLA = 'A1'
5           #SSCN: [1, 1->1001, 108]; IDX_COLA(TEST); btr_scan(1); is_global(0)

Statistics
-----------------------------------------------------------------
        0	    data pages changed
        0	    undo pages changed
        77	    logical reads
        0	    physical reads
        0	    redo size
        269	    bytes sent to client
        123	    bytes received from client
        1	    roundtrips to/from client
        0	    sorts (memory)
        0	    sorts (disk)
        0	    rows processed
        0	    io wait time(ms)
        0	    exec time(ms)

执行计划第五步可见对TEST表进行了SSCN索引全扫描，实际扫描行数1001，这在数据量大的情况下就会导致SQL效率低下。

总结

1、兼容Oracle的字符串后缀空格的情况，还是需要使用初始化参数 BLANK_PAD_MODE=1，SPACE_COMPARE_MODE=1虽然可以保证结果集准确，但却会产生性能问题；
2、如果初始化实例时设置 BLANK_PAD_MODE=1，不论 SPACE_COMPARE_MODE 怎么设置，都不会影响索引定位的使用。