自己搞了函数行专列还是可以拼接
Saturday, September 13th, 2008
今天有个需求，要求把一个表里的根据一列（例如id）分组并且将每行的任意几列的数据挨着拼装在一起中间加特殊字符，
然后分组之后每个id对应的几行合成一条记录，例如：
SQL> select * from test;
P_ID NAME VALUE
———- ——————– ——————–
1 AAA aaa
2 BBB bbb
1 ABC abc
3 CCC ccc
2 DDD ddd
SQL> SELECT P_ID, Mystrcat_n(NAME||chr(01)||value) NAME FROM TEST GROUP BY P_ID;
P_ID
———-
NAME
——————————————————————————–
1
AAAaaaABCabc
2
BBBbbbDDDddd
3
CCCccc
寻求Sky.Jian（简朝阳）帮助，给我了一个他自己搞的一个函数，结果不满足我的要求，
在他的基础上修改一下，可以满足以上要求，中间是用chr(01)来分隔的，函数体如下：
（执行此函数到底消耗多少资源没有测试过）
create or replace type Mystrcat_new as object
(
str varchar2(4000),
static function ODCIAggregateInitialize(sctx IN OUT Mystrcat_new) return number,
member function ODCIAggregateIterate(self IN OUT Mystrcat_new,value IN varchar2) return number,
member function ODCIAggregateTerminate(self IN Mystrcat_new,returnValue [...]
ASM Lib包务必注意小版本啊
Friday, September 12th, 2008
这件事是我太大意了，前几天装ASM的RPM包，怎么搞都搞不上去。我迷茫了好几天。到configure最后一步总是过不去，还调整了N多的OS参数，还上metalink上去查。报的错误是这样的：
[root@localhost wt]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets (’[]‘). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C [...]
oracle 11g standby query error: ora-08103 and ora-01410
Wednesday, September 3rd, 2008
范鑫做的测试，我转过来。看起来很简单的一件事情，由于具有偶然性，不能每次都重现，所以特地记录下来，一直报的 ORA-08103: object no longer exists 是由于 standby 上的查询进程导致 ，把standby 激活后查询就正常了
当然这里要交代一下前提，就是在主数据库上有一个job，每天晚上将一个表truncate再插入数据，第二天就发现standby上查询错误，在主数据库上move表之后恢复正常。 但是手工做 truncate却取法重现，重新做个job任务偶尔重现，也不是一定得到这个现象。目前正在进一步测试中，但至少发现了 11g的 standby提供适时查询功能是存在缺陷的。当然不truncate就没问题。
测试在一个已经存在问题的standby上进行，先以standby模式open，再open read only，最后激活open：
oracle@ctr_db1:/home/oracle>export ORACLE_SID=testctrdmsb2
oracle@ctr_db1:/home/oracle>sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Sep 3 10:28:45 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
@>
@>sqlplus /nolog
SP2-0734: unknown command beginning “sqlplus /n…” - rest of line ignored.
@>conn / as sysdba
Connected.
sys@CTRDM_DB1>
sys@CTRDM_DB1>select count(*) from mcc.sync_job_status;
select count(*) [...]
如何恢复被删除的表空间？
Tuesday, September 2nd, 2008
前几天看到论坛上有人问，看到大家都是纸上谈兵，我做了个example。
下载地址：
删除表空间恢复.pdf
学习latch笔记
Sunday, August 31st, 2008
<!– @page { size: 21cm 29.7cm; margin: 2cm } P { margin-bottom: 0.21cm } –>
个人认为科学技术之间存在一定的相关性的，先举一个例子，来比较浅显的了解一下latch的一些特点。下图为一张十字路口照片，来来往往的车辆就是靠十字路口的一个交通信号灯来指挥的，当然有时候也会有按照交通警察的指令来执行。
<!– @page { size: 21cm 29.7cm; margin: 2cm } P { margin-bottom: 0.21cm } –>
中国的交通，十字路口有交通信号灯，信号灯周期性的变化。当东西直行的信号灯是绿的时，东西方向的汽车可以行走，此时南北方向的车需要等待；当南北方向的信号灯是绿的时候，南北方向的车是可以行走的，东西方向的车要等待。当一个方向为绿灯时另一个方向一定是红灯，除非线路故障。看到这个图，我突然想到和latch很有一比，到底有多少呢，让我们一起看看下面几个问题：
1、那么信号灯是起一个什么作用呢？
告诉行使的车里和行人你能不能通过十字路口。
2、汽车等待的是什么？
汽车在等待交通信号灯变成绿色，这样可以通过。
3、汽车是使用信号灯呢还是使用十字路口的那一块马路呢？
汽车得到绿色信号灯之后，会立即行使通过路口，他使用的就是那一个马路。
4、 同时得到这个绿色信号灯的是不是不仅仅一辆车？
同时看到绿灯的车辆有很多，并发的比较多。
5、信号灯变化时间越长是不是就堵的更严重？
信号灯的时间越长，导致一次等待的时间过长，押车的长度会变长，通过路口的速度要明显小于正常形式的速度，这样导致车会越来越多的堵在路上。
6、是不是有的车得不到中间的道路资源他就不等待信号灯而直接右柺呢？
有些车辆在直行的时候没有得到绿灯，他们还有别的方向可以选择，就会右转来直接走掉
7、是不是车辆的个头越大，同时通过路口的车辆就越少？
一次占用整个车道，那岂不是要让后边的车没法通过？
8、当一辆车在一次信号灯没有通过路口时，是不是继续等待，下一次绿色信号灯亮的时候是不是他第一时间可以通过？
当一次绿灯正好没有通过路口，下一次绿灯到来的时候他就可以比较迅速的得到绿灯信号，而快速通过。
发现交通信号灯这个设计很有意思，和oracle的latch很像，不知道是谁学习的谁.
Latch到底是什么呢？
是能不能独自访问或执行某一种共享资源的信号灯。从程序上来讲就是一个变量，标记了某个资源的一个状态。
Latch有三种类型，父latch，子latch，独立latch。其中，在v$latch_parent这个表中存放的是独立latch和父latch，每一个latch存放一条记录。子latch放在 v$latch_children表中，每个子latch存放一条记录，只有少数的latch有子latch，所以在v$latch_children表中，其latch name是一样的，但是其地址不一样，表明他保护的block不想同。在v$latch表中，保存了所有的latch的聚合信息，在这里能看到宏观的latch信息，在v$latch_children中可以看到微观的每个latch的信息。
根据概念理解，对于共享的数据结构都是需要使用latch进行保护的，有些数据结构使用一个latch，有些数据结构使用多个latch（子latch），多个latch的使用可以增加并发度，因为不用访问一个资源而占用了整个链表的latch，latch的访问是独享的。
<!– @page { size: 21cm 29.7cm; margin: 2cm } P { margin-bottom: 0.21cm } –>
看了latch的概念，那么进程如何得到latch呢？
进程得到latch的方式：willing-to-wait和no_wait两种方式：
Willing-to-wait方式是请求一个latch，如果一次请求没有获得，然后又有经过spin和try again，经过多次的spin（_SPIN_COUNT）如果还没有得到latch则进入到sleep状态，进入sleep前，它需要先安排一下他的后事，不能一睡不起呀（如何醒来，超时和repost），还有就是通知v$session_wait自己发生了latch free wait了，从这里可以知道，每次sleep都会对应着一个latch free wait event。spin也可以称之为active wait，cpu时间片该进程还没有消耗完毕，那么这样进程还是在消耗cpu时间的。这样的方式请求的latch的（level）级别要比自己持有的高才可以，为什么呢？如果a进程占有一个level 为5的latch，它去请求一个level为3的latch，而进程b，占有这个level为3的latch，又去请求那个level [...]
MySQL MyIsam 存储引擎索引长度限制测试记录
Sunday, August 17th, 2008
MySQL MyIsam 存储引擎在创建索引的时候，索引键长度是有一个较为严格的长度限制的，所有索引键最大长度总和不能超过1000，而且不是实际数据长度的总和，而是索引键字段定义长度的总和。下面做个简单的测试，记录一下。
root@sky:~# mysql -u sky -p -h127.0.0.1
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 44
Server version: 5.0.51a-log MySQL Community Server (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
sky@127.0.0.1 : (none) 05:23:08> use test;
Database changed
sky@127.0.0.1 : test 05:23:11>
sky@127.0.0.1 : test 05:23:12>
先创建一个MyIsam表，字符集选择latin1，三个字段均设置为varchar 255，：
sky@127.0.0.1 : [...]
敏捷多模式匹配
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.
这样的工作不能在线上数据库做, 只能找一个空闲的非关键的环境来做.
付出这些代价, 换来的结果还是比较满意的, 任务执行时间平均缩短一半.
关于大数据量模糊查询的方法
Monday, July 21st, 2008
说实话，在ITPUB上也好，CNOUG上也好，看到很多人问数据模糊查询的问题，特别是一张表的数据量在200M以后，你的查询速度越来越慢。
其实最最简单的就是 like ‘%xx%’，但是这样效率极差，纯粹在拼机器的IO了。
其实在我的日常工作中，对于模糊查询，主要使用以下几种方案提高效率：
A：全文索引
Oracle自带的，SQL SERVER,MYSQL也有这个功能。它的主要实现方式是拆字。记得“玉面飞龙”有一篇文章介绍ORACLE全文索引的，介绍的很详细。
但是全文索引也有很多不足，如：繁体字，孤僻字的支持不好；内部BUG比较多；自身维护的代价和成本很高；新来的开发搞不懂全文索引查询语法；
目前也就少数几个数据库在用全文索引了，打算在不远的将来，将全文索引拿下线了。全部使用Search实现。
B：搜索引擎
其实alibaba集团全部使用的搜索技术，叫“ISEARCH”；100%的知识产权归阿里巴巴所有。
其实在业界最有名的搜索引擎叫“Lunce”，它不仅仅支持JAVA,也支持.NET。
使用搜索最大的好处，它是可以线性扩展的，不像我们的DB。此外我介绍一下搜索引擎大致过程：
数据库有张LOG表，记录变化过的数据》》搜索引擎根据LOG表的数据，DUMP出一份BUILD数据》》把DUMP的东西分发到各个集群》》每台机器再把这个应用到索引文件》》APCHE提供WEB SERVICE给前台
大致过程就这样。至于索引文件本身是个二进制文件。
我觉得搜索引擎本身的实现机制和ORACLE的全文索引类似，也是根据分词器来分词。如果去查找也是根据一个个字Hash mapping出来的，然后根据位置先后次序，找出哪些是符合的。
