ORA-979 Reported By SQL Tuning Advisor For Query With Both Bind and CASE WHEN on GROUP BY If cursor_sharing=force
(Doc ID 2600419.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 12.2.0.1 and laterInformation in this document applies to any platform.
Symptoms
ORA-979 is reported by SQL Tuning Advisor for following query which is executed with cursor_sharing=force by a user other than SYS.
Sample SQLs:
select case dummy when 'a' then 'a' else dummy end from dual
group by case dummy when 'a' then 'a' else dummy end;
group by case dummy when 'a' then 'a' else dummy end;
or
select decode(dummy,'a', 'a' , dummy ) from dual group by
decode(dummy,'a', 'a' , dummy );
Test log:
SQL> conn t108/t108
Connected.
SQL> alter session set cursor_sharing = force;
Session altered.
SQL> DECLARE
my_task_name VARCHAR2(30);
2 3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select case dummy when ''a'' then ''a'' else dummy end from dual group by case dummy when ''a'' then ''a'' else dummy end ';
6 7 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
task_name => 'MY323');
END;
/ 8 9 10 11
PL/SQL procedure successfully completed.
SQL> Execute dbms_sqltune.Execute_tuning_task (task_name => 'MY323');
PL/SQL procedure successfully completed.
SQL> set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('MY323') from dual;
SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY323')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : MY323
Tuning Task Owner : T108
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 10/15/2019 23:01:09
Completed at : 10/15/2019 23:01:10
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY323')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: T108
SQL ID : djg5uyzs85ygf
SQL Text : select case dummy when 'a' then 'a' else dummy end from dual
group by case dummy when 'a' then 'a' else dummy end
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-00979: not a GROUP BY expression
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY323')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
SQL>
Connected.
SQL> alter session set cursor_sharing = force;
Session altered.
SQL> DECLARE
my_task_name VARCHAR2(30);
2 3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'select case dummy when ''a'' then ''a'' else dummy end from dual group by case dummy when ''a'' then ''a'' else dummy end ';
6 7 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => my_sqltext,
task_name => 'MY323');
END;
/ 8 9 10 11
PL/SQL procedure successfully completed.
SQL> Execute dbms_sqltune.Execute_tuning_task (task_name => 'MY323');
PL/SQL procedure successfully completed.
SQL> set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('MY323') from dual;
SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY323')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : MY323
Tuning Task Owner : T108
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 10/15/2019 23:01:09
Completed at : 10/15/2019 23:01:10
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY323')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: T108
SQL ID : djg5uyzs85ygf
SQL Text : select case dummy when 'a' then 'a' else dummy end from dual
group by case dummy when 'a' then 'a' else dummy end
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-00979: not a GROUP BY expression
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY323')
----------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
SQL>
Changes
Set cursor_sharing=force.
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |