甲骨文：带子句和更新NOWAIT

Question

在一个简单连接中，我想限制第一个表的结果。所以我想这样做:

WITH events  AS (SELECT event FROM risk_event WHERE status = 'ABC' AND rownum <= 20) 
SELECT event_id
        FROM events ev, attributes att
        WHERE ev.event_id = att.risk_event_id
        FOR UPDATE NOWAIT

问题是，由于rownum<=20和FOR UPDATE NOWAIT '，我得到了ORA-02014: cannot select FOR UPDATE from view异常。

我知道我也可以用内部in子句来做，但我想知道是否有更好的方法?

Answer 1

尝试先选择row wid，然后查询从中选择该row wid的表

DDL:

create table risk_event as select level as event, mod(level,20) as status from dual connect by level <=10000;
begin
    dbms_stats.gather_table_stats(user,
                                  'risk_event',
                                  cascade          => true,
                                  estimate_percent => null,
                                  method_opt       => 'for all columns size 1');
  end;
/
create table attributes as select * from risk_event;
begin
    dbms_stats.gather_table_stats(user,
                                  'attributes',
                                  cascade          => true,
                                  estimate_percent => null,
                                  method_opt       => 'for all columns size 1');
  end;
/

代码

WITH events AS (SELECT rowid as rd from risk_event WHERE status = 19 AND rownum <= 20)
SELECT ev.*
        FROM risk_event ev, attributes att
        WHERE ev.event = att.event and ev.rowid in(select rd from events) 
        FOR UPDATE NOWAIT

计划

-----------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Rows  | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |            |     1 |    23 |   11 | 00:00:01 |
|   1 |   FOR UPDATE                     |            |       |       |      |          |
|   2 |    BUFFER SORT                   |            |       |       |      |          |
| * 3 |     HASH JOIN                    |            |     1 |    23 |   11 | 00:00:01 |
|   4 |      NESTED LOOPS                |            |     1 |    19 |    4 | 00:00:01 |
|   5 |       VIEW                       | VW_NSO_1   |    20 |   240 |    2 | 00:00:01 |
|   6 |        SORT UNIQUE               |            |     1 |   240 |      |          |
|   7 |         VIEW                     |            |    20 |   240 |    2 | 00:00:01 |
| * 8 |          COUNT STOPKEY           |            |       |       |      |          |
| * 9 |           TABLE ACCESS FULL      | RISK_EVENT |    20 |   140 |    2 | 00:00:01 |
|  10 |       TABLE ACCESS BY USER ROWID | RISK_EVENT |     1 |     7 |    1 | 00:00:01 |
|  11 |      TABLE ACCESS FULL           | ATTRIBUTES | 10000 | 40000 |    7 | 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("EV"."EVENT"="ATT"."EVENT")
* 8 - filter(ROWNUM<=20)
* 9 - filter("STATUS"=19)