10g에서 group by 사용시 주의사항

◆ 증상 1
    - group by 절에 2개 이상의 컬럼이 정의된 경우 별도의 내부적으로 Sort를 수행하지 않음.
    - 적용버전 : Oracle 9i 9.2.X 버전 이상
    - 기존 개발 및 운영DB(9.2.0.4)에서도 상기의 경우 동일하게 Sort를 수행하지 않음.
    - 그러나, Index 를 타는지의 여부에 따라 Sort 여부가 결정되므로 order by절을 꼭 기술할 것.

◆ 증상 2
    - group by 절에 1개의 컬럼이 정의된 경우를 테스트한 결과
       . 기존DB(9.2.0.4) : 내부적으로 Sort 수행함.
       . 신규DB(10.2.0.3) : 내부적으로 Sort를 수행하지 않음.

◆ 결론 및 개발자 조치사항
   - Sort가 필요한 SQL에서는 group by절의 유무와 관계없이 반드시 order by 절을 기술할 것.
   - group by와 함께 rollup등의 grouping 함수 사용시에도 Sort를 꼭 점검 필요함.
   - 기타 신규DB를 사용하게 되는 모든 SQL에 대한 결과데이터 및 성능검증을 반드시
     수행할 것.(특히 DB Link를 사용하게 되는 SQL)

◆ 첨부파일 : 기존 운영 및 신규 DB에서 테스트한 결과

============================================
기존 운영DB(Oracle 9i 9.2.0.4)
============================================
SQL> select object_type, status, count(*)
  2  from   user_objects
  3  group by object_type, status;

OBJECT_TYPE        STATUS    COUNT(*)
------------------ ------- ----------
LOB                VALID            5
VIEW               VALID            2
INDEX              VALID         1522
TABLE              VALID         1276
LIBRARY            VALID            1
PACKAGE            VALID            7
SYNONYM            VALID           49
TRIGGER            VALID            1
FUNCTION           VALID           11
FUNCTION           INVALID          7
SEQUENCE           VALID           48

OBJECT_TYPE        STATUS    COUNT(*)
------------------ ------- ----------
PROCEDURE          VALID           47
PROCEDURE          INVALID          7
PACKAGE BODY       VALID            6
DATABASE LINK      VALID            3
INDEX PARTITION    VALID         2661
TABLE PARTITION    VALID          892
INDEX SUBPARTITION VALID         7040
TABLE SUBPARTITION VALID          880

19 rows selected.

SQL> select object_type, count(*)
  2  from   user_objects
  3  group by object_type;

OBJECT_TYPE          COUNT(*)
------------------ ----------
DATABASE LINK               3
FUNCTION                   18
INDEX                    1522
INDEX PARTITION          2661
INDEX SUBPARTITION       7040
LIBRARY                     1
LOB                         5
PACKAGE                     7
PACKAGE BODY                6
PROCEDURE                  54
SEQUENCE                   48

OBJECT_TYPE          COUNT(*)
------------------ ----------
SYNONYM                    49
TABLE                    1276
TABLE PARTITION           892
TABLE SUBPARTITION        880
TRIGGER                     1
VIEW                        2

17 rows selected.

SQL> select object_type, count(*)
  2  from   user_objects
  3  group  by object_type
  4  order  by object_type;

OBJECT_TYPE          COUNT(*)
------------------ ----------
DATABASE LINK               3
FUNCTION                   18
INDEX                    1522
INDEX PARTITION          2661
INDEX SUBPARTITION       7040
LIBRARY                     1
LOB                         5
PACKAGE                     7
PACKAGE BODY                6
PROCEDURE                  54
SEQUENCE                   48

OBJECT_TYPE          COUNT(*)
------------------ ----------
SYNONYM                    49
TABLE                    1276
TABLE PARTITION           892
TABLE SUBPARTITION        880
TRIGGER                     1
VIEW                        2

17 rows selected.
============================================
신규 DB(Oracle 10g 10.2.0.3)
============================================
SQL> select object_type, status, count(*)
  2  from   user_objects
  3  group by object_type, status;
                  
OBJECT_TYPE         STATUS    COUNT(*)
------------------- ------- ----------
PROCEDURE           VALID           21
PACKAGE BODY        VALID            4
FUNCTION            VALID            5
TABLE SUBPARTITION  VALID         1600
PACKAGE             VALID            4
INDEX               VALID          720
SEQUENCE            VALID            9
INDEX SUBPARTITION  VALID        12800
TABLE PARTITION     VALID          648
TABLE               VALID          581
LIBRARY             VALID            1

OBJECT_TYPE         STATUS    COUNT(*)
------------------- ------- ----------
PROCEDURE           INVALID          1
INDEX PARTITION     VALID         2425
MATERIALIZED VIEW   VALID            7
DATABASE LINK       VALID            4

15 rows selected.

SQL> select object_type, count(*)
  2  from   user_objects
  3  group by object_type;

OBJECT_TYPE           COUNT(*)
------------------- ----------
INDEX                      720
TABLE SUBPARTITION        1600
PROCEDURE                   22
TABLE PARTITION            648
INDEX SUBPARTITION       12800
TABLE                      581
LIBRARY                      1
FUNCTION                     5
MATERIALIZED VIEW            7
DATABASE LINK                4
PACKAGE BODY                 4
                             
OBJECT_TYPE           COUNT(*)
------------------- ----------
INDEX PARTITION           2425
SEQUENCE                     9
PACKAGE                      4
                             
14 rows selected.

SQL> select object_type, count(*)                       
  2  from   user_objects                    
  3  group  by object_type                  
  4  order  by object_type;                 

OBJECT_TYPE           COUNT(*)
------------------- ----------
DATABASE LINK                4
FUNCTION                     5
INDEX                      720
INDEX PARTITION           2425
INDEX SUBPARTITION       12800
LIBRARY                      1
MATERIALIZED VIEW            7
PACKAGE                      4
PACKAGE BODY                 4
PROCEDURE                   22
SEQUENCE                     9

OBJECT_TYPE           COUNT(*)
------------------- ----------
TABLE                      581
TABLE PARTITION            648
TABLE SUBPARTITION        1600

14 rows selected.

[출처] 오라클 10G SQL사용시 주의사항[group by sort]|작성자 김연준