[20180928]如何能在11g下执行.txt
–//链接问的问题: http://www.itpub.net/thread-2105467-1-1.html
create table test(t_id int,t_name varchar2(50));
create table test2(t_id int,t_name varchar2(50));
insert into test values(1,’a’);
insert into test values(2,’b’);
insert into test values(3,’c’);
insert into test2 values(1,’a’);
insert into test2 values(2,’b’);
insert into test2 values(3,’c’);
select a.t_id, a.t_name
from test a
left join ( select t_id, t_name
from test2
group by t_name) b
on a.t_id = b.t_id and a.t_name = b.t_name
where a.t_id = 1
group by a.t_id, a.t_name;
–//同样的sql ，在10g下就能执行，在11g下就会报错 ora-00979 不是group by 表达式。红色部分看起来确实是错的，可是10g下整句
–//执行就有结果。如何能做到这个语句再11g下也能执行呢。
–//家里没有10g,使用12c测试看看,这条语句明显错误( select t_id, t_name from test2 group by t_name),这里少写了 group by
–//t_id,t_name.
1.环境:
scott@test01p> @ ver1
port_string version banner con_id
—————————— ————– ——————————————————————————– ———-
ibmpc/win_nt64-9.1.0 12.1.0.1.0 oracle database 12c enterprise edition release 12.1.0.1.0 – 64bit production 0
2.测试:
scott@test01p> select a.t_id,a.t_name from test a left join
2 (select t_id,t_name from test2 group by t_name) b
3 on a.t_id=b.t_id and a.t_name=b.t_name where a.t_id=1
4 group by a.t_id,a.t_name;
(select t_id,t_name from test2 group by t_name) b
*
error at line 2:
ora-00979: not a group by expression
–//12c 一样报错.
scott@test01p> show parameter feature
name type value
———————————— ——————– —————
optimizer_adaptive_features boolean true
optimizer_features_enable string 12.1.0.1
scott@test01p> alter session set optimizer_features_enable=’10.2.0.1′;
session altered.
scott@test01p> select a.t_id, a.t_name
2 from test a
3 left join ( select t_id, t_name
4 from test2
5 group by t_name) b
6 on a.t_id = b.t_id and a.t_name = b.t_name
7 where a.t_id = 1
8 group by a.t_id, a.t_name;
t_id t_name
———- ————————————————–
1 a
–//ok通过.不过这句话明显错误.看看执行计划:
scott@test01p> @ dpc ” advanced
plan_table_output
————————————-
sql_id 0urvg3qakxxcx, child number 0
————————————-
select a.t_id, a.t_name from test a left join ( select
t_id, t_name from test2
group by t_name) b on a.t_id = b.t_id and a.t_name =
b.t_name where a.t_id = 1 group by a.t_id, a.t_name
plan hash value: 1211648783
———————————————————————————————————–
| id | operation | name | e-rows |e-bytes| cost (%cpu)| e-time | omem | 1mem | used-mem |
———————————————————————————————————–
| 0 | select statement | | | | 9 (100)| | | | |
| 1 | hash group by | | 1 | 40 | 9 (34)| 00:00:01 | 2063k| 2063k| 438k (0)|
| 2 | view | | 1 | 40 | 8 (25)| 00:00:01 | | | |
| 3 | hash group by | | 1 | 92 | 8 (25)| 00:00:01 | 1345k| 1345k| 486k (0)|
|* 4 | hash join outer | | 1 | 92 | 7 (15)| 00:00:01 | 1421k| 1421k| 722k (0)|
|* 5 | table access full| test | 1 | 52 | 3 (0)| 00:00:01 | | | |
|* 6 | table access full| test2 | 1 | 40 | 3 (0)| 00:00:01 | | | |
———————————————————————————————————–
query block name / object alias (identified by operation id):
————————————————————-
1 – sel$439afb4f
2 – sel$3dd9cb74 / $vm_view_0@sel$439afb4f
3 – sel$3dd9cb74
5 – sel$3dd9cb74 / a@sel$3
6 – sel$3dd9cb74 / test2@sel$2
outline data
————-
/*+
begin_outline_data
ignore_optim_embedded_hints
optimizer_features_enable(‘10.2.0.1’)
db_version(‘12.1.0.1’)
all_rows
outline_leaf(@”sel$3dd9cb74″)
merge(@”sel$2″)
outline_leaf(@”sel$439afb4f”)
outline(@”sel$f2b2f603″)
outline(@”sel$2″)
outline(@”sel$cd8351fa”)
merge(@”sel$f1d6e378″)
outline(@”sel$4″)
outline(@”sel$f1d6e378″)
merge(@”sel$1″)
outline(@”sel$3″)
outline(@”sel$1″)
no_access(@”sel$439afb4f” “$vm_view_0″@”sel$439afb4f”)
use_hash_aggregation(@”sel$439afb4f”)
full(@”sel$3dd9cb74″ “a”@”sel$3”)
full(@”sel$3dd9cb74″ “test2″@”sel$2”)
leading(@”sel$3dd9cb74″ “a”@”sel$3” “test2″@”sel$2”)
use_hash(@”sel$3dd9cb74″ “test2″@”sel$2”)
use_hash_aggregation(@”sel$3dd9cb74″)
end_outline_data
*/
predicate information (identified by operation id):
—————————————————
4 – access(“a”.”t_name”=”t_name” and “a”.”t_id”=”t_id”)
5 – filter(“a”.”t_id”=1)
6 – filter(“t_id”=1)
–//抽取outline.
–//编辑整理如下:
begin
dbms_sqltune.import_sql_profile(
name => ‘profile_group_error’,
description => ‘sql profile created manually test’,
sql_text => q'[select a.t_id, a.t_name
from test a
left join ( select t_id, t_name
from test2
group by t_name) b
on a.t_id = b.t_id and a.t_name = b.t_name
where a.t_id = 1
group by a.t_id, a.t_name]’,
profile => sqlprof_attr(
‘ignore_optim_embedded_hints’,
q'[optimizer_features_enable(‘10.2.0.1′)]’,
q'[db_version(‘12.1.0.1′)]’,
‘all_rows’,
‘outline_leaf(@”sel$3dd9cb74″)’,
‘merge(@”sel$2″)’,
‘outline_leaf(@”sel$439afb4f”)’,
‘outline(@”sel$f2b2f603″)’,
‘outline(@”sel$2″)’,
‘outline(@”sel$cd8351fa”)’,
‘merge(@”sel$f1d6e378″)’,
‘outline(@”sel$4″)’,
‘outline(@”sel$f1d6e378″)’,
‘merge(@”sel$1″)’,
‘outline(@”sel$3″)’,
‘outline(@”sel$1″)’,
‘no_access(@”sel$439afb4f” “$vm_view_0″@”sel$439afb4f”)’,
‘use_hash_aggregation(@”sel$439afb4f”)’,
‘full(@”sel$3dd9cb74″ “a”@”sel$3”)’,
‘full(@”sel$3dd9cb74″ “test2″@”sel$2”)’,
‘leading(@”sel$3dd9cb74″ “a”@”sel$3” “test2″@”sel$2”)’,
‘use_hash(@”sel$3dd9cb74″ “test2″@”sel$2”)’,
‘use_hash_aggregation(@”sel$3dd9cb74″)’
),
replace => true,
force_match => true
);
end;
/
scott@test01p> show parameter feature
name type value
———————————— ——————– ————–
optimizer_adaptive_features boolean true
optimizer_features_enable string 12.1.0.1
scott@test01p> select a.t_id, a.t_name
2 from test a
3 left join ( select t_id, t_name
4 from test2
5 group by t_name) b
6 on a.t_id = b.t_id and a.t_name = b.t_name
7 where a.t_id = 1
8 group by a.t_id, a.t_name;
t_id t_name
———- ————————————————–
1 a
–//换1个数值,select变成select看看.
scott@test01p> select a.t_id, a.t_name
2 from test a
3 left join ( select t_id, t_name
4 from test2
5 group by t_name) b
6 on a.t_id = b.t_id and a.t_name = b.t_name
7 where a.t_id = 2
8 group by a.t_id, a.t_name;
t_id t_name
———- ————————————————–
2 b
–//ok解决,最好还是叫开发改代码..