CUBRID | FAQ & TIP - VALUES 절 활용하기

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)

VALUES 절 활용하기