当前位置：首页 > news >正文

ORA-01779 BYPASS_UJVC 11.2后废弃了

news 2025/9/10 19:15:21

有这么个update语句
update A t
set status = 1
where exists (select 1
from B B
where B.code = A.code)
因性能问题需要修改写法。
在oracle10G这么update是没问题的：
update(
select
A.status,1 t_status
from A A inner join B B
on A.code = B.code
)
set status = t_status;
在oracle11G则还是会报错，说明hint无效。

以下是http://t.askmaclean.com/thread-1157-1-1.html的问答：
一个好的问题的基本要素：一个好的标题、　完整的版本信息、完整的日志信息、报错信息

ODM TEST1:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> create or replace view v_empdept as
2 select x.empno, x.ename, x.job, y.deptno, y.dname
3 from emp x, dept y
4 where x.deptno = y.deptno
5 /

View created.

SQL> update v_empdept
2 set dname = 'AP_TUNNING'
3 where empno = '7369'
4 /
set dname = 'AP_TUNNING'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

SQL> update v_empdept
2 set dname = 'AP_TUNNING'
3 where empno = '7369'
4 /

1 row updated.

SQL> commit;

Commit complete.

ODM TEST2:

SQL> select * from v$version
2 ;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> update v_empdept
2 set dname = 'AP_TUNNING'
3 where empno = '7369'
4 /
set dname = 'AP_TUNNING'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

bypass_ujvc 是内部使用的hint ，在11.2.0.1以后 bypass_ujvc 被默认忽略

修改 optimizer_features_enable='8.1.5'; 无法 workaround 该问题

SQL> alter session set optimizer_features_enable='8.1.5';

Session altered.

SQL>
SQL> update v_empdept
2 set dname = 'AP_TUNNING'
3 where empno = '7369'
4 /
set dname = 'AP_TUNNING'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

在2010年就有类似的bug 被report 但是Oracle开发部门应当是铁了心不恢复这个 hint了
ODM FINDING:

Bug 9595084: NO LONGER PREVENTS ORA-01779

Hdr: 9595084 11.2.0.1 RDBMS 11.2.0.1 QRY OPTIMIZER PRODID-5 PORTID-226
Abstract: NO LONGER PREVENTS ORA-1779

PROBLEM:
--------
While on 10.2.0.2 was using hint in application code to
avoid occurance of Ora-1779 when updating through a view.

Once migration done to 11.2.0.1 the hint no longer is preventing the checks
normally ignored by having the hint set and now Ora-1779 errors occuring.

DIAGNOSTIC ANALYSIS:
--------------------
Testcase was sent.

Tested on:
============
10.2.0.4: update gets 0 rows updated.

11.1.0.7: update gets 0 rows updated.

11.2.0.1: Gets Ora-1779 error.

SQL> update test_v set c2 = 10;
update test_v set c2 = 10
*
ERROR at line 1:
ORA-1779: cannot modify a column which maps to a non key-preserved table

WORKAROUND:
-----------

RELATED BUGS:
-------------

REPRODUCIBILITY:
----------------
Easily reproduced.

11.2.0.1 test performed on:

TEST CASE:
----------
1)Create the testuser.

Create user DNBI_DBA identified by DNBI_DBA
default tablespace users
temporary tablespace temp;

Grant dba to DNBI_DBA;

2)Connect as that user, create 2 tables, and a view that uses the hint, then
query the view

connect DNBI_DBA/DNBI_DBA

create table test1 (c1 number,c2 number);
create table test2 (c1 number,c2 number);
create or replace view test_v as select a.c2 from
test1 a, (select distinct c1,c2 from test2) b where a.c1 = b.c1 and
a.c1 = 1
/

update test_v set c2 = 10;

STACK TRACE:
------------

SUPPORTING INFORMATION:
-----------------------
Customer has requested sev 1 on this issue for following reasons.
-the application code used uses this hint throughout the code
-this is preventing 10.2.0.2 to 11.2.0.1 migratiuon scheduled for tomorrow
Cannot proceed with migration untl this is resolved.

DIAL-IN INFORMATION:
--------------------

IMPACT DATE:
------------
Migration being prevented is scheduled for tomorrow.

This is at severity 1. A warm-hand off is required and the bug
Should be assigned to the engineer you contact for the warm hand-off.
This may require that you override the BAT assignment.
Failure to perform a warm-hand off may result in a delay in the

查看全文

http://www.lryc.cn/news/330497.html