ORA-00979: not a GROUP BY expression 해소 방안
<<근본해결>>
: GROUP BY 문 사용 시, 그룹 함수 외의 조회 칼럼은 반드시 SELECT 절에 명시해야함
<<WORKAROUND>>
: _FIX_CONTROL 설정을 통해서 해결 가능 ( SYSTEM / SESSION / SQL LEVEL )
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | SYS@TGDB=> select deptno, dname from scott.dept group by deptno ; select deptno, dname from scott.dept group by deptno * ERROR at line 1: ORA-00979: not a GROUP BY expression -- 근복적 해결 SYS@TGDB=> select deptno, dname from scott.dept group by deptno, dname ; DEPTNO DNAME ---------- -------------- 10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES -- _FIX_CONTROL 을 통한 해결 SYS@TGDB=> select /*+ OPT_PARAM('_fix_control' '5520732:OFF') */ deptno, dname from scott.dept group by deptno, dname ; DEPTNO DNAME ---------- -------------- 10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES SYS@TGDB=> | cs |
<< _FIX_CONTROL >>
--> 특정 Bug Fix 적용 여부 결정
--> 주로, 특정 옵티마이져 기능 OFF 시키기
--> SYSTEM, SESSION, SQL LEVEL 설정 가능
--> V$SYSTEM_FIX_CONTROL.VALUE = 0( OFF ), 1( ON )
V$SYSTEM_FIX_CONTROL / V$SESSION_FIX_CONTROL : _FIX_CONTROL 관련 확인 VIEW
--> V$SYSTEM_FIX_CONTROL displays information about Fix Control (enabled/disabled) at the system level.
--> V$SESSION_FIX_CONTROL displays information about Fix Control (enabled/disabled) for the current session.
--> V$SYSTEM_FIX_CONTROL - 11GR2 - 845 , 12CR2 - 1301
-- SYSTEM Level Bug Fix 기능 끄기
SQL> alter system set "_fix_control"="5520732:off" scope=both;
-- SYSTEM Level Bug Fix 기능 켜기
SQL> alter system set "_fix_control"="5520732:on" scope=both;
-- SESSION Level Bug Fix 기능 끄기
alter session set "_fix_control"='5520732:off'
-- SQL Level Bug Fix 기능 끄기
SELECT /*+ OPT_PARAM('_fix_control' '5520732:OFF') */ *
FROM ...;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 | SYS@TGDB=> show parameters _fix_control SYS@TGDB=> set lines 180 pages 1000 SYS@TGDB=> col NAME for a30 SYS@TGDB=> col VALUE for a30 SYS@TGDB=> col DISPLAY_VALUE for a30 SYS@TGDB=> col ISDEFAULT for a10 SYS@TGDB=> COL ISSYS_MODIFIABLE for a20 SYS@TGDB=> SYS@TGDB=> SELECT NAME, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSYS_MODIFIABLE 2 FROM V$PARAMETER 3 WHERE NAME IN ('_fix_control'); no rows selected SYS@TGDB=> alter system set "_fix_control"="5520732:off" scope=both; System altered. SYS@TGDB=> show parameters _fix_control NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ _fix_control string 5520732:off SYS@TGDB=> SELECT NAME, VALUE, DISPLAY_VALUE, ISDEFAULT, ISSYS_MODIFIABLE 2 FROM V$PARAMETER 3 WHERE NAME IN ('_fix_control'); NAME VALUE DISPLAY_VALUE ISDEFAULT ISSYS_MODIFIABLE ------------------------------ ------------------------------ ------------------------------ ---------- -------------------- _fix_control 5520732:off 5520732:off TRUE IMMEDIATE SYS@TGDB=> col value for 999 SYS@TGDB=> set linesize 180 SYS@TGDB=> col description for a40 SYS@TGDB=> col optimizer_faeture_enable for a30 SYS@TGDB=> SYS@TGDB=> select BUGNO, VALUE, DESCRIPTION, OPTIMIZER_FEATURE_ENABLE from v$system_fix_control 2 where bugno=5520732 ; BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE ---------- ----- ---------------------------------------- ------------------------- 5520732 0 light check of gby validity in subquery 11.2.0.1 SYS@TGDB=> select BUGNO, VALUE, DESCRIPTION, OPTIMIZER_FEATURE_ENABLE from v$session_fix_control 2 where bugno = 5520732 ; BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE ---------- ----- ---------------------------------------- ------------------------- 5520732 0 light check of gby validity in subquery 11.2.0.1 SYS@TGDB=> alter session set "_fix_control"='5520732:on'; Session altered. SYS@TGDB=> select BUGNO, VALUE, DESCRIPTION, OPTIMIZER_FEATURE_ENABLE from v$session_fix_control 2 where bugno = 5520732 ; BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE ---------- ----- ---------------------------------------- ------------------------- 5520732 1 light check of gby validity in subquery 11.2.0.1 SYS@TGDB=> alter system set "_fix_control"="5520732:on" scope=both; System altered. SYS@TGDB=> select BUGNO, VALUE, DESCRIPTION, OPTIMIZER_FEATURE_ENABLE from v$system_fix_control 2 where bugno=5520732 ; BUGNO VALUE DESCRIPTION OPTIMIZER_FEATURE_ENABLE ---------- ----- ---------------------------------------- ------------------------- 5520732 1 light check of gby validity in subquery 11.2.0.1 SYS@TGDB=> | cs |