[20180928]如何能在11g下執行.txt
[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解決,最好還是叫開發改程式碼..
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2215299/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 不能在simulator執行
- 如何能在執行計劃中看到Starts
- structs1.1以上版本怎麼不能在jboss下執行????Struct
- [20180928]exists與cardinality.txt
- [20180928]避免表示式在sql語句中.txtSQL
- 使用11g dbms_parallel_execute執行並行更新(下)Parallel並行
- 關於dubbo-admin不能在jdk1.8的環境下執行JDK
- 如何在 Java 11 下執行 RocketMQJavaMQ
- [20190531]如何實現與執行.txt
- Llama 3已能在iPhone或安卓手機上執行iPhone安卓
- Linux下如何執行Shell指令碼Linux指令碼
- oracle 11g下如何捕捉library cache物件執行時產生的lock、pin等資訊Oracle物件
- 急!!!!!!!!!jdon的SimpleJdonFrameworkTest例子不能在tomcat+jb下執行,請各位大俠幫忙FrameworkTomcat
- Java下如何保證多執行緒安全Java執行緒
- 透過DNS TXT記錄執行powershellDNS
- 如何破解只能在編譯器的IDE中執行的控制元件 (4千字)編譯IDE控制元件
- 程式設計之路第17天:不能在ui執行緒執行阻塞操作,請使用setTimeout代替????程式設計UI執行緒
- 微軟稱Office 2019將只能在Windows 10上執行微軟Windows
- 不要再寫出不能在 Python 4 中執行的程式了Python
- 如何在批處理模式下執行 top 命令模式
- 實驗-shell執行資料庫命令.TXT資料庫
- [20221018]本地執行與遠端執行.txt
- Uni-App 打包出來的 Apk 不能在模擬器上執行APPAPK
- Android 不能在子執行緒中更新 UI 的討論和分析Android執行緒UI
- 如何讓MFC編寫的應用程式(用VC6.0開發)能在別的電腦上執行
- [20121004]11G下編譯bbed.txt編譯
- 11g 改變SQL執行計劃SQL
- 使用代理上網的情況下,如何在 cmd 下執行 mvn?
- 如何訪問 Mac 下用 HyperKit 執行的 Docker VM?MacDocker
- RxJava 是如何實現執行緒切換的(下)RxJava執行緒
- 配置好JDK後,如何在cmd下執行java程式JDKJava
- 程式執行緒篇——執行緒切換(下)執行緒
- [20161216]toad下顯示真實的執行計劃.txt
- 蘋果推出 M4 AI晶片:大模型很快就能在手機上執行蘋果AI晶片大模型
- 如何執行maven和執行maven的命令。Maven
- [20131017]11G下truncate的新特性.txt
- 使用11g dbms_parallel_execute執行並行更新(上)Parallel並行
- WAMP環境下如何安裝執行多個網站網站