[20131221]12c 优化 bug.txt
http://connormcdonald.wordpress.com/2013/12/20/the-challenge-of-optimization/
--仅仅自己重复测试看看!
@ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table T ( seq number primary key, x number, y number , u number);
declare
s number := 1;
begin
for i in 1 .. 9 loop
for j in i+1 .. 10 loop
insert into T values (s,i,j, 0);
s := s + 1;
end loop;
end loop;
end;
/
So we've put 45 rows into the table, the values of which are not particularly important. We are going to compare these
rows with those in another table
create table F ( g number, n number, x number, y number );
insert into F values (1,1,3,4);
insert into F values (1,1,5,7);
insert into F values (1,1,7,8);
commit ;
select * from F;
G N X Y
---------- ---------- ---------- ----------
1 1 3 4
1 1 5 7
1 1 7 8
SCOTT@test01p> select x from F where g = 1 union all select y from F where g = 1;
X
----------
3
5
7
4
7
8
6 rows selected.
select seq,x,y
from T
where u = 0
and x not in ( 3,5,7,4,7,8)
and y not in ( 3,5,7,4,7,8);
SEQ X Y
---------- ---------- ----------
1 1 2
5 1 6
8 1 9
9 1 10
13 2 6
16 2 9
17 2 10
38 6 9
39 6 10
45 9 10
10 rows selected.
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 6zuy1wa9dpa99, child number 0
-------------------------------------
select seq,x,y from T where u = 0 and x not in ( 3,5,7,4,7,8) and y not
in ( 3,5,7,4,7,8)
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| T | 10 | 3 (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("U"=0 AND "X"3 AND "X"5 AND "X"7 AND "X"4 AND
"X"8 AND "Y"3 AND "Y"5 AND "Y"7 AND "Y"4 AND "Y"8))
--执行如下结果应该一样.
select seq,x,y
from T
where u = 0
and x not in ( select x from F where g = 1 union all select y from F where g = 1)
and y not in ( select x from F where g = 1 union all select y from F where g = 1);
SEQ X Y
---------- ---------- ----------
1 1 2
2 1 3
3 1 4
4 1 5
5 1 6
6 1 7
7 1 8
8 1 9
9 1 10
10 2 3
11 2 4
12 2 5
13 2 6
14 2 7
15 2 8
16 2 9
17 2 10
36 6 7
37 6 8
38 6 9
39 6 10
45 9 10
22 rows selected.
--BUG?
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID fawwqt2yn7sb7, child number 0
-------------------------------------
select seq,x,y from T where u = 0 and x not in ( select x from F where
g = 1 union all select y from F where g = 1) and y not in ( select x
from F where g = 1 union all select y from F where g = 1)
Plan hash value: 2519685722
----------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------
| 0 | SELECT STATEMENT | | | 11 (100)|
|* 1 | FILTER | | | |
|* 2 | TABLE ACCESS FULL | T | 45 | 3 (0)|
| 3 | UNION-ALL | | | |
|* 4 | TABLE ACCESS FULL| F | 3 | 3 (0)|
|* 5 | TABLE ACCESS FULL| F | 3 | 3 (0)|
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
2 - filter("U"=0)
4 - filter(("G"=1 AND LNNVL("X":B1)))
5 - filter(("G"=1 AND LNNVL("Y":B1)))
--x,y的not前后顺序对调。
select seq,x,y
from T
where u = 0
and y not in ( select x from F where g = 1 union all select y from F where g = 1)
and x not in ( select x from F where g = 1 union all select y from F where g = 1);
SEQ X Y
---------- ---------- ----------
1 1 2
5 1 6
8 1 9
9 1 10
13 2 6
16 2 9
17 2 10
20 3 6
23 3 9
24 3 10
26 4 6
29 4 9
30 4 10
31 5 6
34 5 9
35 5 10
38 6 9
39 6 10
41 7 9
42 7 10
43 8 9
44 8 10
45 9 10
23 rows selected.
--多了1行,而且结果集合相差很远。
select seq,x,y
from T
where u = 0
and y not in ( select x from F where g = 1 union all select y from F where g = 1)
and x not in ( select x from F where g = 1 union all select y from F where g = 1)
minus
select seq,x,y
from T
where u = 0
and x not in ( select x from F where g = 1 union all select y from F where g = 1)
and y not in ( select x from F where g = 1 union all select y from F where g = 1)
SEQ X Y
---------- ---------- ----------
20 3 6
23 3 9
24 3 10
26 4 6
29 4 9
30 4 10
31 5 6
34 5 9
35 5 10
41 7 9
42 7 10
43 8 9
44 8 10
13 rows selected.
--乱了套了！
--仔细观察：
select seq,x,y
from T
where u = 0
and x not in ( select x from F where g = 1 union all select y from F where g = 1);
SEQ X Y
---------- ---------- ----------
1 1 2
2 1 3
3 1 4
4 1 5
5 1 6
6 1 7
7 1 8
8 1 9
9 1 10
10 2 3
11 2 4
12 2 5
13 2 6
14 2 7
15 2 8
16 2 9
17 2 10
36 6 7
37 6 8
38 6 9
39 6 10
45 9 10
22 rows selected.
--与下面的结果一致。
--select seq,x,y
--from T
--where u = 0
--and x not in ( select x from F where g = 1 union all select y from F where g = 1)
--and y not in ( select x from F where g = 1 union all select y from F where g = 1);
select seq,x,y
from T
where u = 0
and y not in ( select x from F where g = 1 union all select y from F where g = 1);
SEQ X Y
---------- ---------- ----------
1 1 2
5 1 6
8 1 9
9 1 10
13 2 6
16 2 9
17 2 10
20 3 6
23 3 9
24 3 10
26 4 6
29 4 9
30 4 10
31 5 6
34 5 9
35 5 10
38 6 9
39 6 10
41 7 9
42 7 10
43 8 9
44 8 10
45 9 10
23 rows selected.
--与下面的结果一致。
--select seq,x,y
--from T
--where u = 0
--and y not in ( select x from F where g = 1 union all select y from F where g = 1)
--and x not in ( select x from F where g = 1 union all select y from F where g = 1);
select seq,x,y
from T
where u = 0
and x not in ( select x from F where g = 1)
and x not in ( select y from F where g = 1)
and y not in ( select x from F where g = 1)
and y not in ( select y from F where g = 1)
SEQ X Y
---------- ---------- ----------
45 9 10
9 1 10
1 1 2
8 1 9
5 1 6
17 2 10
16 2 9
13 2 6
39 6 10
38 6 9
10 rows selected.
--这回正确了，但是注意输出的seq顺序！
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID 81aq0nf2b49c8, child number 0
-------------------------------------
select seq,x,y from T where u = 0 and x not in ( select x from F where
g = 1) and x not in ( select y from F where g = 1) and y not in (
select x from F where g = 1) and y not in ( select y from F where g = 1)
Plan hash value: 4061876635
---------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 15 (100)| | | |
|* 1 | HASH JOIN ANTI NA | | 45 | 15 (0)| 1696K| 1696K| 1031K (0)|
|* 2 | HASH JOIN ANTI NA | | 45 | 12 (0)| 1696K| 1696K| 1168K (0)|
|* 3 | HASH JOIN ANTI NA | | 45 | 9 (0)| 1696K| 1696K| 925K (0)|
|* 4 | HASH JOIN ANTI NA | | 45 | 6 (0)| 1696K| 1696K| 1063K (0)|
|* 5 | TABLE ACCESS FULL| T | 45 | 3 (0)| | | |
|* 6 | TABLE ACCESS FULL| F | 3 | 3 (0)| | | |
|* 7 | TABLE ACCESS FULL | F | 3 | 3 (0)| | | |
|* 8 | TABLE ACCESS FULL | F | 3 | 3 (0)| | | |
|* 9 | TABLE ACCESS FULL | F | 3 | 3 (0)| | | |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("X"="X")
2 - access("X"="Y")
3 - access("Y"="X")
4 - access("Y"="Y")
5 - filter("U"=0)
6 - filter("G"=1)
7 - filter("G"=1)
8 - filter("G"=1)
9 - filter("G"=1)
--执行计划发生了很大变化！使用hash join.
总结:
--看来oracle的版本XX.1.YY的版本最好不再生产系统使用！
--补充在11GR2下测试:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID fawwqt2yn7sb7, child number 0
-------------------------------------
select seq,x,y from T where u = 0 and x not in ( select x from F where
g = 1 union all select y from F where g = 1) and y not in ( select x
from F where g = 1 union all select y from F where g = 1)
Plan hash value: 2379973572
----------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
----------------------------------------------------------
| 0 | SELECT STATEMENT | | | 20 (100)|
|* 1 | FILTER | | | |
|* 2 | TABLE ACCESS FULL | T | 45 | 3 (0)|
| 3 | UNION-ALL | | | |
|* 4 | TABLE ACCESS FULL| F | 3 | 3 (0)|
|* 5 | TABLE ACCESS FULL| F | 3 | 3 (0)|
| 6 | UNION-ALL | | | |
|* 7 | TABLE ACCESS FULL| F | 3 | 3 (0)|
|* 8 | TABLE ACCESS FULL| F | 3 | 3 (0)|
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(( IS NULL AND IS NULL))
2 - filter("U"=0)
4 - filter(("G"=1 AND LNNVL("X":B1)))
5 - filter(("G"=1 AND LNNVL("Y":B1)))
7 - filter(("G"=1 AND LNNVL("X":B1)))
8 - filter(("G"=1 AND LNNVL("Y":B1)))
--很明显12c缺少
| 6 | UNION-ALL | | | |
|* 7 | TABLE ACCESS FULL| F | 3 | 3 (0)|
|* 8 | TABLE ACCESS FULL| F | 3 | 3 (0)|