Oracle 10 MERGE性能

我在Oracle 10中碰着了与MERGE很是稀疏的机能相干题目.简而言之,我有一个存储进程,它按照体系中的勾当计较和存储用户排名,而且只包括一个MERGE语句：

MERGE INTO user_ranks target USING 
([complex query that returns rank_id and user_id])src ON 
(src.user_id = target.user_id)
WHEN MATCHED THEN UPDATE SET target.rank_id = src.rank_id
WHEN NOT MATCHED THEN INSERT (target.user_id,target.rank_id)
  VALUES (src.user_id,src.rank_id);

// user_ranks table structure:
CREATE TABLE user_ranks (user_id INT NOT NULL 
PRIMARY KEY USING INDEX (CREATE UNIQUE INDEX UQ_uid_uranks ON user_ranks(user_id)),rank_id INT NOT NULL,CONSTRAINT FK_uid_uranks FOREIGN KEY (user_id) REFERENCES users(id),CONSTRAINT FK_rid_uranks FOREIGN KEY(rank_id) REFERENCES ranks(id));
// no index on rank_id - intentionally,ranks table is a lookup with 
// a very few records and no delete/update allowed

用作MERGE源的子查询最多返回1笔记录(user_id作为参数转达给进程).这是相等昂贵的,但执行时刻是可以接管的(1-1.2秒).题目是MERGE执行时刻增进到40秒以上,我不知道为什么.我实行行使LEADING提醒但没有乐成.可是,假如我将语句分成两部门,第一部门 – 运行SELECT子查询并将功效(rank_id)存储到变量然后归并 – MERGE … USING(SELECT user_id,rank_id FROM DUAL)src ……统统正常.按照我的阅读,Oracle的MERGE存在已知题目,但它们首要与触发器相干(在我的环境下没有触发器).它还说MERGE比INSERT和UPDATE的组合事变慢,但我以为“正常”差别约莫是5-10％,而不是30倍……

我想知道我做错了什么……感谢你的提议.

更新
执行打算很长,在这里宣布,简而言之：子查询本钱自己是12737,归并 – 76305.统计输出归并：

>统计

108  recursive calls
           4  db block gets
    45630447  consistent gets
       24905  physical reads
           0  redo size
         620  bytes sent via SQL*Net to client
        1183  bytes received via SQL*Net from client
           2  SQL*Net roundtrips to/from client
           2  sorts (memory)
           0  sorts (disk)

票据查询：

>统计

8  recursive calls
           0  db block gets
          34  consistent gets
           0  physical reads
           0  redo size
         558  bytes sent via SQL*Net to client
         234  bytes received via SQL*Net from client
           1  SQL*Net roundtrips to/from client
           1  sorts (memory)
           0  sorts (disk)

办理要领

请留意,行使此要领得到的信息比简朴的表明打算更准确.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!