ORA-00979: not a GROUP BY expression

Yesterday I found something interesting about the GROUP BY clause. The model (borrowed from Jonathan Lewis as always) is

mhouri.world> create table t1
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(1,1000))    n1,
 12      lpad(rownum,10,'0') vc2,
 13      rpad('x',100)       vc3
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;

Table created.

mhouri.world> create index t1_n1 on t1(id, n1);
Index created.

mhouri.world> create table t2
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(10001,20001))   x1,
 12      lpad(rownum,10,'0') vc2,
 13      rpad('x',100)       vc3
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;

Table created.

mhouri.world> create index t2_i1 on t2(x1);

Index created.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');

PL/SQL procedure successfully completed.

mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              )
 11        ) as alias1
 12  from   t1
 13   where t1.id   = 11
 14   and   t1.n1   = 707
 15   group by
 16         t1.id
 17        ;
            and   t2.x1  = t1.n1
                           *
ERROR at line 9:
ORA-00979: not a GROUP BY expression 

Why do I need to group by t1.n1 also?

mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              )
 11        ) as alias1
 12  from   t1
 13   where t1.id   = 11
 14   and   t1.n1   = 707
 15   group by
 16         t1.id
 17        ,t1.n1
 18        ;

        ID SUM(T1.N1)     ALIAS1                                                
---------- ---------- ----------                                                
        11        707                                                           

If I slightly change the above query by adding an extra where clause in the sub select (and t2.vc2 = t1.vc2)

mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              and   t2.vc2 = t1.vc2
 11              )
 12        ) as alias1
 13  from   t1
 14   where t1.id   = 11
 15   and   t1.n1   = 707
 16   group by
 17         t1.id
 18        ,t1.n1
 19        ;
            and   t2.vc2 = t1.vc2
                           *
ERROR at line 10:
ORA-00979: not a GROUP BY expression 

The same error is raised again meaning that I need to group by this extra column (vc2) added into the where clause

mhouri.world> select
  2       t1.id
  3      ,sum(t1.n1)
  4      ,(sum(t1.n1) +
  5             (select
  6                   sum(t2.x1) x
  7              from  t2
  8              where t2.id  = t1.id
  9              and   t2.x1  = t1.n1
 10              and   t2.vc2 = t1.vc2
 11              )
 12        ) as alias1
 13  from   t1
 14   where t1.id   = 11
 15   and   t1.n1   = 707
 16   group by
 17         t1.id
 18        ,t1.n1
 19        ,t1.vc2
 20        ;

        ID SUM(T1.N1)     ALIAS1                                                
---------- ---------- ----------                                                
        11        707                                                           

And so on. Each time a column from table t1 (other than id1) is added to the sub select we need to group by that column

Any explanation?

May be when observing the execution plan

mhouri.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------

SQL_ID  5mzq0bmusjvtg, child number 0
-------------------------------------
select      t1.id     ,sum(t1.n1)     ,(sum(t1.n1) +            (select
           sum(t2.x1) x             from  t2             where t2.id  = t1.id
            and   t2.x1  = t1.n1             )       ) as alias1 from   t1
where t1.id   = 11  and   t1.n1   = 707  group by        t1.id       ,t1.n1

Plan hash value: 508850092

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE              |       |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   103   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |
|   4 |  SORT GROUP BY NOSORT        |       |     1 |     9 |     3   (0)| 00:00:01 |
|*  5 |   INDEX RANGE SCAN           | T1_N1 |     1 |     9 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T2"."ID"=:B1)
   3 - access("T2"."X1"=:B1)
   5 - access("T1"."ID"=11 AND "T1"."N1"=707)
                                                                                

…..

Auditing Updates and Deletes

I found myself very often answering question like “I want to track any update or delete done on my table. I want to store the complete record before modification and want also to know who modified this record, when it has been modified and by what program”. I decided finally to write this blog article in order to use it as an answer to this kind of question.

So let’s say we have an employee table defined as follows

mhouri.world> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

When I want to audit modifications on this table, the first thing to do is to create a resembling historical table

mhouri.world> create table emp_history as select * from emp where 1 = 2;

Table created.

mhouri.world> alter table emp_history add( dml_usr varchar2(48)
  2                                       ,dml_pgm  varchar2(48)
  3                                       ,dml_dat  date
  4                                       ,dml_operation varchar2(1)
  5                                     );

