ORA-01779 & BYPASS_UJVC
问题陈述
在调用一个存储过程的时候，发现有个UPDATE语句执行的很慢，这个UPDATE语句是很常见的用一个表中的数据去更新另外一个表中的数据，通常的做法是通过一个关联子查询从"source table" 读取相应的数据来更新"target table"中对应的数据行。同时，为了保证"target table" 中不相关的数据行不会被更新，因此在where语句部分还是要和"source table"做个“join"。很显然，source table会被读取多次，而且由于关联查询的存在，表的连接方式会倾向于Nested Loop Join方式，而不会是Hash Join.
下面是原始的update语句...
UPDATE GEM_CURR_AUTHORIZATION GCA
SET (TIMESTAMP,
STATUS,
ORIGINAL_AUTHORIZATION,
CURRENCY,
CURRENT_AUTHORIZATION,
EXPIRY_DATE,
BLOCK_DATE,
LIMIT_MODE) =
(SELECT SYSDATE,
STATUS,
ORIGINAL_AUTHORIZATION,
CURRENCY,
CROSS_CCY (ORIGINAL_AUTHORIZATION,
CURRENCY,
PACK_GEM_COMMON.GET_METRIC_CURRENCY (METRIC_ID)),
EXPIRY_DATE,
BLOCK_DATE,
LIMIT_MODE
FROM GEM_ORIG_AUTHORIZATION GOA
WHERE GOA.COMBINATION_ID = GCA.COMBINATION_ID
AND NVL (GOA.METRIC_ID, 0) = NVL (GCA.METRIC_ID, 0))
WHERE ( (GCA.COMBINATION_ID = :B2 AND NVL (GCA.METRIC_ID, 0) = :B1)
OR EXISTS
(SELECT NULL
FROM AUTHORIZATION_LENDING LENDER
WHERE LENDER.SOURCE_COMBINATION_ID = :B2
AND NVL (LENDER.METRIC_ID, 0) = :B1
AND LENDER.DEST_COMBINATION_ID = GCA.COMBINATION_ID)
OR (:B2 IS NULL AND :B1 IS NULL))
AND EXISTS
(SELECT NULL
FROM GEM_ORIG_AUTHORIZATION GOA
WHERE GOA.COMBINATION_ID = GCA.COMBINATION_ID
AND NVL (GOA.METRIC_ID, 0) = NVL (GCA.METRIC_ID, 0));
下面是我尝试改写的SQL...
update
(
select
gca.timestamp as dest_timestamp,
gca.status as dest_status,
gca.original_authorization as dest_orig_limit,
gca.currency as dest_currency,
gca.current_authorization as dest_curr_limit,
gca.expiry_date as dest_expiry_date,
gca.block_date as dest_block_date,
gca.limit_mode as dest_limit_mode,
goa.status as src_status,
goa.original_authorization as src_orig_limit,
goa.currency as src_currency,
goa.metric_id as src_metric,
goa.expiry_date as src_expiry_date,
goa.block_date as src_block_date,
goa.limit_mode as src_limit_mode
from GEM_ORIG_AUTHORIZATION goa,
GEM_CURR_AUTHORIZATION gca
where goa.combination_id = gca.combination_id
and nvl(goa.metric_id, 0) = nvl(gca.metric_id, 0)
and ( (gca.combination_id = :B2 and nvl(gca.metric_id, 0) = :B1)
OR (:B2 IS NULL AND :B1 IS NULL)
OR EXISTS
(SELECT NULL
FROM AUTHORIZATION_LENDING LENDER
WHERE LENDER.SOURCE_COMBINATION_ID = :B2
AND NVL (LENDER.METRIC_ID, 0) = :B1
AND LENDER.DEST_COMBINATION_ID = GCA.COMBINATION_ID)
)
)
set dest_timestamp = sysdate,
dest_status = src_status,
dest_orig_limit = src_orig_limit,
dest_currency = src_currency,
dest_curr_limit = CROSS_CCY (src_orig_limit, src_currency, PACK_GEM_COMMON.GET_METRIC_CURRENCY (src_metric)),
dest_expiry_date = src_expiry_date,
dest_block_date = src_block_date,
dest_limit_mode = src_limit_mode;
但是杯具的是，当我执行新的SQL的时候Oracle报出了如下的错误...
ORA-01779: cannot modify a column which maps to a non key-preserved table
出错的原因是因为我的那个inline view中的"source table" -- GEM_ORIG_AUTHORIZATION用来同"target table" -- GEM_CURR_AUTHORIZATION做连接操作的数据行有可能不是是唯一的，因此在进行update操作的时候，对于目标表的一行有可能对应源表中的多行，这样在update操作的时候有可能会得到错误的结果。
错误分析
下面来测试一下什么情况下位报这种错误...
drop table fang_test_1;
drop table fang_test_2;
create table fang_test_1 (id number, name varchar2(10));
create table fang_test_2 (id number, name varchar2(10));
insert into fang_test_1 values(1, 'Fang');
insert into fang_test_1 values(2, 'Fraud');
insert into fang_test_2 values(1, 'Frank');
现在用表fang_test_1中的name更新fang_test_2中的name...
update
(
select a.id a_id, a.name a_name, b.id b_id, b.name b_name
from fang_test_1 a, fang_test_2 b
where a.id = b.id
)
set b_name = a_name;
执行的时候出现错误ORA-01779!
尝试给源表fang_test_1的id列（因为两个表是通过ID列做join的）上创建unique index...
create unique index ui_fang_test_1 on fang_test_1(id);
然后再执行update操作就没有问题了。如果我创建一个联合的unique index会怎么样...
create unique index ui_fang_test_1 on fang_test_1(id, name);
在执行update操作的时候同样报ORA-01779。 这个也是很好理解的，毕竟unique index是建立在id 和name 这两列之上的，而表fang_test_1和表fang_test_2只是通过id列做连接，这个并不能保证fang_test_1的数据行是唯一的。当然如果把f表fang_test_1和fang_test_2的连接条件改成a.id = b.id and a.name = b.name 那么update 操作就没问题了。
现在把update语句改成如下形式…
update
(
select a.id a_id, a.name a_name, b.id b_id, b.name b_name
from fang_test_1 a, fang_test_2 b
where a.id+1 = b.id and a.name = b.name
)
set b_name = a_name;
注意fang_test_1和fang_test_2在id列上的连接条件有点小小的改变 – a.id + 1 = b.id. 这个时候再执行update操作的话，却会报ora-01779! 类似的，如果在列id或name上做一些小小的操作，比如加个函数啥的，如下…
update
(
select a.id a_id, a.name a_name, b.id b_id, b.name b_name
from fang_test_1 a, fang_test_2 b
where a.id = b.id and nvl(a.name, 'TEST') = b.name
)
set b_name = a_name;
这个时候即使更新unique index 为
create unique index ui_fang_test_1 on fang_test_1(id, nvl(name, 'TEST'));
执行update操作的时候也会报 ora-01779, 虽然我们知道这个连接条件能保证唯一性，但是Oracle却赤裸裸地无视之！
总结原因
总结下什么情况会报ora-01779
(1) 源表在连接的列上没有建立unique index
(2) 如果unique index是个组合索引，而源表和目标表做join的列只是这些组合索引中的部分列
(3) 做连接的列有其他操作，比如说函数啊之类。 注意这个列不论是源表还是目标表的都会报错。
Oracle Hint BYPASS_UJVC
对于前两种情况，应该考虑更改SQL语句，对于第三种情况，如果确定Update不会有问题同时又想oracle不报ora-01779这个错误，这个时候可以借助oracle hint /*+ BYPASS_UJVC */. 在inline view的select语句部分加上这个hint, 可以让oracle不做这种数据唯一性的检查，当然结果正确与否只有自己保证了。
解决问题
回到刚开始提到的改写的那个Update SQL语句，其实表GEM_ORIG_AUTHORIZATION上面在列(combination_id, metric_id)上有个组合的unique index的，但是因为在做join的时候，列metric_id上用了NVL函数导致了会报错，因此这里面为了执行这个update语句，只好借助于hint /*+ BYPASS_UJVC */, 如下所示…
update
(
select /*+ BYPASS_UJVC */
gca.timestamp as dest_timestamp,
gca.status as dest_status,
gca.original_authorization as dest_orig_limit,
gca.currency as dest_currency,
gca.current_authorization as dest_curr_limit,
gca.expiry_date as dest_expiry_date,
gca.block_date as dest_block_date,
gca.limit_mode as dest_limit_mode,
goa.status as src_status,
goa.original_authorization as src_orig_limit,
goa.currency as src_currency,
goa.combination_id,
goa.metric_id as src_metric,
goa.expiry_date as src_expiry_date,
goa.block_date as src_block_date,
goa.limit_mode as src_limit_mode
from GEM_ORIG_AUTHORIZATION goa,
GEM_CURR_AUTHORIZATION gca
where goa.combination_id = gca.combination_id
and nvl(goa.metric_id, 0) = nvl(gca.metric_id, 0)
and ( (gca.combination_id = :B2 and nvl(gca.metric_id, 0) = :B1)
OR (:B2 IS NULL AND :B1 IS NULL)
OR EXISTS
(SELECT NULL
FROM AUTHORIZATION_LENDING LENDER
WHERE LENDER.SOURCE_COMBINATION_ID = :B2
AND NVL (LENDER.METRIC_ID, 0) = :B1
AND LENDER.DEST_COMBINATION_ID = GCA.COMBINATION_ID)
)
)
set dest_timestamp = sysdate,
dest_status = src_status,
dest_orig_limit = src_orig_limit,
dest_currency = src_currency,
dest_curr_limit = CROSS_CCY (src_orig_limit, src_currency, PACK_GEM_COMMON.GET_METRIC_CURRENCY (src_metric)),
dest_expiry_date = src_expiry_date,
dest_block_date = src_block_date,
dest_limit_mode = src_limit_mode;
对改写前后的SQL进行了简单的测试，发现还是有很大区别的，如下 (通过设置10046事件 + tkprof得到)
改写前的SQL 的执行情况…
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.09 0.09 0 0 0 0
Execute 1 0.16 0.17 0 213 126 61
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.25 0.26 0 213 126 61
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1182
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE GEM_CURR_AUTHORIZATION (cr=315 pr=0 pw=0 time=168567 us)
61 HASH JOIN RIGHT SEMI (cr=30 pr=0 pw=0 time=4042 us)
818 PARTITION RANGE SINGLE PARTITION: 2 2 (cr=7 pr=0 pw=0 time=3588 us)
818 INDEX FAST FULL SCAN I1_GEM_ORIG_AUTHORIZATION PARTITION: 2 2 (cr=7 pr=0 pw=0 time=1940 us)(object id 362634)
1209 PARTITION RANGE SINGLE PARTITION: 2 2 (cr=23 pr=0 pw=0 time=6093 us)
1209 TABLE ACCESS FULL GEM_CURR_AUTHORIZATION PARTITION: 2 2 (cr=23 pr=0 pw=0 time=2460 us)
61 PARTITION RANGE SINGLE PARTITION: 2 2 (cr=183 pr=0 pw=0 time=2322 us)
61 TABLE ACCESS BY LOCAL INDEX ROWID GEM_ORIG_AUTHORIZATION PARTITION: 2 2 (cr=183 pr=0 pw=0 time=1694 us)
61 INDEX RANGE SCAN I1_GEM_ORIG_AUTHORIZATION PARTITION: 2 2 (cr=122 pr=0 pw=0 time=966 us)(object id 362634)
改写后的SQL执行情况…
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 39 126 61
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 0 39 126 61
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 1182
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE GEM_CURR_AUTHORIZATION (cr=119 pr=0 pw=0 time=12072 us)
61 HASH JOIN (cr=39 pr=0 pw=0 time=2601 us)
818 PARTITION RANGE SINGLE PARTITION: 2 2 (cr=16 pr=0 pw=0 time=3344 us)
818 TABLE ACCESS FULL GEM_ORIG_AUTHORIZATION PARTITION: 2 2 (cr=16 pr=0 pw=0 time=1696 us)
1209 PARTITION RANGE SINGLE PARTITION: 2 2 (cr=23 pr=0 pw=0 time=7284 us)
1209 TABLE ACCESS FULL GEM_CURR_AUTHORIZATION PARTITION: 2 2 (cr=23 pr=0 pw=0 time=4860 us)
改写后的SQL执行明显效率更高，query数量大幅度降低。
--------------------------------------
Regards,
FangwenYu
