Oracle BYPASS_UJVC HINT提示说明

本文地址:https://www.askmac.cn/archives/bypass_ujvc.html

 

 

  • 本资料中设计到的 BYPASS_UJVC 提示是没有在oracle公司的语句件中记载的提示语语句。
  • 不支持使用BYPASS_UJVC提示,或者不能从安全性的角度来使用。
  • 指定BYPASS_UJVC 提示,发生了不太恰当的操作时,也无法执行修正。
  • 使用BYPASS_UJVC提示时,需要参考本资料,将其变更为不使用提示语语句的处理。
  • 为了删除BYPASS_UJVC提示,需要在实际环境中进行充分的验证。

 

可以更新的结合视图与ORA-1779

[参考] 样本数据

BYPASS_UJVC1

 

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;

 

BYPASS_UJVC2

 

可以更新的结合视图 (Updatable Join View)

BYPASS_UJVC3

 

更新视图时的注意事项

  • 对于视图的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

BYPASS_UJVC4

 

ORA-01779

ORA-01779

[JP] 无法对没有保存key的表的列进行映射。

[EN] cannot modify a column which maps to a non key-preserved table

结合的结果

  • 可以更新保存key的表的列
  • 对于不是保存key的表的列进行更新时,就会发生ORA-01779

BYPASS_UJVC5

 

 

保存key的表 (Key-Preserved Table)

保存key的表是指什么 ….

所有表的key都是结合的结果key,其表就会变成保存key的表换言之,保存key的表就是指结合之后依旧保存了key的表。

 

BYPASS_UJVC6

 

样本 #1

BYPASS_UJVC7

样本 #2

BYPASS_UJVC8

样本 #3

BYPASS_UJVC9

 

样本#4:UPDATE语句内的子查询

BYPASS_UJVC10

 

 

[参考] 样本#4event 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_UJVC11

 

BYPASS_UJVC 提示的利用的悬念点

 

  • 没有document化的提示语句
    • 不支持对象
  • 更新的对象行数不同
    • 基于对象行数的处理中,可能会产生业务逻辑错误
  • 更新了意料之外的数据
    • 由于视图化,可能会更新之前隐藏的数据

 

更新的对象行数不同

BYPASS_UJVC12

 

 

[参考] 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_UJVC13

 

面向废止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 概要

BYPASS_UJVC14

  • 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语句 (样本)

 

BYPASS_UJVC15

 

[参考] 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_UJVC16

总结

  • BYPASS_UJVC提示语句
    • BYPASS_UJVC提示是没有被文件化的提示语句。
    • 因为存在安全性的风险,所以尽可能不要使用这个来修正应用会比较好。
    • 11g无视了这个提示语句,导致了ORA-01779错误发生。
  • ORA-1779错误
    • 对于不是保存key的表的列进行更新时发生
    • 可以利用Real Application Testing (付费Option)进行高效指定对应SQL语句。
  • 回避BYPASS_UJVC提示
    • 使用MERGE语句
    • 使用WHERE EXISTS语句
    • (使用Instead of 触发器)

 




Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号