只有20几行的表,笛卡儿积要很久,逻辑读很大,改为物理表就没事了,改为其他写法也没事
SQL> set lines 132 pages 50000
SQL> set autot on
SQL> with t as(select 2*level+1 a from dual connect by level<50),
2 p as (select a from t where a>10
3 minus
4 select t1.a*t2.a from t t1,t t2 )
5 select p.a,p1.a,p2.a from p,p p1,p p2
6 where p.a<p1.a and p1.a<p2.a
7 and (p.a+p1.a)/2 in (select a from p)
8 and (p1.a+p2.a)/2 in (select a from p)
9 and (p.a+p2.a)/2 in (select a from p)
10 and (p.a+p1.a+p2.a)/3 in (select a from p)
11 ;
A A A
----- ----- -----
11 47 71
已用时间: 00: 00: 40.31
执行计划
----------------------------------------------------------
Plan hash value: 3966769004
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 24 (25)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D776C_42BAC073 | | | | |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D776D_42BAC073 | | | | |
| 6 | MINUS | | | | | |
| 7 | SORT UNIQUE | | 1 | 13 | 3 (34)| 00:00:01 |
|* 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776C_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | SORT UNIQUE | | 1 | 26 | 5 (20)| 00:00:01 |
| 11 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 12 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776C_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 15 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776C_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 91 | 14 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 78 | 12 (0)| 00:00:01 |
| 19 | MERGE JOIN CARTESIAN | | 1 | 65 | 10 (0)| 00:00:01 |
| 20 | MERGE JOIN CARTESIAN | | 1 | 52 | 8 (0)| 00:00:01 |
| 21 | MERGE JOIN CARTESIAN | | 1 | 39 | 6 (0)| 00:00:01 |
| 22 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 23 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 25 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 26 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 28 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 29 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 31 | BUFFER SORT | | 1 | 13 | 6 (0)| 00:00:01 |
| 32 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 34 | BUFFER SORT | | 1 | 13 | 8 (0)| 00:00:01 |
| 35 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 36 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
|* 37 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
|* 39 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(LEVEL<50)
8 - filter("A">10)
37 - filter("P"."A"<"P1"."A" AND "A"=("P"."A"+"P1"."A")/2)
39 - filter("P1"."A"<"P2"."A" AND "A"=("P1"."A"+"P2"."A")/2 AND "A"=("P"."A"+"P2"."A")/2 AND
"A"=("P"."A"+"P1"."A"+"P2"."A")/3)
统计信息
----------------------------------------------------------
96 recursive calls
16 db block gets
8668377 consistent gets
2 physical reads
1704 redo size
652 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> with t as(select 2*level+1 a from dual connect by level<50),
2 p as (select a from t where a>10
3 minus
4 select t1.a*t2.a from t t1,t t2 ),
5 p2 as (select p.a,p1.a b from p,p p1 where p.a<p1.a and (p.a+p1.a)/2 in (select a from p))
6 select a.a,a.b,b.b from p2 a,p2 b where a.b=b.a and (a.a+a.b+b.b)/3 in (select a from p)
7 and (a.a,b.b) in (select a,b from p2);
A B B
----- ----- -----
11 47 71
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 2207517943
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 25 (28)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D7771_42BAC073 | | | | |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D7772_42BAC073 | | | | |
| 6 | MINUS | | | | | |
| 7 | SORT UNIQUE | | 1 | 13 | 3 (34)| 00:00:01 |
|* 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7771_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | SORT UNIQUE | | 1 | 26 | 5 (20)| 00:00:01 |
| 11 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 12 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7771_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 15 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7771_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 17 | LOAD AS SELECT | SYS_TEMP_0FD9D7773_42BAC073 | | | | |
| 18 | NESTED LOOPS | | 1 | 39 | 6 (0)| 00:00:01 |
| 19 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 20 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7772_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 22 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 23 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7772_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
|* 25 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7772_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
|* 27 | HASH JOIN SEMI | | 1 | 91 | 9 (12)| 00:00:01 |
|* 28 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
| 29 | MERGE JOIN CARTESIAN | | 1 | 39 | 4 (0)| 00:00:01 |
| 30 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7772_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 32 | BUFFER SORT | | 1 | 26 | 4 (0)| 00:00:01 |
| 33 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7773_42BAC073 | 1 | 26 | 2 (0)| 00:00:01 |
| 35 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 36 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7773_42BAC073 | 1 | 26 | 2 (0)| 00:00:01 |
| 37 | VIEW | VW_NSO_1 | 1 | 26 | 2 (0)| 00:00:01 |
| 38 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 39 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7773_42BAC073 | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(LEVEL<50)
8 - filter("A">10)
25 - filter("P"."A"<"P1"."A" AND "A"=("P"."A"+"P1"."A")/2)
27 - access("A"."A"="A" AND "B"."B"="B")
28 - access("A"."B"="B"."A")
filter("A"=("A"."A"+"A"."B"+"B"."B")/3)
统计信息
----------------------------------------------------------
140 recursive calls
25 db block gets
997 consistent gets
3 physical reads
2468 redo size
652 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> create table t as select 2*level+1 a from dual connect by level<50;
表已创建。
已用时间: 00: 00: 00.09
SQL> with p as (select a from t where a>10
2 minus
3 select t1.a*t2.a from t t1,t t2 )
4 select p.a,p1.a,p2.a from p,p p1,p p2
5 where p.a<p1.a and p1.a<p2.a
6 and (p.a+p1.a)/2 in (select a from p)
7 and (p1.a+p2.a)/2 in (select a from p)
8 and (p.a+p2.a)/2 in (select a from p)
9 and (p.a+p1.a+p2.a)/3 in (select a from p)
10 ;
A A A
----- ----- -----
11 47 71
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 1234059685
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 819 | 82 (80)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D7775_42BAC073 | | | | |
| 3 | MINUS | | | | | |
| 4 | SORT UNIQUE | | 45 | 585 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T | 45 | 585 | 3 (0)| 00:00:01 |
| 6 | SORT UNIQUE | | 2401 | 62426 | 59 (2)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 2401 | 62426 | 58 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T | 49 | 637 | 3 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 49 | 637 | 55 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | T | 49 | 637 | 1 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 9 | 819 | 19 (27)| 00:00:01 |
|* 12 | HASH JOIN | | 21 | 1638 | 17 (30)| 00:00:01 |
|* 13 | HASH JOIN | | 46 | 2990 | 14 (29)| 00:00:01 |
|* 14 | HASH JOIN | | 103 | 5356 | 12 (34)| 00:00:01 |
| 15 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
| 17 | MERGE JOIN | | 228 | 8892 | 9 (34)| 00:00:01 |
| 18 | SORT JOIN | | 101 | 2626 | 6 (34)| 00:00:01 |
| 19 | MERGE JOIN | | 101 | 2626 | 6 (34)| 00:00:01 |
| 20 | SORT JOIN | | 45 | 585 | 3 (34)| 00:00:01 |
| 21 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL| SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
|* 23 | SORT JOIN | | 45 | 585 | 3 (34)| 00:00:01 |
| 24 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL| SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
|* 26 | SORT JOIN | | 45 | 585 | 3 (34)| 00:00:01 |
| 27 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
| 29 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
| 31 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
| 33 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("A">10)
11 - access("A"=("P"."A"+"P1"."A"+"P2"."A")/3)
12 - access("A"=("P"."A"+"P2"."A")/2)
13 - access("A"=("P"."A"+"P1"."A")/2)
14 - access("A"=("P1"."A"+"P2"."A")/2)
23 - access("P1"."A"<"P2"."A")
filter("P1"."A"<"P2"."A")
26 - access(INTERNAL_FUNCTION("P"."A")<INTERNAL_FUNCTION("P1"."A"))
filter(INTERNAL_FUNCTION("P"."A")<INTERNAL_FUNCTION("P1"."A"))
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
75 recursive calls
8 db block gets
82 consistent gets
2 physical reads
852 redo size
652 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set lines 132 pages 50000
SQL> set autot on
SQL> with t as(select 2*level+1 a from dual connect by level<50),
2 p as (select a from t where a>10
3 minus
4 select t1.a*t2.a from t t1,t t2 )
5 select p.a,p1.a,p2.a from p,p p1,p p2
6 where p.a<p1.a and p1.a<p2.a
7 and (p.a+p1.a)/2 in (select a from p)
8 and (p1.a+p2.a)/2 in (select a from p)
9 and (p.a+p2.a)/2 in (select a from p)
10 and (p.a+p1.a+p2.a)/3 in (select a from p)
11 ;
A A A
----- ----- -----
11 47 71
已用时间: 00: 00: 40.31
执行计划
----------------------------------------------------------
Plan hash value: 3966769004
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 24 (25)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D776C_42BAC073 | | | | |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D776D_42BAC073 | | | | |
| 6 | MINUS | | | | | |
| 7 | SORT UNIQUE | | 1 | 13 | 3 (34)| 00:00:01 |
|* 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776C_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | SORT UNIQUE | | 1 | 26 | 5 (20)| 00:00:01 |
| 11 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 12 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776C_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 15 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776C_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 91 | 14 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 78 | 12 (0)| 00:00:01 |
| 19 | MERGE JOIN CARTESIAN | | 1 | 65 | 10 (0)| 00:00:01 |
| 20 | MERGE JOIN CARTESIAN | | 1 | 52 | 8 (0)| 00:00:01 |
| 21 | MERGE JOIN CARTESIAN | | 1 | 39 | 6 (0)| 00:00:01 |
| 22 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 23 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 25 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 26 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 28 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 29 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 31 | BUFFER SORT | | 1 | 13 | 6 (0)| 00:00:01 |
| 32 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 33 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 34 | BUFFER SORT | | 1 | 13 | 8 (0)| 00:00:01 |
| 35 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 36 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
|* 37 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
|* 39 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D776D_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(LEVEL<50)
8 - filter("A">10)
37 - filter("P"."A"<"P1"."A" AND "A"=("P"."A"+"P1"."A")/2)
39 - filter("P1"."A"<"P2"."A" AND "A"=("P1"."A"+"P2"."A")/2 AND "A"=("P"."A"+"P2"."A")/2 AND
"A"=("P"."A"+"P1"."A"+"P2"."A")/3)
统计信息
----------------------------------------------------------
96 recursive calls
16 db block gets
8668377 consistent gets
2 physical reads
1704 redo size
652 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> with t as(select 2*level+1 a from dual connect by level<50),
2 p as (select a from t where a>10
3 minus
4 select t1.a*t2.a from t t1,t t2 ),
5 p2 as (select p.a,p1.a b from p,p p1 where p.a<p1.a and (p.a+p1.a)/2 in (select a from p))
6 select a.a,a.b,b.b from p2 a,p2 b where a.b=b.a and (a.a+a.b+b.b)/3 in (select a from p)
7 and (a.a,b.b) in (select a,b from p2);
A B B
----- ----- -----
11 47 71
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 2207517943
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 91 | 25 (28)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D7771_42BAC073 | | | | |
|* 3 | CONNECT BY WITHOUT FILTERING| | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 5 | LOAD AS SELECT | SYS_TEMP_0FD9D7772_42BAC073 | | | | |
| 6 | MINUS | | | | | |
| 7 | SORT UNIQUE | | 1 | 13 | 3 (34)| 00:00:01 |
|* 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7771_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 10 | SORT UNIQUE | | 1 | 26 | 5 (20)| 00:00:01 |
| 11 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 12 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7771_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 14 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 15 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7771_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 17 | LOAD AS SELECT | SYS_TEMP_0FD9D7773_42BAC073 | | | | |
| 18 | NESTED LOOPS | | 1 | 39 | 6 (0)| 00:00:01 |
| 19 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 |
| 20 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7772_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 22 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 |
| 23 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7772_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
|* 25 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 26 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7772_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
|* 27 | HASH JOIN SEMI | | 1 | 91 | 9 (12)| 00:00:01 |
|* 28 | HASH JOIN | | 1 | 65 | 7 (15)| 00:00:01 |
| 29 | MERGE JOIN CARTESIAN | | 1 | 39 | 4 (0)| 00:00:01 |
| 30 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7772_42BAC073 | 1 | 13 | 2 (0)| 00:00:01 |
| 32 | BUFFER SORT | | 1 | 26 | 4 (0)| 00:00:01 |
| 33 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7773_42BAC073 | 1 | 26 | 2 (0)| 00:00:01 |
| 35 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 36 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7773_42BAC073 | 1 | 26 | 2 (0)| 00:00:01 |
| 37 | VIEW | VW_NSO_1 | 1 | 26 | 2 (0)| 00:00:01 |
| 38 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 39 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7773_42BAC073 | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(LEVEL<50)
8 - filter("A">10)
25 - filter("P"."A"<"P1"."A" AND "A"=("P"."A"+"P1"."A")/2)
27 - access("A"."A"="A" AND "B"."B"="B")
28 - access("A"."B"="B"."A")
filter("A"=("A"."A"+"A"."B"+"B"."B")/3)
统计信息
----------------------------------------------------------
140 recursive calls
25 db block gets
997 consistent gets
3 physical reads
2468 redo size
652 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> create table t as select 2*level+1 a from dual connect by level<50;
表已创建。
已用时间: 00: 00: 00.09
SQL> with p as (select a from t where a>10
2 minus
3 select t1.a*t2.a from t t1,t t2 )
4 select p.a,p1.a,p2.a from p,p p1,p p2
5 where p.a<p1.a and p1.a<p2.a
6 and (p.a+p1.a)/2 in (select a from p)
7 and (p1.a+p2.a)/2 in (select a from p)
8 and (p.a+p2.a)/2 in (select a from p)
9 and (p.a+p1.a+p2.a)/3 in (select a from p)
10 ;
A A A
----- ----- -----
11 47 71
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 1234059685
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 819 | 82 (80)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D7775_42BAC073 | | | | |
| 3 | MINUS | | | | | |
| 4 | SORT UNIQUE | | 45 | 585 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T | 45 | 585 | 3 (0)| 00:00:01 |
| 6 | SORT UNIQUE | | 2401 | 62426 | 59 (2)| 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 2401 | 62426 | 58 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | T | 49 | 637 | 3 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 49 | 637 | 55 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | T | 49 | 637 | 1 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 9 | 819 | 19 (27)| 00:00:01 |
|* 12 | HASH JOIN | | 21 | 1638 | 17 (30)| 00:00:01 |
|* 13 | HASH JOIN | | 46 | 2990 | 14 (29)| 00:00:01 |
|* 14 | HASH JOIN | | 103 | 5356 | 12 (34)| 00:00:01 |
| 15 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
| 17 | MERGE JOIN | | 228 | 8892 | 9 (34)| 00:00:01 |
| 18 | SORT JOIN | | 101 | 2626 | 6 (34)| 00:00:01 |
| 19 | MERGE JOIN | | 101 | 2626 | 6 (34)| 00:00:01 |
| 20 | SORT JOIN | | 45 | 585 | 3 (34)| 00:00:01 |
| 21 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL| SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
|* 23 | SORT JOIN | | 45 | 585 | 3 (34)| 00:00:01 |
| 24 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 25 | TABLE ACCESS FULL| SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
|* 26 | SORT JOIN | | 45 | 585 | 3 (34)| 00:00:01 |
| 27 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
| 29 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
| 31 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
| 33 | VIEW | | 45 | 585 | 2 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7775_42BAC073 | 45 | 585 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("A">10)
11 - access("A"=("P"."A"+"P1"."A"+"P2"."A")/3)
12 - access("A"=("P"."A"+"P2"."A")/2)
13 - access("A"=("P"."A"+"P1"."A")/2)
14 - access("A"=("P1"."A"+"P2"."A")/2)
23 - access("P1"."A"<"P2"."A")
filter("P1"."A"<"P2"."A")
26 - access(INTERNAL_FUNCTION("P"."A")<INTERNAL_FUNCTION("P1"."A"))
filter(INTERNAL_FUNCTION("P"."A")<INTERNAL_FUNCTION("P1"."A"))
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
75 recursive calls
8 db block gets
82 consistent gets
2 physical reads
852 redo size
652 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed