Oracle이나 MSSQL을 사용해 본 사람이라면 아마도 “WITH 절”
을 사용해 보았을 것이다. 어떤 사람들은 이를 “서브 쿼리 팩토링(Subquery Factoring)”이라 하고, 또 어떤 사람들은 “COMMON TABLE EXPRESSION”이라고 한다3개의 컬럼을 가지는 T1 테이블을 가정해보자.
CREATE TABLE T1(
YEAR INT, # 2000, 2001, 2002 ...
MONTH INT, # January, February, ...
SALES INT # how much we sold on that month of that year
);
이제 연도별로 판매 실적의 트렌드(증감)를 조회하고자 한다고 해보자.
SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND
FROM
(SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR) AS D1,
(SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR) AS D2
WHERE D1.YEAR = D2.YEAR-1;
위 쿼리에서 두개의 서브 쿼리는 동일한 서브 쿼리 문장을 사용하고 있다. 하지만 일반적인 DBMS는 그걸 인지할만큼 똑똑하지 않다. 그래서 DBMS 옵티마이저는 “SELECT YEAR, SUM(SALES), …” 문장을 두번 실행해서 각각 D1과 D2 임시 테이블을 채우게 될 것이다. 이렇게 동일한 문장을 두번 수행하는 작업은 성능적으로도 상당히 문제가 될 수 있다. WITH 절을 사용하게 되면, 이런 제한 사항이 없어지고 서브 쿼리는 단 한번만 수행하게 된다.
WITH D AS (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR)
SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND
FROM
D AS D1,
D AS D2
WHERE D1.YEAR = D2.YEAR-1;
하지만 MySQL에서는 WITH 절이 지원되지 않는다. 하지만 VIEW를 이용하면 WITH절을 에뮬레이션할 수 있다.
CREATE VIEW D AS (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR);
SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND
FROM
D AS D1,
D AS D2
WHERE D1.YEAR = D2.YEAR-1;
DROP VIEW D;
VIEW 대신 D 테이블을 일반 테이블로 생성할 수도 있다. 이때 D 테이블을 임시 테이블로 사용할 수는 없다. MySQL에서는 하나의 쿼리 문장에서 임시 테이블은 단 한번만 참조될 수 있다는 제한 사항이 있기 때문이다. (http://dev.mysql.com/doc/refman/5.7/en/temporary-table-problems.html)
이제 조금 더 복잡한 형태의 WITH 절 사용법(재귀적인 형태)을 살펴보자.
SQL 표준에 의하면, 재귀적인 형태를 사용하기 위해서는 “WITH RECURSIVE”를 사용해야 한다. 하지만 일반적인 DBMS에서는 RECURSIVE 키워드는 생략할 수 있는 것으로 보인다.
WITH RECURSIVE는 매우 강력한 기능을 제공하는데, 예를 들어서 오라클 RDBMS의 CONNECT BY와 동일한 형태의 쿼리를 처리할 수도 있다. WITH RECURSIVE를 이해하기 위해서 employees 라는 테이블(아주 전통적인 WITH RECURSIVE 예제용 테이블)을 생각해보자. (실제 오라클 RDBMS에서도 이제는 SQL 표준 형태인 WITH 절을 지원하므로, 더 이상 오라클 RDBMS에서도 CONNECT BY .. START WITH .. 구문을 사용하지 않아도 된다.)
CREATE TABLE EMPLOYEES (
ID INT PRIMARY KEY,
NAME VARCHAR(100),
MANAGER_ID INT,
INDEX (MANAGER_ID),
FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES(ID)
);
INSERT INTO EMPLOYEES VALUES
(333, "Yasmina", NULL),
(198, "John", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(692, "Tarek", 333);
데이터를 간단히 살펴보면, Yasmina는 CEO이며 John과 Tarek의 보스는 Yasmina이며, Pedro의 보스는 John이다. 그리고 Sarah와 Pierre의 보스는 Pero이다. 매우 큰 회사라면, 이 테이블의 레코드 건수는 몇 천에서 몇 만건이 될 것이다.
이제 각 사원에 대해서 “얼마나 많은 사람들이 직간접적으로 조직 관계를 구성하는지”를 알아내고자 한다. 이를 위해서 우선 매니저가 아닌 사람들의 목록을 만들고자 하는데, 간단히 서브 쿼리를 이용해서 매니저인 사람의 목록을 만들고 NOT IN (subquery)를 이용해서 매니저가 아닌 사람들의 목록을 조회할 수 있다.
SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
FROM EMPLOYEES
WHERE ID NOT IN (
SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL
);
그리고 이 결과를 새로운 테이블 EMPLOYEES_EXTENDED에 저장하자. EMPLOYEES_EXTENDED 테이블의 마지막에는 “이 사원에게 직간접적으로 보고를 하는 사원 수를 저장하는” REPORTS 컬럼을 추가했다. 즉 REPORTS 컬럼의 값이 0인 사원은 매니저가 아닌 것이다. 이제 아래와 같은 쿼리를 이용해서 첫번째 레벨의 매니저(매니저가 아닌 사원의 직접적인 차상위 매니저)를 구할 수 있게 되었다.
SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
FROM EMPLOYEES M
JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
GROUP BY M.ID, M.NAME, M.MANAGER_ID;
위 쿼리 각 사원(매니저가 아닌)의 직접적인 보스 사원 정보를 0건 1건 이상을 만들어내게 된다. 이때 REPORTS 컬럼의 값이 1인 경우는 매니저가 아닌 사원을 의미하며, 2이상인 경우는 자신에게 보고하는 사원중에서 매니저가 아닌 사원의 수를 리턴하게 된다.
이제 EMPLOYEES_EXTENDED 테이블을 모두 지우고, 바로 위의 쿼리 결과를 EMPLOYEES_EXTENDED 테이블에 채워넣자. 이제 EMPLOYEES_EXTENDED 테이블에는 1차 레벨의 매니저로 채워지게 된다. 그리고 다시 똑같은 쿼리를 실행해보자. 그러면 그 결과로 2차 레벨의 매니저 목록을 조회할 수 있게 된다. 이 과정을 계속 반복하면 결국 Yasmina 레코드 한건만 EMPLOYEES_EXTENDED 테이블에 채워지게 될 것이다. 하지만 마지막 과정에서 SELECT 쿼리는 (E.MANAGER_ID가 NULL일 것이므로) 아무런 레코드를 만들어내지 않게 될 것이다.
지금까지의 과정을 간략히 정리해보면, EXMPLOYEES_EXTENDED 테이블은 매니저가 아닌 사원 그리고 1차 레벨 매니저 그리고 그 다음에는 2차 매니저 그리고 반복해서 조회한 N차 매니저를 저장하는 일종의 임시 버퍼로 사용된 것이다. 여기에서 우리는 재귀적인 기능을 사용한 것이다. 하지만 여기에서 한 가지 문제점은 이렇게 EMPLOYEES_EXENDED 테이블(임시 버퍼)에 저장되었던 결과들을 어떻게 병합(UNION ALL)할 것인지이다.
매니저가 아닌 사원의 목록이 재귀적인 처리의 시작이었는데, 이를 “앵커 멤버(Anchor Member)” 또는 “시드(Seed)”라고 한다. 그리고 재귀적으로 반복해서 실행되는 SELECT 쿼리는 “재귀 멤버(Recursive Member)”라고 한다. 완전히 WITH 절 쿼리는 아래와 같다.
WITH RECURSIVE
# The temporary buffer, also used as UNION result:
EMPLOYEES_EXTENDED
AS
(
# The seed:
SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
FROM EMPLOYEES
WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)
UNION ALL
# The recursive member:
SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
GROUP BY M.ID, M.NAME, M.MANAGER_ID
)
# what we want to do with the complete result (the UNION):
SELECT * FROM EMPLOYEES_EXTENDED;
MySQL은 아직 WITH RECURSIVE 기능을 제공하지 않는다. 하지만 일반적인 스토어드 프로시져를 이용하면 아래와 같이 간단하게 에뮬레이션할 수 있다.
CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
FROM EMPLOYEES
WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)
",
"
SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
GROUP BY M.ID, M.NAME, M.MANAGER_ID
",
"SELECT * FROM EMPLOYEES_EXTENDED",
0,
""
);
눈치채고 있듯이, 스토어드 프로그램의 각 파라미터는 WITH 표준 문법의 각 멤버(“임시 버퍼의 이름”, “시드를 위한 쿼리”, “ 재귀 멤버를 위한 쿼리”, “최종 결과를 어떻게 할 것인지”)를 입력으로 사용하고 있다. 마지막 두개 파라미터는 0과 빈 문자값은 지금은 무시하도록 하자.
아래는 스토어드 프로그램 실행 결과이다.
+------+---------+------------+---------+
| ID | NAME | MANAGER_ID | REPORTS |
+------+---------+------------+---------+
| 72 | Pierre | 29 | 0 |
| 692 | Tarek | 333 | 0 |
| 4610 | Sarah | 29 | 0 |
| 29 | Pedro | 198 | 2 |
| 333 | Yasmina | NULL | 1 |
| 198 | John | 333 | 3 |
| 333 | Yasmina | NULL | 4 |
+------+---------+------------+---------+
7 rows in set
Pierre와 Tarek 그리고 Sarah는 아무도 직속 멤버가 없으며, Pedro는 2명의 직속 멤버를 가지고 있다. 하지만 Yasmina는 2번 결과 셋에서 두번 나타났다. 이는 우리의 알고리즘이 매니저가 아닌 사원으로부터 시작했기 때문이다. 즉 나무(Yasmina는 나무의 뿌리)의 가지에서부터 재귀적으로 데이터를 가져왔기 때문이다. 그래서 마지막 쿼리를 이제 조금 바꿔 보았다.
CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
FROM EMPLOYEES
WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)
",
"
SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
GROUP BY M.ID, M.NAME, M.MANAGER_ID
",
"
SELECT ID, NAME, MANAGER_ID, SUM(REPORTS)
FROM EMPLOYEES_EXTENDED
GROUP BY ID, NAME, MANAGER_ID
",
0,
""
);
이제서야 제대로 된 결과가 나왔다.
+------+---------+------------+--------------+
| ID | NAME | MANAGER_ID | SUM(REPORTS) |
+------+---------+------------+--------------+
| 29 | Pedro | 198 | 2 |
| 72 | Pierre | 29 | 0 |
| 198 | John | 333 | 3 |
| 333 | Yasmina | NULL | 5 |
| 692 | Tarek | 333 | 0 |
| 4610 | Sarah | 29 | 0 |
+------+---------+------------+--------------+
6 rows in set
이제 스토어드 프로시져의 실제 코드를 살펴보자. PreparedStatement를 이용하는 동적 쿼리가 상당히 사용된 것을 알 수 있다. 이 스토어드 프로시져는 특별히 해결하기 어려운 제한 사항들을 가지고 있지는 않으므로 다른 용도(대표적으로 각 부서의 조직도를 조회하는 재귀 쿼리)의 재귀적 쿼리에서도 충분히 활용할 수 있을 것이다. 자세한 내용은 스토어드 프로그램 코드의 주석을 참조하자.
# Usage: the standard syntax:
# WITH RECURSIVE recursive_table AS
# (initial_SELECT
# UNION ALL
# recursive_SELECT)
# final_SELECT;
# should be translated by you to
# CALL WITH_EMULATOR(recursive_table, initial_SELECT, recursive_SELECT,
# final_SELECT, 0, "").
# ALGORITHM:
# 1) we have an initial table T0 (actual name is an argument
# "recursive_table"), we fill it with result of initial_SELECT.
# 2) We have a union table U, initially empty.
# 3) Loop:
# add rows of T0 to U,
# run recursive_SELECT based on T0 and put result into table T1,
# if T1 is empty
# then leave loop,
# else swap T0 and T1 (renaming) and empty T1
# 4) Drop T0, T1
# 5) Rename U to T0
# 6) run final select, send result to client
# This is for *one* recursive table.
# It would be possible to write a SP creating multiple recursive tables.
delimiter ;;
CREATE PROCEDURE with_emulator(
recursive_table VARCHAR(100), # name of recursive table
initial_select VARCHAR(65530), # seed a.k.a. anchor
recursive_select VARCHAR(65530), # recursive member
final_select VARCHAR(65530), # final SELECT on UNION result
max_recursion INT UNSIGNED, # safety against infinite loop, use 0 for default
create_table_options VARCHAR(65530) # you can add CREATE-TABLE-time options
# to your recursive_table, to speed up initial/recursive/final SELECTs; example: "(KEY(some_column)) ENGINE=MEMORY"
)
BEGIN
DECLARE new_rows INT UNSIGNED;
DECLARE show_progress INT DEFAULT 0; # SET to 1 to trace/debug execution
DECLARE recursive_table_next VARCHAR(120);
DECLARE recursive_table_union VARCHAR(120);
DECLARE recursive_table_tmp VARCHAR(120);
SET recursive_table_next = CONCAT(recursive_table, "_next");
SET recursive_table_union = CONCAT(recursive_table, "_union");
SET recursive_table_tmp = CONCAT(recursive_table, "_tmp");
# IF you need to reference recursive_table more than
# once in recursive_select, remove the TEMPORARY word.
# 0) cleaning for previous procedure failed
SET @str = CONCAT("DROP TABLE IF EXISTS ", recursive_table_next);
PREPARE stmt FROM @str;
EXECUTE stmt;
SET @str = CONCAT("DROP TABLE IF EXISTS ", recursive_table_union);
PREPARE stmt FROM @str;
EXECUTE stmt;
SET @str = CONCAT("DROP TABLE IF EXISTS ", recursive_table_tmp);
PREPARE stmt FROM @str;
EXECUTE stmt;
# 1) create and fill T0
SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ", create_table_options, " AS ", initial_select);
PREPARE stmt FROM @str;
EXECUTE stmt;
# 2) create U
SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
# 3) create T1
SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
IF max_recursion = 0 THEN
SET max_recursion = 100; # a default to protect the innocent
END IF;
recursion: REPEAT
# add T0 to U (this is always UNION ALL)
SET @str = CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
# we are done IF max depth reached
SET max_recursion = max_recursion - 1;
IF not max_recursion THEN
IF show_progress THEN
SELECT CONCAT("max recursion exceeded");
END IF;
LEAVE recursion;
END IF;
# fill T1 by applying the recursive SELECT on T0
SET @str = CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_select);
PREPARE stmt FROM @str;
EXECUTE stmt;
# we are done IF no rows in T1
SELECT ROW_COUNT() INTO new_rows;
IF show_progress THEN
SELECT CONCAT(new_rows, " new rows found");
END IF;
IF NOT new_rows THEN
LEAVE recursion;
END IF;
# Prepare next iteration:
# T1 becomes T0, to be the source of next run of recursive_select,
# T0 is recycled to be T1.
SET @str = CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
PREPARE stmt FROM @str;
EXECUTE stmt;
# we use ALTER TABLE RENAME because RENAME TABLE does not support temp tables
SET @str = CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
SET @str = CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
PREPARE stmt FROM @str;
EXECUTE stmt;
# empty T1
SET @str = CONCAT("TRUNCATE TABLE ", recursive_table_next);
PREPARE stmt FROM @str;
EXECUTE stmt;
UNTIL 0 END REPEAT;
# eliminate T0 and T1
SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table_next, ", ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
# Final (output) SELECT uses recursive_table name
SET @str = CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
# Run final SELECT on UNION
SET @str = final_select;
PREPARE stmt FROM @str;
EXECUTE stmt;
# No temporary tables may survive:
SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
# We are done :-)
END ;;
delimiter ;
여기까지의 글은 Oracle의 MySQL 개발자인 "Guilhem Bichot"의 블로그 게시물을 번역한 것입니다.
“Guilhem Bichot”의 블로그 내용에 몇 가지를 추가해보면…
일반적으로 조직도를 조회하는 재귀 쿼리를 위해서 Top-Down 트리를 조회하는 쿼리는 아래와 같이 실행할 수 있다. 각 그룹별로 트리를 순회(Tree traversal)하는 형태의 결과와 같이 레코드를 정렬하기 위해서 sort_key 라는 컬럼도 같이 추가해서 구현해본 예제이다. Sort_key 컬럼에 저장되는 값은 SQL 문장을 보면 쉽게 이해할 수 있을 것으로 보이므로, 자세한 설명은 생략하도록 하겠다.(물론, 이 경우에도 M-M 관계의 트리가 생성되는 경우라면, GROUP BY가 필요할 수 있을 것으로 보인다.)
CALL with_emulator(
"e$emp",
"
SELECT id, name, manager_id, 1 as lv, @_seq:=0 as sort_key
FROM emp
WHERE manager_id is null
",
"
SELECT m.id, m.name, m.manager_id, e.lv+1 as lv, CONCAT(e.sort_key, LPAD(@_seq:=@_seq+1, 5, '0')) as sort_key
FROM emp m JOIN e$emp e ON m.manager_id=e.id
",
"
SELECT id, name, manager_id, lv, sort_key
FROM e$emp
ORDER BY sort_key
",
0,
"(id INT NOT NULL, name VARCHAR(100), manager_id INT, lv INT, sort_key VARCHAR(100), KEY ix_sortkey(sort_key)) ENGINE=MEMORY"
);
+------+---------+------------+------+------------------+
| id | name | manager_id | lv | sort_key |
+------+---------+------------+------+------------------+
| 333 | Yasmina | NULL | 1 | 0 |
| 198 | John | 333 | 2 | 000001 |
| 29 | Pedro | 198 | 3 | 00000100003 |
| 72 | Pierre | 29 | 4 | 0000010000300004 |
| 4610 | Sarah | 29 | 4 | 0000010000300005 |
| 692 | Tarek | 333 | 2 | 000002 |
+------+---------+------------+------+------------------+
6 rows in set (0.01 sec)
그리고 아래 프로시져는 “Guilhem Bichot”의 블로그에 공유된 프로시져에, 내부적으로 사용되는 임시 테이블중에서 중간 테이블과 최종 테이블의 구조를 다르게 생성하도록 조금 수정해 본 것이다. 중간 과정에서 필요한 테이블에는 인덱스가 있으면 더 성능이 느려지지만, 마지막 결과를 저장하는 최종 테이블에는 인덱스가 있으면 더 효율적인 경우에는 이런 구성이 더 효율적일 수도 있을 것으로 보인다.
또한 추가로 임시 테이블과 쿼리를 위한 세션 변수 변경 및 복구와 SQL Error에 대한 처리 몇 가지를 더 추가한 것이다.
DELIMITER ;;
DROP PROCEDURE with_emulator2;;
CREATE PROCEDURE with_emulator2(
recursive_table VARCHAR(100), # name of recursive table
initial_select VARCHAR(65530), # seed a.k.a. anchor
recursive_select VARCHAR(65530), # recursive member
final_select VARCHAR(65530), # final SELECT on UNION result
max_recursion INT UNSIGNED, # safety against infinite loop, use 0 for default
working_table_options VARCHAR(500), # you can add CREATE-TABLE-time options for intermediate working table
final_table_options VARCHAR(500) # you can add CREATE-TABLE-time options for final table
# to your recursive_table, to speed up initial/recursive/final SELECTs; example: "(KEY(some_column)) ENGINE=MEMORY"
)
BEGIN
DECLARE loop_count INT UNSIGNED DEFAULT 0;
DECLARE new_rows INT UNSIGNED;
DECLARE show_progress INT DEFAULT 0; # SET to 1 to trace/debug execution
DECLARE recursive_table_next VARCHAR(120);
DECLARE recursive_table_union VARCHAR(120);
DECLARE recursive_table_tmp VARCHAR(120);
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
## Restore session variables
SET @@tmp_table_size = @orig_tmp_table_size;
SET @@max_heap_table_size = @orig_max_heap_table_size;
SET @@sort_buffer_size = @orig_sort_buffer_size;
SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table_next);
PREPARE stmt FROM @str;
EXECUTE stmt;
SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table_tmp);
PREPARE stmt FROM @str;
EXECUTE stmt;
SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table_union);
PREPARE stmt FROM @str;
EXECUTE stmt;
## Resignaling the origin exception to user
RESIGNAL;
END;
SET recursive_table_next = CONCAT(recursive_table, "_next");
SET recursive_table_union = CONCAT(recursive_table, "_union");
SET recursive_table_tmp = CONCAT(recursive_table, "_tmp");
## Backup session variables
SET @orig_tmp_table_size = @@tmp_table_size;
SET @orig_max_heap_table_size = @@max_heap_table_size;
SET @orig_sort_buffer_size = @@sort_buffer_size;
## Update new session variables for with_emulator
SET @@tmp_table_size = 1024*1024; # 1MB
SET @@max_heap_table_size = 1024*1024; # 1MB
SET @@sort_buffer_size = 1024*1024; # 1MB
# IF you need to reference recursive_table more than
# once in recursive_select, remove the TEMPORARY word.
# 1) create and fill T0
SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ", working_table_options, " AS ", initial_select);
PREPARE stmt FROM @str;
EXECUTE stmt;
# 2) create U
## 작업 테이블과 최종 테이블의 구조를 다르게 가져가기 위해서는 initial_select를 두번 실행할 수밖에 없다.
## 작업 테이블에는 인덱스가 필요없지만 최종 테이블에는 인덱스가 필요한 경우가 많으므로, 원본 쿼리대신 recursive_table_union 테이블을 (recursive_table 복사가 아니라) initial_select를 이용해서 다시 생성하도록 수정함.
## ORIG :: SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
## initial_select가 아주 가벼운 쿼리라면 아래 쿼리를 실행해도 무방하지만, initial_select 쿼리가 무겁다면 위의 원본 쿼리가 좋음.
SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " ", final_table_options, " AS ", initial_select);
PREPARE stmt FROM @str;
EXECUTE stmt;
# 3) create T1
SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
IF max_recursion = 0 THEN
SET max_recursion = 100; # a default to protect the innocent
END IF;
recursion: REPEAT
IF loop_count > 0 THEN ## Copy recursive_table to recursive_table_union if this is not the first loop
# add T0 to U (this is always UNION ALL)
SET @str = CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
END IF;
# we are done IF max depth reached
SET loop_count = loop_count + 1;
IF loop_count > max_recursion THEN
IF show_progress THEN
SELECT CONCAT("max recursion exceeded");
END IF;
LEAVE recursion;
END IF;
# fill T1 by applying the recursive SELECT on T0
SET @str = CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_select);
PREPARE stmt FROM @str;
EXECUTE stmt;
# we are done IF no rows in T1
SELECT ROW_COUNT() INTO new_rows;
IF show_progress THEN
SELECT CONCAT(new_rows, " new rows found");
END IF;
IF NOT new_rows THEN
LEAVE recursion;
END IF;
# Prepare next iteration:
# T1 becomes T0, to be the source of next run of recursive_select,
# T0 is recycled to be T1.
SET @str = CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
PREPARE stmt FROM @str;
EXECUTE stmt;
# we use ALTER TABLE RENAME because RENAME TABLE does not support temp tables
SET @str = CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
SET @str = CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
PREPARE stmt FROM @str;
EXECUTE stmt;
# empty T1
SET @str = CONCAT("TRUNCATE TABLE ", recursive_table_next);
PREPARE stmt FROM @str;
EXECUTE stmt;
UNTIL 0 END REPEAT;
# eliminate T0 and T1
SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table_next, ", ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
# Final (output) SELECT uses recursive_table name
SET @str = CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
# Run final SELECT on UNION
SET @str = final_select;
PREPARE stmt FROM @str;
EXECUTE stmt;
# No temporary tables may survive:
SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table);
PREPARE stmt FROM @str;
EXECUTE stmt;
# We are done :-)
## Restore session variables
SET @@tmp_table_size = @orig_tmp_table_size;
SET @@max_heap_table_size = @orig_max_heap_table_size;
SET @@sort_buffer_size = @orig_sort_buffer_size;
END ;;
DELIMITER ;
CALL with_emulator2(
"e$emp",
"
SELECT id, name, manager_id, 1 as lv, @_seq:=0 as sort_key
FROM emp
WHERE manager_id is null
",
"
SELECT m.id, m.name, m.manager_id, e.lv+1 as lv, CONCAT(e.sort_key, LPAD(@_seq:=@_seq+1, 5, '0')) as sort_key
FROM emp m JOIN e$emp e ON m.manager_id=e.id
",
"
SELECT id, name, manager_id, lv, sort_key
FROM e$emp
ORDER BY sort_key
",
0,
"(id INT NOT NULL, name VARCHAR(100), manager_id INT, lv INT, sort_key VARCHAR(100)) ENGINE=MEMORY",
"(id INT NOT NULL, name VARCHAR(100), manager_id INT, lv INT, sort_key VARCHAR(100), KEY ix_sortkey(sort_key)) ENGINE=MEMORY"
);
+------+---------+------------+------+------------------+
| id | name | manager_id | lv | sort_key |
+------+---------+------------+------+------------------+
| 333 | Yasmina | NULL | 1 | 0 |
| 198 | John | 333 | 2 | 000001 |
| 29 | Pedro | 198 | 3 | 00000100003 |
| 72 | Pierre | 29 | 4 | 0000010000300004 |
| 4610 | Sarah | 29 | 4 | 0000010000300005 |
| 692 | Tarek | 333 | 2 | 000002 |
+------+---------+------------+------+------------------+
6 rows in set (0.02 sec)
댓글 없음:
댓글 쓰기