GROUP BY 절에 있는 모든 컬럼이 인덱스에 포함되어 질의 수행 시 인덱스를 사용할 수 있어 별도의 정렬 작업을 하지 않는 것을 GROUP BY 절 최적화라고 한다. 이를 위해서는 GROUP BY 절에 있는 컬럼들이 인덱스를 구성하는 컬럼들의 제일 앞 쪽에 모두 존재해야 한다.
SELECT * FROM tab WHERE col1 > 0 GROUP BY col1,col2
인덱스를 구성하는 컬럼이 GROUP BY 절에 없더라도 그 컬럼의 조건이 상수일 때는 인덱스를 사용할 수 있다.
SELECT * FROM tab WHERE col2=val GROUP BY col1,col3
위의 예에서 tab(col1, col2, col3)로 구성된 인덱스가 있으면 이 인덱스를 GROUP BY 최적화에 사용한다.
이 경우에도 인덱스 스캔 시 요구하는 순서대로 결과를 가져오므로, GROUP BY에 의해서 행에 대한 정렬이 불필요하게 된다.
WHERE 절이 없어도 GROUP BY 컬럼으로 구성된 인덱스가 있고 그 인덱스의 첫번째 컬럼이 NOT NULL 이면 GROUP BY 최적화가 적용된다.
집계 함수 사용 시 GROUP BY 최적화가 적용되는 경우는 MIN()이나 MAX()를 사용할 때 뿐이며, 두 집계 함수가 같이 쓰이려면 같은 컬럼을 사용하는 경우에만 적용된다.
CREATE INDEX ON T(a, b, c);
SELECT a, MIN(b), c, MAX(b) FROM T WHERE a > 18 GROUP BY a, b;
CREATE TABLE tab (i INT, j INT, k INT);
CREATE INDEX ON tab (j,k);
INSERT INTO tab VALUES (1,2,3),(6,4,2),(3,4,1),(5,2,1),(1,5,5),(2,6,6),(3,5,4);
다음의 예는 j, k 컬럼으로 GROUP BY를 수행하므로 tab(j,k)로 구성된 인덱스가 사용되고 별도의 정렬 과정이 필요 없다.
SELECT i,j,k FROM tab WHERE j > 0 GROUP BY j,k;
-- the selection from the query plan dump shows that the index i_tab_j_k was used and sorting was not necessary
-- (/* ---> skip GROUP BY */)
Query plan:
iscan
class: tab node[0]
index: i_tab_j_k term[0]
sort: 2 asc, 3 asc
cost: fixed 0(0.0/0.0) var 1(0.0/1.0) card 0
Query stmt:
select tab.i, tab.j, tab.k from tab tab where ((tab.j> ?:0 )) group by tab.j, tab.k
/* ---> skip GROUP BY */
i j k
5 2 1
1 2 3
3 4 1
6 4 2
3 5 4
1 5 5
2 6 6
다음의 예는 j, k 컬럼으로 GROUP BY를 수행하며 j에 대한 조건이 없지만 j 컬럼은 NOT NULL 속성을 지니므로, tab(j,k)로 구성된 인덱스가 사용되고 별도의 정렬 과정이 필요 없다.
ALTER TABLE tab CHANGE COLUMN j j INT NOT NULL;
SELECT * FROM tab GROUP BY j,k;
-- the selection from the query plan dump shows that the index i_tab_j_k was used (since j has the NOT NULL constraint )
-- and sorting was not necessary (/* ---> skip GROUP BY */)
Query plan:
iscan
class: tab node[0]
index: i_tab_j_k
sort: 2 asc, 3 asc
cost: fixed 0(0.0/0.0) var 1(0.0/1.0) card 0
Query stmt: select tab.i, tab.j, tab.k from tab tab group by tab.j, tab.k
/* ---> skip GROUP BY */
=== <Result of SELECT Command in Line 1> ===
i j k
=======================================
5 2 1
1 2 3
3 4 1
6 4 2
3 5 4
1 5 5
2 6 6