Table altered.

Then I will create a special trigger on this historical table

mhouri.world> create or replace trigger emp_his_b_i_r_trg
  2  before insert on emp_history
  3  for each row
  4  declare
  5     lv_module VARCHAR2(48);
  6     lv_action VARCHAR2(48);
  7     ld_current_date DATE := SYSDATE;
  8     lv_current_user VARCHAR2(48) := USER;
  9  begin
 10     dbms_application_info.read_module(lv_module, lv_action);
 11  
 12     :new.dml_usr  := lv_current_user;
 13     :new.dml_dat  := ld_current_date;
 14     :new.dml_pgm  := lv_module;
 15  end emp_his_b_i_r_trg;
 16  /

Trigger created.

And finally, I will create a trigger on the target table i.e. the table I want to audit

mhouri.world> create or replace trigger emp_b_u_d_r_trg
  2  before update or delete on emp
  3  for each row
  4  declare
  5   lv_dml_operation emp_history.dml_operation%type;
  6  begin
  7  if updating then
  8    lv_dml_operation := 'U';
  9  elsif deleting then
 10    lv_dml_operation := 'D';
 11  end if;
 12  
 13     insert into emp_history(empno
 14                            ,ename
 15                            ,job
 16                            ,mgr
 17                            ,hiredate
 18                            ,sal
 19                            ,comm
 20                            ,deptno
 21                            ,dml_operation
 22                            )
 23                      values(:old.empno
 24                            ,:old.ename
 25                            ,:old.job
 26                            ,:old.mgr
 27                            ,:old.hiredate
 28                            ,:old.sal
 29                            ,:old.comm
 30                            ,:old.deptno
 31                            ,lv_dml_operation
 32                            );
 33  end emp_b_u_d_r_trg;
 34  /

Trigger created.

Ok, we are done now. Our new historical table is empty for the moment

mhouri.world> select * from emp_history;
no rows selected

Let’s update and delete from target table

mhouri.world> update emp set sal = 4500 where empno = 7369;
1 row updated.                                                    
mhouri.world> delete from emp where empno = 9999;
1 row deleted.
mhouri.world> commit;
Commit complete.

What will look like our emp_history table?

d102@o102d00d.world> select  empno
  2        , ename
  3        , job
  4        , sal
  5        , substr(dml_usr,1,10) usr
  6        , substr(dml_pgm,1,10) pgm
  7        , substr(dml_dat,1,12) dat
  8        , dml_operation        dml
  9  from emp_history
 10  ;

     EMPNO ENAME      JOB              SAL USR        PGM        DAT       D
---------- ---------- --------- ---------- ---------- ---------- --------- -
      7369 allen      clerk             35 SX2_Z      SQL*Plus   16-MAR-12 U
      9999 Miloud     Expert          5600 SX2_Z      SQL*Plus   16-MAR-12 D

Where we can see that we have already done two DML operations on our emp table an update followed by a delete. For that we used SQL*Plus and the user who did the operation was SX2_Z.

ORDER BY and FIRST_ROWS

Recently an interesting question came up on a French oracle forum about a costly order by operation. The original poster said that he has a query like this:

select *
from t1 where id in (select id from t2 where x1 = :b1) ;

which is returning 39 records in a very acceptable response time. However, when he adds a simple order by to that query, the response time become very nasty and not acceptable at all. When I asked about the value of the optimizer mode he is using, I was not really surprised when he answered “I am running under FIRST_ROWS mode”.   The aim of this blog is exactly to explain the nasty side effect the FIRST_ROWS optimizer can produce particularly in presence of an order by clause.

The model (borrowed from Jonathan Lewis) is:

mhouri.world> create table t1
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(1,1000))    n1,
 12      lpad(rownum,10,'0') small_vc,
 13      rpad('x',100)       padding
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;
Table created.

mhouri.world> create index t1_n1 on t1(id, n1);
Index created.

mhouri.world> create table t2
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          rownum id
  6      from dual
  7      connect by
  8          level <= 10000)
  9  select
 10      rownum                  id,
 11      trunc(dbms_random.value(10001,20001))   x1,
 12      lpad(rownum,10,'0') small_vc,
 13      rpad('x',100)       padding
 14  from
 15      generator   v1,
 16      generator   v2
 17  where
 18      rownum <= 1000000;
