[20180928]如何能在11g下执行.txt

来源：这里教程网时间：2026-03-03 12:02:17 作者：

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