Archive for March 11th, 2008
全文索引的代价
Tuesday, March 11th, 2008
今天下午做了全文索引的update测试。被测试的SQL如下：
update ali_zeus_customer set OPEN_REASON=’wt’ where rownum=1 （OPEN_REASON字段是个全文索引字段）
结果做了把TRACE发现，仅仅是这条UPDATE，全文索引需要执行下面一系列的SQL，比普通INDEX字段维护的大家和成本要高很多。
update ali_zeus_customer set OPEN_REASON=’wt’ where rownum=1
DELETE “ZEUS”.”DR$ALI_ZEUS_CUST_OPEN_CIND$K” WHERE TEXTKEY = :rid RETURNING DOCID INTO :docid
INSERT INTO “ZEUS”.”DR$ALI_ZEUS_CUST_OPEN_CIND$N” (NLT_DOCID,NLT_MARK) VALUES(:docid,’U’)
INSERT INTO DR$DELETE (DEL_IDX_ID,DEL_IXP_ID,DEL_DOCID) VALUES(:idxid,:ixpid,:docid)
INSERT INTO CTXSYS.DR$PENDING (PND_CID, PND_PID, PND_ROWID, PND_TIMESTAMP, PND_LOCK_FAILED) VALUES(:1, :2, :3, SYSDATE, ‘N’)
begin ctxsys.syncrn(:idxid, :ixpid, :rtabnm); end;
select del_docid from ctxsys.dr$delete where del_idx_id = :idxid and del_ixp_id = [...]
Returnning into的使用
Tuesday, March 11th, 2008
今天在做ORACLE本身的TRACE的时候发现的。以前还真没用过。示例如下：
@>create table wt(id number,name varchar2(200)); Table created.
@>insert into wt values(1,’mytest’);
1 row created.
@>commit; Commit complete.
@>declare
2 va varchar2(200);
3 begin
4 delete from wt where id=1 returning name into va;
5 dbms_output.put_line(’My value:’ || va);
6 end;
7 /
My value:mytest
PL/SQL procedure successfully completed.
