데이터베이스 질문과 답변

오라클 in 조건에 들어가는 순서대로 정렬 질문입니다. 0 12 13,566

by 2bono [SQL Query] ORACLE 오라클 in order by 정렬 [2019.02.19 16:22:22]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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 이하 인걸로 예상됩니다.

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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조건에 들어간 항목의 그룹별로 정렬을 하고 싶습니다.

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

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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
        );

 

KEY_COL GROUP_COL GROUP_ORDER PRINT_DATA
G G 0 DATA10
G1 G 1 DATA11
F NULL NULL DATA9
C C 0 DATA3
C1 C 1 DATA4
C2 C 2 DATA5
B NULL NULL DATA2

 

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

검색해본 결과

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

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

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

감사합니다.

 

by 마농 [2019.02.19 16:45:07]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
WITH tbl AS
(
SELECT 'A' key_col, NULL group_col, NULL group_order, 'DATA1' print_data FROM dual
UNION ALL SELECT 'B' , NULL, NULL, 'DATA2'  FROM dual
UNION ALL SELECT 'C' , 'C' , 0   , 'DATA3'  FROM dual
UNION ALL SELECT 'C1', 'C' , 1   , 'DATA4'  FROM dual
UNION ALL SELECT 'C2', 'C' , 2   , 'DATA5'  FROM dual
UNION ALL SELECT 'D' , NULL, NULL, 'DATA6'  FROM dual
UNION ALL SELECT 'E' , 'E' , 0   , 'DATA7'  FROM dual
UNION ALL SELECT 'E1', 'E' , 1   , 'DATA8'  FROM dual
UNION ALL SELECT 'F' , NULL, NULL, 'DATA9'  FROM dual
UNION ALL SELECT 'G' , 'G' , 0   , 'DATA10' FROM dual
UNION ALL SELECT 'G1', 'G' , 1   , 'DATA11' FROM dual
)
SELECT *
  FROM tbl
 WHERE key_col   IN ('G', 'F', 'C', 'B')
    OR group_col IN ('G', 'F', 'C', 'B')
 ORDER BY NVL(INSTR(',G,F,C,B,', group_col), INSTR(',G,F,C,B,', key_col))
     , group_order
;

 

by 2bono [2019.02.19 16:47:25]

고정자리수 랜덤문자열입니다.

조건의 이유는 G, F, C, B와 관련된 그룹 전체를 가져오기위해 KEY_COL과 GROUP_COL로 조회했습니다.

그룹이아닌 단일건의경우 GROUP_COL은 NULL이기때문에 OR 조건으로 둘다걸어준 상황입니다.

by 2bono [2019.02.19 16:50:53]

답변갑사드립니다.

완벽히 해결됬습니다.

by 마농 [2019.02.19 16:58:25]
?
1
2
3
4
5
6
SELECT *
  FROM tbl a
 WHERE NVL(group_col, key_col) IN ('G', 'F', 'C', 'B')
 ORDER BY INSTR('G,F,C,B', NVL(group_col, key_col))
     , group_order
;

 

by 2bono [2019.02.19 17:06:16]

추가 답변 감사드립니다!

by 마농 [2019.02.19 17:09:34]

group_col 에 인덱스가 존재하니
실제로 널이 아닌 key_col 로 관리한다면?
좋은 성능이 예상됩니다.
아니면 NVL(group_col, key_col) 을 이용해 함수기반인덱스(FBI)를 만들어도 되겠네요.

by 2bono [2019.02.19 17:11:07]

실제 GROUP_COL이 NULL로 관리가 될경우에는 처음 작성해주신 쿼리가 COAST가 더 적게 나오는대

실제 GROUP_COL에 NULL데이터가아닌 KEY_COL데이터로 무조건 넣게되면 성능향상이 예상된다.

라고 이해했는대 잘 이해한게 맞나요??

by 2bono [2019.02.19 17:17:01]

함수기반 인덱스라는게 있었네요...

공부를 좀더 해봐야겠습니다!

답변감사드립니다.

by 마농 [2019.02.19 17:43:04]

1. 원본의 OR 조건은
 - 인덱스가 두개로 나뉘어져 각각의 인덱스를 한꺼번에 이용하기 어렵습니다.
 - 인덱스 조인이나, 인덱스 컴바인 등의 조인 기술이 있긴 하지만
 - 인덱스 안타는 것보다야 낫겠지만 단순 인덱스 타는 것에 비하면 효율은 떨어지죠
2. NVL 의 경우는
 - 컬럼을 가공하여 인덱스를 이용할 수 없습니다.
 - 이에 2가지 대안을 제시한 것입니다.
3. 대응방안
 - NULL 을 채워서 관리하면 NVL 을 안써도 되니 인덱스를 이용할 수 있구요.
 - FBI 를 만들면 NVL 을 사용해도 인덱스 탈 수 있습니다.

by 2bono [2019.02.20 09:34:15]

정리해주셔서 바로 이해가 됬습니다.

답변 감사드립니다.

by 정재홍 [2019.02.20 16:26:18]

SELECT * FROM tbl a

 WHERE NVL(group_col, key_col) IN ('G', 'F', 'C', 'B')

 ORDER BY NVL(group_col, key_col) DESC

     , group_order

by 2bono [2019.02.20 17:50:56]

답변 감사드립니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입