AnySQL.net

    Oracle的全文索引是一个很好的搜索解决方案, 不过随着要搜索的内容的增加, 会发现全文搜索的索引会耗掉很多的空间, 也在一定的程度上影响了全文索引的查询速度. 我们可以用如下方法去看, Oracle是如何划分单词的, 如在CR_CTXDEMO表的COL2列上面建一了个CTXSYS.CONTENT类型的全文索引, 则可以用如下SQL语句去查询:

SELECT TOKEN_TEXT, COUNT(*)
  FROM DR$IDX_CR_CTXDEMO_COL2$I
  WHERE ROWNUM < 1000000
  GROUP BY TOKEN_TEXT
  HAVING COUNT(*) > 1000

    可能会得到如下这样的结果:

TOKEN_TEXT          COUNT(*)
----------------- ----------
01                    107284
02                    104629
03                    103982
04                    103776
05                    103362
06                    101581

    看起来是一些无用的单词占据了大部份的记录, 如果确定用户不太会用这样的单词做搜索, 则可以用Stop List来过滤这些单词, 让Oracle在维护全文索引时不索引这些单词, 这样的话全文索引占用的空间就要小多了. 创建一个这样的Stop List的代码:

begin
    ctx_ddl.create_stoplist('DEMO_STOPLIST', 'BASIC_STOPLIST');
    ctx_ddl.add_stopword('DEMO_STOPLIST', '01');
    ctx_ddl.add_stopword('DEMO_STOPLIST', '02');
    ctx_ddl.add_stopword('DEMO_STOPLIST', '03');
    ctx_ddl.add_stopword('DEMO_STOPLIST', '04');
    ctx_ddl.add_stopword('DEMO_STOPLIST', '05');
    ctx_ddl.add_stopword('DEMO_STOPLIST', '06');
end;
/

    然后在创建索引或Rebuild索引时加上StopList参数就可以了, 如下所示:

create index idx_cr_ctxdemo_col2 on cr_ctxdemo(col2)
  indextype is ctxsys.context
  parameters ('stoplist DEMO_STOPLIST');

ALTER INDEX idx_cr_ctxdemo_col2 REBUILD
  PARAMETERS ('REPLACE STOPLIST DEMO_STOPLIST');

    要作优化要懂的东西还真不少!

    对于Oracle的字符集, 一直不是很明白的, 除了在出道的当年(98年)范过一次这方面的错后, 到还没有第二次, 算是走运了. 下面来做一个小实验, 加深一下理解, 我测试用的数据库端是UTF8格式的, 测试用的表结构如下, 第一个字段用于记录NLS_LANG的设置, 第二列则为相同的值.

create table t_charset(col1 varchar2(10), col2 varchar2(20));

    接下来, 我在Windows在命令窗口(cmd.exe)中更改NLS_LANG的设置为中文, 然后插入一条记录.

set NLS_LANG=.ZHS16GBK
SQL> insert into t_charset values ('ZHS16GBK','中国');

    接下来更改NLS_LANG的设置为中文, 然后插入另一条记录.

set NLS_LANG=.UTF8
SQL> insert into t_charset values ('UTF8','中国');

    用Oracle的Dump函数来看一下第二个字段在不同设置下的存贮.

SQL> col col2 format a40
SQL> select col1, dump(col2) col2 from t_charset;

COL1       COL2
---------- ----------------------------------------
ZHS16GBK   Typ=1 Len=6: 228,184,173,229,155,189
UTF8       Typ=1 Len=4: 214,208,185,250

    发现NLS_LANG设为ZHS16GBK的, 里面存放是以UTF8格式存的, 而设为UTF8的, 存放格式是中文的, 难道反了吗? 不是, 因为我的Windows机器的区域是中文, 因此在命令行输入文本时, 文本的字符集是中文, 因此NLS_LANG和服务器相同时, 不作转换, 所以存放格式其实是中文, 而设成ZHS16GBK(和机器的区域一致时)Oracle自动作了转换, 其存贮格式就是UTF8的. 当客户端环境变量设成UTF8时, 中国的用户输入时存成了中文字符集, 其他国家的用户输入时则存成了其他字符集.

    在进行字符集转换时, 我们可以写一个角本(如Perl)程序, 将值在UTF8的环境下取出来, 然后变更环境变量设置, 再将值更新回去. 当我用iconv工具来进行文件的字符集转换时, 如果源文件和指定的源字符集不一样时, iconv会报错. Oracle的csscan却不是用来进行字符集转换的, 而是用来找出那些表中的那些记录还没有被成功转换, 原理是iconv转换报错是一样的.

    再来回顾一下上一篇中的两条样本数据.

