业务方一个需求, 要对一个表中某些文本字段进行多模式匹配, 找出含有某个关键词列表中关键词的记录.
为了实现这个需求, 搞了如下的SQL:

select id, result
from
(
select /*+parallel(t 16)*/id,
decode(sign(instr(x,’关键词1′)),1,’关键词1,’,null) ||
decode(sign(instr(x,’关键词2′)),1,’关键词2,’,null) ||
decode(sign(instr(x,’关键词3′)),1,’关键词3,’,null)
as result
from
(
select id as id, col1||’,'||col2 as x
from table1
) t
)
where result is not null
/

说明一下:

1. 输出结果:
 所有包含任意关键词的记录, 输出记录的id和该笔记录中所匹配到的关键词, 也可以根据需要输出其他字段.
 
2. 关键词列表: 把需要扫描的关键词都拼凑到SQL中, 而不是选择存放在一个表中去做表关联.
 这样做是考虑到如果做表关联, instr这样的需求只能走nest loop, 而table1的记录有很大(百万级甚至更高).
 而且关键词数量也不少, 一般是一两千个, 我这里为了便于阅读, 只写了三个.
 这样的情况无论怎么样走nest loop, 逻辑读都非常大.
 
3. 关于并行: 需求紧急, 为了短平快的实现, 我用了并行.
 也正好说说我对并行的理解.
 并行, 其实是利用闲置的数据库服务器资源来换取任务执行的时间.
 如果简单对一个10G级的大表进行全表扫描, 把并行开到32, 存储的吞吐一下子就到了极限.
 而对于上面写的instr的SQL, 把并行度开到16, 则存储很闲, CPU里的user%一下子就到了96,7, 服务器load也到了很高的水平.
 有一次不小心开了两个上面的SQL同时跑, 我有幸看到了三位数的load.
 这样的工作不能在线上数据库做, 只能找一个空闲的非关键的环境来做.
 付出这些代价, 换来的结果还是比较满意的, 任务执行时间平均缩短一半.

• 

  上次和陈立测试过，感觉INSTR速度是最快的了。
  和别的表关联，逻辑读反而高了。
  我这里移植DW打分程序，也是用INSTR的。

  vogts @ August 5, 2008 |