Author Archive
Saturday, August 2nd, 2008
业务方一个需求, 要对一个表中某些文本字段进行多模式匹配, 找出含有某个关键词列表中关键词的记录.
为了实现这个需求, 搞了如下的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.
这样的工作不能在线上数据库做, 只能找一个空闲的非关键的环境来做.
付出这些代价, ...
Posted in 大话技术 | 1 Comment »
Monday, May 26th, 2008
以前对hash join时应该用大记录集做驱动表还是用小记录集做驱动表一直存在疑虑, 直到最近做了一个实验, 才真正体会到其间的差别.实验过程如下.
用大记录集做驱动的执行计划如下:
———————————————————————————————————————————————————
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost |
———————————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 12 | | 1784 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
|* 2 | HASH JOIN | | 1820K| 20M| 31M| ...
Posted in 大话技术 | No Comments »
Monday, May 26th, 2008
最近业务部门提出一个需求, 数据库里有些字段中保存的信息里全角和半角字符参杂, 影响市容, 要求做数据订正, 全部统一.
其中英文/数字/英文标点符号 都统一用半角字符, 而日文字符都统一用全角字符. 而且提出订正的字段中还包含一些clob字段.
那就函数+游标, 慢慢订正吧, 分析了一下, 要写四个函数:
1. varchar2数据类型的, 全角英文变半角.
2. clob数据类型的, 全角英文变半角.
3. varchar2数据类型的, 半角日文变全角.
4. clob数据类型的, 半角日文变全角.
开工:
第一个, 还好, 有现成函数: to_single_byte.
TO_SINGLE_BYTE returns char with all of its multibyte characters converted to their corresponding single-byte characters. char can be of datatype CHAR, VARCHAR2, NCHAR, or NVARCHAR2. The value returned is ...
Posted in 大话技术 | No Comments »
Friday, March 21st, 2008
起因是一个SQL走错了索引
SELECT ROWID AS rid
FROM tab_1 s
WHERE s.c_id = :1
ORDER BY s.gmt_r DESC
执行计划如下
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes| Cost |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 858| 25740| 138K|
|* 1 | TABLE ACCESS BY INDEX ROWID | tab_1 | 858| 25740| 138K|
|* 2 ...
Posted in 大话技术 | 3 Comments »
Tuesday, February 26th, 2008
在utf8字符集的数据库中如何做数据订正.这个问题对我们DBA来说是比较现实的.
矛盾在于当数据库是UTF8时, 我们用Windows的操作系统如何input到数据库一个UTF8的文本.
经过多次的探索, 发现有三种方式.
1. 当Windows是简体中文操作系统时, 一个普通的txt文件(或者sql文件)都是ZHS16GBK格式的,
这个时候如果我们期望输入数据库的UTF8文本都是属于ZHS16GBK字符集时
(简单的说就是我们想输入简体中文或繁体中文时),
直接走SecureCRT 输入的中文都可以自动被转化为UTF8的格式.
与做普通字符集的数据订正没有区别.
2.当Windows是简体中文操作系统时, 如果我们期望输入数据库的UTF8文本不属于ZHS16GBK字符集时,
有两种方法把真正的UTF8字符输入给数据库
第一种:
把需要输入的文本保存为UTF8格式, 然后本机的NLS_LANG=AMERICAN_AMERICA.UTF8,
然后用SQLLDR到数据库中, 就可以了.
第二种:
把需要执行的SQL文件保存为UTF8格式, 然后SCP到对应的服务器上, 服务器的NLS_LANG=AMERICAN_AMERICA.UTF8,
然后在sqlplus里面用@操作符来调用这个SQL文件, 就可以了.
最后一种方法比较简便, 我举个例子说明一下:
'ｱｲｳｴｵ'这串字符是半角的日文, 不属于ZHS16GBK字符集.
那把如下的语句保存为名为test.sql的文件
select 'ｱｲｳｴｵ', dump('ｱｲｳｴｵ',1016) from dual;
文件保存的格式选择为utf8.
然后scp到服务器上.
[admin@japan_db1 jonathan]$ env|grep NLS_L
NLS_LANG=AMERICAN_AMERICA.UTF8
ops$admin@JAPAN>l
1* select 'ｱｲｳｴｵ', dump('ｱｲｳｴｵ',1016) from dual
ops$admin@JAPAN>@test
SP2-0042: unknown command "" - rest of line ignored.
'ｱｲｳｴ�� DUMP('ｱｲｳｴｵ',1016)
ｱｲｳｴｵ Typ=96 Len=15 CharacterSet=UTF8: ef,bd,b1,ef,bd,b2,ef,bd,b3,ef,bd,b4,ef,bd,b5
Elapsed: 00:00:00.00
ops$admin@JAPAN>spool off
Posted in 大话技术 | 1 Comment »
Wednesday, January 23rd, 2008
select
decode(username,
'大师', '业界评论+吃喝玩乐',
'旺旺', '前沿技术+时事评论',
'Donny', '主机存储+旅行照片',
'陈立', '技术，美女，漫画，各种片',
'老罗', '加班以及加班的体会',
'SKY', '阿里开发DBA第一牛',
'涛哥', 'oracle+mysql+postgresql+sqlserver+access+foxpro…',
'小强', 'oracle技术心得',
'勇斌', 'java+oracle',
'jacky', '流水帐+育儿知识'
) as 个人分工
from dual;
Posted in 团队风采 | 6 Comments »
