本文地址：https://www.askmac.cn/archives/bypass_ujvc.html
- 本资料中设计到的 BYPASS_UJVC 提示是没有在oracle公司的语句件中记载的提示语语句。
- 不支持使用BYPASS_UJVC提示，或者不能从安全性的角度来使用。
- 指定BYPASS_UJVC 提示，发生了不太恰当的操作时，也无法执行修正。
- 使用BYPASS_UJVC提示时，需要参考本资料，将其变更为不使用提示语语句的处理。
- 为了删除BYPASS_UJVC提示，需要在实际环境中进行充分的验证。
可以更新的结合视图与ORA-1779
[参考] 样本数据
create or replace view sample_v1 as
select empno,ename,d.deptno,dname,loc
from emp e,dept d
where e.deptno = d.deptno (+)
order by 1;
可以更新的结合视图 (Updatable Join View)
更新视图时的注意事项
- 对于视图的DML操作的限制中，应用如下基准。
- 视图的定义中使用包含SET或者DISTINCT运算符、GROUP BY语句或者group函数查询时，无法对其视图的实际表插入、更新、或者删除行。
- 视图的定义中使用了WITH CHECK OPTION，从实际表中无法选择行时，无法通过视图对实际表的行进行插入或者更新。
- 没有DEFAULT语句的NOT NULL列会在视图中被省略，无法通过视图对实际表的行进行插入或者更新。
- 制成视图时如果使用DECODE(deptno, 10, “SALES”, …)类似式子的话，就无法通过视图对实际表的行进行插入或者更新。
更新可以更新的视图时的注意事项
- 视图的各列需要映射单一表的列。
- 视图中，不能包含以下内容。
- 集合运算符
- DISTINCT运算符
- 总计功能或者分析功能
- GROUP BY、ORDER BY、MODEL、CONNECT BY或者START WITH语句
- SELECT列表中的收集式
- SELECT列表中的副查询
- WITH READ ONLY指定的副查询
- 结合 (有例外 … <下章会谈到>)
- 可以更新的视图包含疑似的列或者式子时，使用参考这些疑似列或者式子的更新语句时，无法更新实际环境中的表的行。
- DELETE语句中，通过结合，制成保存多个key的表的话，不管视图是否指定WITH CHECK OPTION来制成，都会从FROM语句中所指定的最开始的表中删除。
通过结合视图更新时的注意事项
- DML语句仅仅会影响结合基础的1个表。
- UPDATE语句
- 所以可以更新的结合视图的列都需要对保存key的表的列进行映射。
- 在视图的定义中，使用WITH CHECK OPTION语句时，所有的结合列以及重复表的列都无法更新。
- DELETE语句
- 结合中保存key的表只有一个时，无法在结合视图中删除行。
- 这个保存可以的表可以使用FROM语句来重复。
- 视图的定义中，使用了WITH CHECK OPTION语句，重复保存key表示，无法从视图中删除行。
- INSERT语句
- INSERT语句中，请不要参考没有保存key的表。
- 结合视图的定义中，使用WITH CHECK OPTION语句时，无法使用INSERT语句
可以更新的结合视图的确认方法
SQL> select table_name,column_name,updatable,insertable,deletable 2 from user_updatable_columns, user_views 3 where view_name = table_name; TABLE_NAME COLUMN_NAME UPD INS DEL ------------------------------ ------------------------------ --- --- --- SAMPLE_V1 EMPNO YES YES YES SAMPLE_V1 ENAME YES YES YES SAMPLE_V1 DEPTNO NO NO NO SAMPLE_V1 DNAME NO NO NO SAMPLE_V1 LOC NO NO NO
ORA-01779
ORA-01779
[JP] 无法对没有保存key的表的列进行映射。
[EN] cannot modify a column which maps to a non key-preserved table
结合的结果
- 可以更新保存key的表的列
- 对于不是保存key的表的列进行更新时，就会发生ORA-01779
保存key的表 (Key-Preserved Table)
保存key的表是指什么 ….
所有表的key都是结合的结果key时，其表就会变成保存key的表。换言之，保存key的表就是指结合之后依旧保存了key的表。
样本 #1
样本 #2
样本 #3
样本#4:UPDATE语句内的子查询
[参考] 样本#4的event 10046
Update (
select empno,ename,d.deptno,dname,loc from emp e,dept d where e.deptno=d.deptno (+) order by 1)
set ename='ORACLE' where empno=7369
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 3 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 3 3 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE EMP (cr=3 pr=0 pw=0 time=595 us)
1 NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=154 us)
1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=113 us)
1 INDEX UNIQUE SCAN PK_EMPNO (cr=1 pr=0 pw=0 time=56 us)(object id 51936)
1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=27 us)(object id 51934)
update /*+ BYPASS_UJVC */ (
select empno,ename,d.deptno,dname,loc from emp e,dept d where e.deptno=d.deptno (+) order by 1)
set dname='ACCOUNTING' where empno=7499
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 3 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 1 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 58 (SCOTT)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE DEPT (cr=3 pr=0 pw=0 time=199 us)
1 NESTED LOOPS OUTER (cr=3 pr=0 pw=0 time=59 us)
1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=35 us)
1 INDEX UNIQUE SCAN PK_EMPNO (cr=1 pr=0 pw=0 time=20 us)(object id 51936)
1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=10 us)(object id 51934)
本文地址：https://www.askmac.cn/archives/bypass_ujvc.html
[参考] ORA-01779以外的ORA错误
- ORA-01732
- [JP] 这个视图中数据操作无效
- [EN] data manipulation operation not legal on this view
- INSERTABLE是对NO表执行INSERT语句时发生的错误
- 即使指定列，也无法插入NULL
- ORA-01752
- [JP] 无法从多个表的视图中删除
- [EN] cannot delete from view without exactly one key-preserved table
- DELETABLE是对NO表执行DELETE语句时发生的错误
BYPASS_UJVC提示
制成优化的执行计划时，对UJVC(Updatable Join View) 的测试进行bypass的提示语句
BYPASS_UJVC 提示的利用的悬念点
- 没有document化的提示语句
- 不支持对象
- 更新的对象行数不同
- 基于对象行数的处理中，可能会产生业务逻辑错误
- 更新了意料之外的数据
- 由于视图化，可能会更新之前隐藏的数据
更新的对象行数不同
[参考] event 10046 的结果
update /*+ BYPASS_UJVC */ sample_v1 set loc = 'BOSTON‘ where deptno = 20 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 3 0 0 Execute 1 0.00 0.00 0 8 7 5 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.01 0.00 0 11 7 5 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 58 (SCOTT) Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE DEPT (cr=8 pr=0 pw=0 time=2301 us) 5 SORT ORDER BY (cr=8 pr=0 pw=0 time=1600 us) 5 HASH JOIN OUTER (cr=8 pr=0 pw=0 time=1437 us) 5 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=218 us) 1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=24 us)(object id 51934)
本文地址：https://www.askmac.cn/archives/bypass_ujvc.html
更新了意料之外的数据
面向废止BYPASS_UJVC提示的指导线
【简介】 迁移的指导线
- 指定对应地址
- 在每个批量处理的模型中执行
- à高效使用 Real Application Testing
- 改写对应地址
1.◎ 使用MERGE语句
- 将从源表中搜索到数据结果记录在USING语句中，通过在ON语句中记录这个结果的结合条件就可以不使用UJV来记录
2.使用○ WHERE EXISTS语句
- 通过在WHERE EXISTS语句中记录结合条件，就可以改写不使用视图以及inline视图的SQL语句
- 比MERGE语句的SQL语句稍微复杂一点
3.使用△Instead of 触发器
Oracle Real Application Testing 概要
- Database Replay
- 吞吐量的性能测试
- 修正应用同时执行性的问题
- SQL Performance Analyzer
- SQL的响应时间测试
- 指定SQL重定向以及调优
本文地址：https://www.askmac.cn/archives/bypass_ujvc.html
Database Replay概要
- 利用正式数据库的负载进行性能测试
- 维持正式环境中特有的时机以及事务的依赖性、思考时间
- 测量与测试事务的吞吐量
- 指定应用的可扩展性，新功能的并行性
- 执行Oracle Database 9iR2以及10gR2更新
BYPASS_UJVC改写对应地址
| 方法 | 内容 | 平均 | 是否推荐 | |
| 案1 | 使用MERGE语句 | 将从源表中搜索到的结果记录在USING语句中，将这个见过的结合条件记录在ON语句中，就可以不使用UJV来记录 | 直观上来说，便于理解，并且可以改写成简单的SQL语句 | ◎ |
| 案2 | 使用WHERE EXISTS语句 | 通过在WHERE EXISTS语句记录结合条件，就可以不使用视图以及inline视图来改写SQL语句 | SQL语句比MERGE语句要复杂 | ○ |
| 案3 | 使用Instead of 触发器 | 使用BEFORE触发器更新 | 使用起来太复杂，不现实 | △ |
使用MERGE语句
MERGE语句
从源表中搜索数据，根据条件不同，可以在一个SQL语句中对对象表执行插入与更新
MERGE INTO 表名1 USING 表名2 ｜副查询 ON ( 结合条件 ) WHEN MATCHED THEN UPDATE SET 列名1 = 值1, 列名2 = 值2, ･･･ WHEN NOT MATCHED THEN INSERT [( 列名1, 列名2, ･･･ )] VALUES ( 值1, 值2, ･･･ );
MERGE语句的注意事项
- 无法成为Fine-grained access control的对象
- 使用INTO语句的话，就可以在更新或者插入对象的表、视图中进行指定。对视图映射数据时，需要更新视图。
- 必须保证ON语句的对象列的一致性
使用MERGE语句 (样本)
[参考] event 10046 的结果
使用了结合视图的BYPASS_UJVC 提示的更新
update /*+ BYPASS_UJVC */ ( select empno,ename,d.deptno,dname,loc from emp e,dept d where e.deptno=d.deptno) set loc = 'BOSTON' where ename = 'SMITH' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 8 3 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.00 0.00 0 8 3 1 Rows Row Source Operation ------- --------------------------------------------------- 0 UPDATE DEPT (cr=8 pr=0 pw=0 time=899 us) 1 NESTED LOOPS (cr=8 pr=0 pw=0 time=366 us) 1 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=306 us) 1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=46 us)(object id 53317)
使用了MERGE语句 的更新
merge into dept d using (select deptno from emp where ename = 'SMITH' ) v on ( v.deptno = d.deptno) when matched then update set loc='BOSTON' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.03 0.03 0 0 0 0 Execute 1 0.00 0.00 0 9 2 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.03 0.03 0 9 2 1 Rows Row Source Operation ------- --------------------------------------------------- 0 MERGE DEPT (cr=9 pr=0 pw=0 time=0 us) 1 VIEW (cr=9 pr=0 pw=0 time=0 us) 1 NESTED LOOPS (cr=9 pr=0 pw=0 time=0 us) 1 NESTED LOOPS (cr=8 pr=0 pw=0 time=0 us cost=5 size=29 card=1) 1 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=4 size=9 card=1) 1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 83537) 1 TABLE ACCESS BY INDEX ROWID DEPT (cr=1 pr=0 pw=0 time=0 us cost=1 size=20 card=1)
所有WHERE EXISTS语句 (样本)
使用了结合视图的BYPASS_UJVC 提示的更新
SQL> update /*+ BYPASS_UJVC */ 2 ( 3 select empno,ename,d.deptno,dname,loc from emp e,dept d 4 where e.deptno=d.deptno) 5 set loc = 'BOSTON' where ename = 'SMITH'; 1 row updated.
通过WHERE EXISTS语句的改写
SQL> update dept d 2 set loc = 'BOSTON' 3 where exists ( 4 select * from emp e 5 where e.deptno=d.deptno and e.ename = 'SMITH') 6 ; 1 row updated.
通过WHERE EXISTS语句进行的改写
update dept d
set loc = 'BOSTON'
where exists (
select * from emp e
where e.deptno=d.deptno and e.ename = 'SMITH')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.05 0 0 0 0
Execute 1 0.00 0.00 0 8 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.05 0.06 0 8 2 1
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE DEPT (cr=8 pr=0 pw=0 time=0 us)
1 NESTED LOOPS (cr=8 pr=0 pw=0 time=0 us cost=6 size=20 card=1)
1 SORT UNIQUE (cr=7 pr=0 pw=0 time=0 us cost=4 size=9 card=1)
1 TABLE ACCESS FULL EMP (cr=7 pr=0 pw=0 time=0 us cost=4 size=9 card=1)
1 INDEX UNIQUE SCAN PK_DEPT (cr=1 pr=0 pw=0 time=0 us cost=0 size=11 card=1)(object id 83537)
其他改写 例
总结
- BYPASS_UJVC提示语句
- BYPASS_UJVC提示是没有被文件化的提示语句。
- 因为存在安全性的风险，所以尽可能不要使用这个来修正应用会比较好。
- 11g无视了这个提示语句，导致了ORA-01779错误发生。
- ORA-1779错误
- 对于不是保存key的表的列进行更新时发生
- 可以利用Real Application Testing (付费Option)进行高效指定对应SQL语句。
- 回避BYPASS_UJVC提示
- 使用MERGE语句
- 使用WHERE EXISTS语句
- (使用Instead of 触发器)
Comment