UNION ALL 질의문을 400개를 초과해 사용할 경우 다음과 같이 에러가 출력된다.
- ERROR: The query has been rejected due to attempt to exceed the maximum allowed nesting level(400).
UNION ALL을 대신하여 VALUES 절을 활용하여 UNION ALL 질의문을 단순하게 표현할 수 있다.
[테스트]
create table tmp as (select level as lv from db_root connect by level <= 2000);
alter table tmp add primary key (lv);
SELECT /*recompile*/ *
FROM tmp a
JOIN(
SELECT 1 AS col1, 'first' AS col2
UNION ALL
SELECT 2, 'a'
UNION ALL
SELECT 3, 'a'
UNION ALL
...
SELECT 1001, 'a'
UNION ALL
SELECT 1002, 'a'
) b ON b.col1 = a.lv;
위와 같이 UNION ALL 문을 400개 이상 사용할 수 없는 질의를 VALUES문을 활용하여 아래와 같이 사용할 수 있다.
SELECT /* recompile */ *
FROM tmp a
JOIN (
VALUES (1 AS col1,'first' AS col2),
(2 ,'a'),
(3 ,'a'),
...
(1001 ,'a'),
(1002 ,'a')
) b ON b.col1 = a.lv;
trace
======================
Trace Statistics:
SELECT (time: 7, fetch: 2005, ioread: 0)
SCAN (temp time: 0, fetch: 0, ioread: 0, readrows: 1002, rows: 1002)
SCAN (index: tmp.pk_tmp_lv), (btree time: 5, fetch: 2004, ioread: 0, readkeys: 1002, filteredkeys: 0, rows: 1002, covered: true)
SUBQUERY (uncorrelated)
SELECT (time: 0, fetch: 0, ioread: 0)
SCAN (noscan time: 0, fetch: 0, ioread: 0)
관련 내용 메뉴얼 URL : https://www.cubrid.org/manual/ko/9.3.0/sql/query/select.html#values