Table created.

mhouri.world> create index t2_i1 on t2(x1);
Index created.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't2', method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

mhouri.world> exec dbms_stats.gather_table_stats(user, 't1', method_opt => 'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.

mhouri.world> show parameter optimiz%
NAME                                 TYPE        VALUE                                                                 
------------------------------------ ----------- ------------------------                
optimizer_mode                       string      ALL_ROWS                  
                                             

mhouri.world> select *
  2  from t1 where id in (select id from t2 where x1 = 17335)
  3  order by id
  4  ;

107 rows selected.

mhouri.world> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT                                                                                                      
-----------------------------------------------------------------------------------------
SQL_ID  d2wym19g2n196, child number 1                                                                                  
-------------------------------------                                                                                  
select * from t1 where id in (select id from t2 where x1 = 17335) order by id                                          
Plan hash value: 153456901                                                                                             
-----------------------------------------------------------------------------------------                              
| Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                              
-----------------------------------------------------------------------------------------                              
|   0 | SELECT STATEMENT                |       |       |       |   255 (100)|          |                              
|   1 |  SORT ORDER BY                  |       |   100 | 13000 |   255   (1)| 00:00:02 |                              
|   2 |   TABLE ACCESS BY INDEX ROWID   | T1    |     1 |   120 |     3   (0)| 00:00:01 |                              
|   3 |    NESTED LOOPS                 |       |   100 | 13000 |   254   (1)| 00:00:02 |                              
|   4 |     SORT UNIQUE                 |       |   100 |  1000 |   103   (0)| 00:00:01 |                              
|   5 |      TABLE ACCESS BY INDEX ROWID| T2    |   100 |  1000 |   103   (0)| 00:00:01 |                              
|*  6 |       INDEX RANGE SCAN          | T2_I1 |   100 |       |     3   (0)| 00:00:01 |                              
|*  7 |     INDEX RANGE SCAN            | T1_N1 |     1 |       |     2   (0)| 0:00:01                                 
-----------------------------------------------------------------------------------------                              
Predicate Information (identified by operation id):                                                                    
---------------------------------------------------                                                                    
   6 - access("X1"=17335)                                                                                              
   7 - access("ID"="ID")                                                                                         
25 rows selected.

Ok that’s nice and acceptable response time. But let’s try the same query under first_rows mode

mhouri.world> alter session set optimizer_mode=FIRST_ROWS;
Session altered.

mhouri.world> select *
  2  from t1 where id in (select id from t2 where x1 = 17335)
  3  order by id
  4  ;

107 rows selected.

mhouri.world> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT                                                                                                      
--------------------------------------------------------------------------------------
SQL_ID  d2wym19g2n196, child number0                                                                                   
-------------------------------------                                                                                  
select * from t1 where id in (select id from t2 where x1 = 17335) order by id                               
Plan hash value: 3283237002                                                                                            

--------------------------------------------------------------------------------------                                 
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |                                 
--------------------------------------------------------------------------------------                                 
|   0 | SELECT STATEMENT             |       |       |       |   102M(100)|          |                                 
|   1 |  NESTED LOOPS SEMI           |       |   101 | 13130 |   102M  (1)|168:20:17 |                                 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |  1000K|   114M| 20385   (1)| 00:02:01 |                                 
|   3 |    INDEX FULL SCAN           | T1_N1 |  1000K|       |  2800   (1)| 00:00:17 |                                 
|*  4 |   TABLE ACCESS BY INDEX ROWID| T2    |     1 |    10 |   102   (0)| 00:00:01 |                                 
|*  5 |    INDEX RANGE SCAN          | T2_I1 |   100 |       |     2   (0)| 00:00:01 |                                 
--------------------------------------------------------------------------------------                                 
Predicate Information (identified by operationid):                                                                     
---------------------------------------------------                                                                    
   4 - filter("ID"="ID")                                                                                               
   5 - acces("X1"=17335)                                                                                               
23 rows selected.

Woo!!! Note the apparition of the costly operation 3 INDEX FULL SCAN, the enormous cost difference between all_rows and first_rows and the absence of the order by operation. Under the later mode, Oracle optimizer prefers using a costly INDEX FULL SCAN operation instead of doing a sort operation which in this case will generate a catastrophic response time.