WITH TBL AS (
	SELECT 'A'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA1'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'B'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA2'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'C'  AS KEY_COL, 'C'  AS GROUP_COL, 0    AS GROUP_ORDER, 'DATA3'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'C1' AS KEY_COL, 'C'  AS GROUP_COL, 1    AS GROUP_ORDER, 'DATA4'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'C2' AS KEY_COL, 'C'  AS GROUP_COL, 2    AS GROUP_ORDER, 'DATA5'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'D'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA6'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'E'  AS KEY_COL, 'E'  AS GROUP_COL, 0    AS GROUP_ORDER, 'DATA7'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'E1' AS KEY_COL, 'E'  AS GROUP_COL, 1    AS GROUP_ORDER, 'DATA8'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'F'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA9'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'G'  AS KEY_COL, 'G'  AS GROUP_COL, 0    AS GROUP_ORDER, 'DATA10' AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'G1' AS KEY_COL, 'G'  AS GROUP_COL, 1    AS GROUP_ORDER, 'DATA11' AS PRINT_DATA FROM DUAL 
)
SELECT 	KEY_COL, 
        GROUP_COL, 
        GROUP_ORDER, 
        PRINT_DATA
FROM    TBL;

위의 데이터가 있다고 가정했을때

KEY_COL은 PK이고

다건일 경우는 첫번째 데이터(ex: C, E, G)의 키값을 GROUP_COL로 가지며 GROUP_ORDER로 순번이 정해집니다.

단건일 경우는 GROUP_COL과 GROUP_ORDER값이 NULL로 들어갑니다.

추가 인덱스는 GROUP_COL컬럼 인덱스가 걸려있습니다.

컬럼의 데이터는 편의상 알파벳으로 표현했으며 실제 데이터는 난수값이 들어가 KEY로 순서를 보장할 수 없습니다.

IN 조건에 들어갈 항목은 최소 1건에서 최대 100건 내외로 예상됩니다.

오라클 버전은 11g 이하 인걸로 예상됩니다.

WITH TBL AS (
	SELECT 'A'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA1'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'B'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA2'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'C'  AS KEY_COL, 'C'  AS GROUP_COL, 0    AS GROUP_ORDER, 'DATA3'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'C1' AS KEY_COL, 'C'  AS GROUP_COL, 1    AS GROUP_ORDER, 'DATA4'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'C2' AS KEY_COL, 'C'  AS GROUP_COL, 2    AS GROUP_ORDER, 'DATA5'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'D'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA6'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'E'  AS KEY_COL, 'E'  AS GROUP_COL, 0    AS GROUP_ORDER, 'DATA7'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'E1' AS KEY_COL, 'E'  AS GROUP_COL, 1    AS GROUP_ORDER, 'DATA8'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'F'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA9'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'G'  AS KEY_COL, 'G'  AS GROUP_COL, 0    AS GROUP_ORDER, 'DATA10' AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'G1' AS KEY_COL, 'G'  AS GROUP_COL, 1    AS GROUP_ORDER, 'DATA11' AS PRINT_DATA FROM DUAL 
)
SELECT 	KEY_COL, GROUP_COL, GROUP_ORDER, PRINT_DATA
FROM	TBL
WHERE	KEY_COL IN ('G', 'F', 'C', 'B')
OR      GROUP_COL IN ('G', 'F', 'C', 'B');

데이터는 같고 위 쿼리처럼 IN 절로 데이터를 조회할때 IN조건에 들어간 항목의 그룹별로 정렬을 하고 싶습니다.

아래는 원하는 결과값입니다.

WITH TBL AS (
	SELECT 'A'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA1'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'B'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA2'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'C'  AS KEY_COL, 'C'  AS GROUP_COL, 0    AS GROUP_ORDER, 'DATA3'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'C1' AS KEY_COL, 'C'  AS GROUP_COL, 1    AS GROUP_ORDER, 'DATA4'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'C2' AS KEY_COL, 'C'  AS GROUP_COL, 2    AS GROUP_ORDER, 'DATA5'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'D'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA6'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'E'  AS KEY_COL, 'E'  AS GROUP_COL, 0    AS GROUP_ORDER, 'DATA7'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'E1' AS KEY_COL, 'E'  AS GROUP_COL, 1    AS GROUP_ORDER, 'DATA8'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'F'  AS KEY_COL, NULL AS GROUP_COL, NULL AS GROUP_ORDER, 'DATA9'  AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'G'  AS KEY_COL, 'G'  AS GROUP_COL, 0    AS GROUP_ORDER, 'DATA10' AS PRINT_DATA FROM DUAL UNION ALL
	SELECT 'G1' AS KEY_COL, 'G'  AS GROUP_COL, 1    AS GROUP_ORDER, 'DATA11' AS PRINT_DATA FROM DUAL 
)
SELECT 	KEY_COL, GROUP_COL, GROUP_ORDER, PRINT_DATA
FROM	(
			SELECT  KEY_COL, GROUP_COL, GROUP_ORDER, PRINT_DATA
			FROM	TBL
			WHERE	KEY_COL = 'G'
			OR      GROUP_COL = 'G'
			ORDER BY GROUP_ORDER
		)
		
UNION ALL

SELECT 	KEY_COL, GROUP_COL, GROUP_ORDER, PRINT_DATA
FROM	(
			SELECT  KEY_COL, GROUP_COL, GROUP_ORDER, PRINT_DATA
			FROM	TBL
			WHERE	KEY_COL = 'F'
			OR      GROUP_COL = 'F'
			ORDER BY GROUP_ORDER
		)
		
UNION ALL

SELECT 	KEY_COL, GROUP_COL, GROUP_ORDER, PRINT_DATA
FROM	(
			SELECT  KEY_COL, GROUP_COL, GROUP_ORDER, PRINT_DATA
			FROM	TBL
			WHERE	KEY_COL = 'C'
			OR      GROUP_COL = 'C'
			ORDER BY GROUP_ORDER
		)
		
UNION ALL

SELECT 	KEY_COL, GROUP_COL, GROUP_ORDER, PRINT_DATA
FROM	(
			SELECT  KEY_COL, GROUP_COL, GROUP_ORDER, PRINT_DATA
			FROM	TBL
			WHERE	KEY_COL = 'B'
			OR      GROUP_COL = 'B'
			ORDER BY GROUP_ORDER
		);

위와같이 IN절에 들어간 G, F, C, B의 그룹 순서대로 데이터를 정렬해서 조회하고싶은대

검색해본 결과

ORDER BY절에 FIELD(컬럼,'G', 'F', 'C', 'B')

위와 같이 작성하면 된다고 했는대 컬럼 조건이 두개인경우는 찾아볼 수 없어서 질문드립니다.

최악의 경우는 위처럼 UNION ALL로 하면 되긴하는대 혹시 좀더 성능상 괜찮은 방법 아시는 분 계시면 조언 부탁드립니다.

감사합니다.