데이터베이스 질문과 답변
by 신입0년차 [SQL Query] [2023.02.21 16:18:34]
이렇게 쿼리를 한이유는
mybatis 환경에 넘긴 파라미터의 사람들의 성명, 생년월일, 전화번호로 3개의 테이블에 각각 찾아서(사람들의 직분? 같은걸로 테이블이 구분되어있어요 학생인지,직원인지,외부직원인지)
이사람의 USER_ID 를 찾고
또 하나의 A라는 테이블로(키가 USER_ID) 특정코드로 이사람이 어떤교육을 들었나 찾는 로직인데
키는 USER_ID 하나인데 이 비교해야하는 자료들이 USER_ID는 없는경우가 있는데 성명, 생년월일은 있는경우가 있어서 각3개의테이블의 성명,생년월일,휴대폰번호로
USER_ID를 찾아서 A테이블에 이 USER_ID로 교육을 들은사람이 있는지 확인하는 로직인데요
처음에는 3개의테이블에 3개의 파라미터를 비교해야하니까
foreach로 3개의 테이블 각각 IN절로 넘겼었습니다.
그런데 고객쪽에서 점점 요구사항이 많아지자 기존에 쿼리속도도 느린데 더더느려져서
1000건 안되는 사람들의 파라미터로 조회하니 웹서버에서 너무오래걸려서 쿼리를 처리하지않아서... 변경했는데요
처음보다는 시간이 줄여서 1분30~2분안에 동작이 되긴하는데요 이거마저 속도줄일수 없나싶어서 확인하는도중
왜속도가 느리지 싶은부분이 있어서 질문드립니다!
1700건으로 조회했는데
SELECT '' AS NAME
, '' AS BIRDT
, '' AS HNPH_NO
, '' AS CODE
FROM DUAL
UNION ALL
SELECT REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 1) AS NAME
, REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 2) AS BIRDT
, REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 3) AS HNPH_NO
, REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 4) AS CODE
FROM DUAL
UNION ALL
.
.
.
SELECT REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 1) AS NAME
, REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 2) AS BIRDT
, REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 3) AS HNPH_NO
, REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 4) AS CODE
FROM DUAL
제일안에 쿼리같은경우 0.5~0.7 초내외 로 나옵니다.
SELECT A.NAME AS NAME
, A.BIRDT AS BIRDT
, A.HNPH_NO AS HNPH_NO
, A.CODE AS CODE
FROM ( SELECT '' AS NAME
, '' AS BIRDT
, '' AS HNPH_NO
, '' AS CODE
FROM DUAL
UNION ALL
SELECT REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 1) AS NAME
, REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 2) AS BIRDT
, REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 3) AS HNPH_NO
, REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 4) AS CODE
FROM DUAL
UNION ALL
.
.
.
SELECT REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 1) AS NAME
, REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 2) AS BIRDT
, REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 3) AS HNPH_NO
, REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 4) AS CODE
FROM DUAL ) A
WHERE A.NAME IS NOT NULL
GROUP BY A.NAME
, A.BIRDT
, A.HNPH_NO
, A.LEGAL_EDU_CD
두번째 쿼리같은경우도 0.5~0.7 초내외로 조회되요...
WITH NAMES AS
(
SELECT A.NAME AS NAME
, A.BIRDT AS BIRDT
, A.HNPH_NO AS HNPH_NO
, A.CODE AS CODE
FROM ( SELECT '' AS NAME
, '' AS BIRDT
, '' AS HNPH_NO
, '' AS CODE
FROM DUAL
UNION ALL
SELECT REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 1) AS NAME
, REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 2) AS BIRDT
, REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 3) AS HNPH_NO
, REGEXP_SUBSTR('이름/생년월일/휴대폰번호/특정코드','[^/]+', 1, 4) AS CODE
FROM DUAL
UNION ALL
.
.
.
SELECT REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 1) AS NAME
, REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 2) AS BIRDT
, REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 3) AS HNPH_NO
, REGEXP_SUBSTR('이름1700/생년월일/휴대폰번호/특정코드','[^/]+', 1, 4) AS CODE
FROM DUAL ) A
WHERE A.NAME IS NOT NULL
GROUP BY A.NAME
, A.BIRDT
, A.HNPH_NO
, A.LEGAL_EDU_CD
)
SELECT *
FROM NAMES
근데 WITH로 정의하고 단순 임시테이블 조회를 하는데 16초넘게 걸려서요....
* 말고 NAME으로 컬럼명 지정해줘도 마찬가지라서
안에 쿼리자체는 빨리 되는데 왜 WITH절로 정의한다음 SELECT 하면 이리 오래걸리는지 이유를 모르겠습니다.
WITH에 관해 검색해도 나오지않고 거기에 대해 저도 아직 지식이 별로없는지라... 해결을 못한채 반나절 이것저것 해보고
알아보다 안되서 질문드립니다.
답변 부탁드립니다.
감사합니다.
1. 자른 뒤에 유니온 하지 말고 -> 유니온 한 후에 자르기
- 파라미터 하나당 4번 반복 사용 -> 1번 사용으로 줄이기
- 쿼리 단순화 : regxep (1700건 * 4번 사용 -> 4번만 사용)
2. 정규식 함수를 일반 문자함수로 바꾸기
- 정규식이 좀 무겁습니다.
3. GROUP BY 가 필요한가?
4. 이 쿼리만으로 느린것인지?
- 이 쿼리를 이용해 다른 테이블과 조인하는게 느린 것인지?
감사합니다.!
현재 이거 작업가능한 원격컴퓨터를 회사 다른분이 쓰고계셔서 되는대로 빨리 테스트해보고 말씀드릴게요
1. 해보고말씀드리겠습니다.
2. 변경해보겠습니다.
3. 이게 교육을 받은 데이터들을 담당자들이 취합해서 엑셀로 받은다음 엑셀파일을 업로드전에 직번 또는 학번이 없어서 이사람의 개인정보를 가지고 학번 또는 직번을 찾아내기위한 쿼리라서
(학번 또는 직번이 없어서 에러리스트로 떨어진상태에서 사용자 ID찾기 버튼으로 ID찾아서 정상적으로 업로드 해주는 로직)
해당파일에 4가지 정보말고도 다른 교육관련 정보가있는데요 같은 사람이 교육을 여러번 이수할수있어서 같은 이름,생년월일,휴대폰번호,특정코드는 같고 수료일자만 다른 데이터가 2~3건 있는경우가있어서
사실상 1700건 union all 하는데 실질적인 사람수는 1530 건이나 1400건이라던가 이래서요 밑에서 NAMES가지고 조인할때 건수줄일려고 했습니다.
생각해보니 이부분도 화면단에서 파라미터 던지기전 줄이는게 더빠를거같네요 화면에서부터 중복 제외해서 파라미터 던져서 쓸데없는 group by부분 빼겠습니다.
4. 이부분은 제가 튜닝쪽을 몰라서 .. 실행계획을 보긴했는데 잘모르겠어서 하나하나 속도 체크할려고 보다가 올린글의 쿼리부분이 16초걸리길래 왜그런가 싶어서 질문했습니다.
WITH names AS
(
SELECT REGEXP_SUBSTR(p, '[^/]+', 1, 1) name
, REGEXP_SUBSTR(p, '[^/]+', 1, 2) birdt
, REGEXP_SUBSTR(p, '[^/]+', 1, 3) hnph_no
, REGEXP_SUBSTR(p, '[^/]+', 1, 4) code
FROM (SELECT '' p FROM dual WHERE 1=2
UNION ALL SELECT '이름/생년월일/휴대폰번호/특정코드' FROM dual
---
UNION ALL SELECT '이름1700/생년월일/휴대폰번호/특정코드' FROM dual
)
GROUP BY p
)
SELECT *
FROM names
;
WITH names AS
(
SELECT SUBSTR(p, INSTR('/'||p, '/', 1, 1), INSTR(p||'/', '/', 1, 1) - INSTR('/'||p, '/', 1, 1)) name
, SUBSTR(p, INSTR('/'||p, '/', 1, 2), INSTR(p||'/', '/', 1, 2) - INSTR('/'||p, '/', 1, 2)) birdt
, SUBSTR(p, INSTR('/'||p, '/', 1, 3), INSTR(p||'/', '/', 1, 3) - INSTR('/'||p, '/', 1, 3)) hnph_no
, SUBSTR(p, INSTR('/'||p, '/', 1, 4), INSTR(p||'/', '/', 1, 4) - INSTR('/'||p, '/', 1, 4)) code
FROM (SELECT '' p FROM dual WHERE 1=2
UNION ALL SELECT '이름/생년월일/휴대폰번호/특정코드' FROM dual
-- ...
UNION ALL SELECT '이름1700/생년월일/휴대폰번호/특정코드' FROM dual
)
GROUP BY p
)
SELECT *
FROM names
;
with 절이 materialized 로 실행되면 temp 를 사용하게 될 수 있어서 느릴 수 있습니다.
with 테이블 as ( select /*+ INLIN */ ... ). 으로 힌트를 주고 테스트 해 보세요.
) 버튼을 클릭하여 작성 하시면 됩니다.