SQL> col col2 format a40
SQL> select col1, dump(col2) col2 from t_charset;

COL1       COL2
---------- ----------------------------------------
ZHS16GBK   Typ=1 Len=6: 228,184,173,229,155,189
UTF8       Typ=1 Len=4: 214,208,185,250

    其实csscan的工作原理和CONVERT函数一样, 如果数据库中存放的已经是UTF8字符集了, 那么肯定可以转换到UTFE字符集, 因此我们在使用csscan或CONVERT函数时, 源字符集指定为UTF8而目标字符集指定为UTFE(因为是UTF8的超集). 先测试第一条, 数据库中存放的不是UTF8格式的, 因此转换就报错了.

SQL> SELECT CONVERT(COL2,'UTF8','UTFE') FROM T_CHARSET WHERE COL1='UTF8';
                                        *
ERROR at line 1:
ORA-12703: this character set conversion is not supported

    测试另一条记录则可以成功转换, 没有报错.

SQL> SELECT CONVERT(COL2,'UTF8','UTFE') FROM T_CHARSET WHERE COL1='ZHS16GBK';

CONVERT(COL2,'UTF8','UTFE')
-----------------------------------------------
......

    csscan程序可以找出所有含有字符字段的表中, 存放的内容不是指定字符集(如CONVERT函数转换报错)的记录, 将ROWID存放在预先创建好的表中. 然后你可以查询这些表, 将没有转换成目标字符集的记录处理掉.

    在用Oracle的exp/imp工具时, 常常有人嫌它忙, 这个两个工具是比较慢, 除了用比较大的buffer参数外, 还是有办法可以加快的. 在exp时, 可以考虑更改表的并行度(ALTER TABLE xxx PARALLEL 4), 或者更改exp进程的Multiple Block Read参数的值.

SELECT SID, SERIAL# FROM V$SESSION WHERE PROGRAM LIKE 'exp%';
exec dbms_system.set_int_param_in_session( sid, serial#,
                'db_file_multiblock_read_count', 128);
exec dbms_system.set_bool_param_in_session( sid, serial#,
                '_serial_direct_read', true);

    在imp时, 没有什么办法可以提高它的速度, 但如果imp过程中包含了索引, 则可以不导入索引和约束(Primary或Unique), 而改为人工来创建. 如果不想这么麻烦呢, 也可以通过更改Session一级的参数微调一下.

SELECT SID, SERIAL# FROM V$SESSION WHERE PROGRAM LIKE 'imp%';
exec dbms_system.set_int_param_in_session( sid, serial#,
                'db_file_multiblock_read_count', 128);
exec dbms_system.set_int_param_in_session( sid, serial#,
                'sort_area_size', 104857600);
exec dbms_system.set_int_param_in_session( sid, serial#,
                'sort_area_retained_size', 104857600);
exec dbms_system.set_int_param_in_session( sid, serial#,
                'sort_multiblock_read_count', 128);

    更改表的并行度的方法不是很好, 但是Oracle好象没有提供强制其他会话Parallel运行的接口, 用Logon触发器, 还是觉得太麻烦了一些, 不明白Oracle为什么不给exp工具加个HINT或Parallel命令行选项.

    Oracle在进行值的大小比较时, 不是我们人脑所想象的那种值比较, 而是将这些值的物理存贮进行字典式的字节比较. 我们来看一下数值1和2的物理存贮.

SQL> SELECT DUMP(1) FROM DUAL;

Typ=2 Len=2: 193,2

SQL> SELECT DUMP(2) FROM DUAL;

Typ=2 Len=2: 193,3

    可以看到"1<2"就是象"AB<AC"这样比较的. 对于Timestamp类型也是这样的.

SQL> select dump(current_timestamp) as col1 from dual;

Typ=188 Len=20: 7,215,12,4,2,15,5,0,7,7,2,224,249,0,5,49,0,0,0,1

SQL> select dump(current_timestamp) as col1 from dual;

Typ=188 Len=20: 7,215,12,4,2,15,9,0,3,1,11,0,249,0,5,49,0,0,0,1

    对于Timestamp with time zone类型, 就不能靠这样直接来比较了, 而需要转换到UTC(+00:00)的值来进行比较, 因此在这种类型上建索引时, 实际是上建了函数索引.