1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
|
SQL>
SQL> drop table f1;
Table dropped.
SQL> drop table f2;
Table dropped.
SQL> drop table f3;
Table dropped.
SQL> drop sequence s1;
Sequence dropped.
SQL> drop sequence s2;
Sequence dropped.
SQL> drop sequence s3;
Sequence dropped.
SQL>
SQL> whenever sqlerror exit failure;
SQL>
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for 32-bit Windows: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
SQL>
SQL> create sequence s1;
Sequence created.
SQL> create sequence s2;
Sequence created.
SQL> create sequence s3;
Sequence created.
SQL>
SQL> create table f1(x char, y int);
Table created.
SQL> insert into f1 select '1', rownum from audit_actions;
144 rows created.
SQL> create table f2(x char, y int);
Table created.
SQL> insert into f2 select '2', rownum from audit_actions;
144 rows created.
SQL> create table f3(x char, y int);
Table created.
SQL> insert into f3 select '3', rownum from audit_actions;
144 rows created.
SQL> commit;
Commit complete.
SQL>
SQL> create or replace view v123 as
2 select 1 as tn, f1.* from f1
3 union all
4 select 2 as tn, f2.* from f2
5 union all
6 select 3 as tn, f3.* from f3;
View created.
SQL> set autotrace traceonly;
SQL> select * from v123 where tn = 1;
144 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW OF 'V123'
2 1 UNION-ALL
3 2 TABLE ACCESS (FULL) OF 'F1'
4 2 FILTER
5 4 TABLE ACCESS (FULL) OF 'F2'
6 2 FILTER
7 6 TABLE ACCESS (FULL) OF 'F3'
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
2807 bytes sent via SQL*Net to client
598 bytes received via SQL*Net from client
11 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
144 rows processed
SQL>
SQL> create or replace view vs123 as
2 select 1 as tn, s1.nextval as seq, f1.* from f1
3 union all
4 select 2 as tn, s2.nextval as seq, f2.* from f2
5 union all
6 select 3 as tn, s3.nextval as seq, f3.* from f3
7 ;
select 1 as tn, s1.nextval as seq, f1.* from f1
*
ERROR at line 2:
ORA-02287: sequence number not allowed here |
Partager