[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解决,最好还是叫开发改代码..
