Study/DataBase
[데이터베이스] 오라클 JOB/ 페이징(ROWNUM) 연습문제 / SQL 튜닝(힌트)
minulbora
2024. 1. 4. 17:43
행 삭제하는 프로시저 작성하기
--EMP TABLE 에 EMPNO가 3000번 이상인 직원을 삭제하는 프로시저 작성
--프로시저명 EX_EMP_DEL
DELETE FROM EMP WHERE EMPNO>3000;
CREATE OR REPLACE PROCEDURE EX_EMP_DEL
IS
BEGIN
DELETE FROM EMP WHERE EMPNO>'3000';
--SQL%ROWCOUNT; 처리건수
DBMS_OUTPUT.PUT_LINE ('삭제 건수 : '|| SQL%ROWCOUNT || '건 입니다');
COMMIT;
END;
/
EXEC EX_EMP_DEL;
...ROWCOUNT -> 처리 건수
오라클 JOB 등록하기
--오라클 JOB 등록
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
JOB_NAME => 'EX_JOB', --짓는 이름
JOB_TYPE => 'STORED_PROCEDURE',
JOB_ACTION => 'E_JOB_EMP',
REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=1', --1분에 한 번
COMMENTS=>'EMP_TEMP추가 객체'
);
END;
/
DELETE FROM EMP_TMP;
COMMIT;
SELECT * FROM EMP_TMP;
--잡 객체 실행 로그 확인하기 (잘 돌고 있는지 확인해야)
SELECT * FROM USER_SCHEDULER_JOB_LOG ORDER BY LOG_DATE DESC;
SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS ORDER BY LOG_DATE DESC;
SELECT * FROM USER_JOBS;
-- 잡 실행과 중지하는 방법
BEGIN
--DBMS_SCHEDULER.ENABLE('EX_JOB'); --잡 실행
DBMS_SCHEDULER.DISABLE('EX_JOB'); --잡 중지
END;
/단 매개변수 있으면 안됨. (하드코딩하면 되긴 함)
-- 잡 실행과 중지하는 방법
BEGIN
--DBMS_SCHEDULER.ENABLE('EX_JOB'); --잡 실행
--DBMS_SCHEDULER.DISABLE('EX_JOB'); --잡 중지
--잡 삭제
DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'EX_JOB',
FORCE => FALSE);
END;
/
SALMONTH 를 하나씩 높이면서 급여 정보를 추가하는 JOB 생성
(SAL(급여) 에 10씩 추가, COMM(보너스)에 5씩 추가)
--급여테이블 데이터 생성용
SELECT DISTINCT SALMONTH FROM SALARY;
UPDATE SALARY
SET SALMONTH = '202202'
WHERE SALMONTH = '202312';
COMMIT;
--프로시저 커서 사용
CREATE OR REPLACE PROCEDURE PC_CURSOR
IS
BEGIN
DECLARE
CURSOR cursor_Salary
IS SELECT * --SALMONTH, EMPNO, SAL, COMM
FROM SALARY
WHERE SALMONTH = (SELECT MAX(SALMONTH)
FROM SALARY);
--변수 정의
V_PRODUCT SALARY%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('프로시저 커서 시작');
OPEN cursor_Salary;
LOOP
FETCH cursor_Salary INTO V_PRODUCT;
EXIT WHEN cursor_Salary%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('SALMONTH: '||V_PRODUCT.SALMONTH);
INSERT INTO SALARY
VALUES (TO_CHAR(ADD_MONTHS (TO_DATE(V_PRODUCT.SALMONTH,'YYYYMM'),1),'YYYYMM'),
V_PRODUCT.EMPNO, V_PRODUCT.SAL+10, V_PRODUCT.COMM+5);
END LOOP;
COMMIT;
IF cursor_Salary%ISOPEN THEN
CLOSE cursor_Salary;
END IF;
END;
END;
/
SELECT SALMONTH, EMPNO, SAL, COMM
FROM SALARY
WHERE SALMONTH = (SELECT MAX(SALMONTH)
FROM SALARY);
SELECT TO_CHAR(ADD_MONTHS (TO_DATE('202202','YYYYMM'),1),'YYYYMM')
FROM DUAL;
EXEC PC_CURSOR;
--스케줄러 등록 : 잡명은 EX_SALARY, 1분에 한 번씩 실행하도록
--오라클 잡 등록
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
JOB_NAME => 'EX_SALARY',
JOB_TYPE => 'STORED_PROCEDURE',
JOB_ACTION => 'PC_CURSOR',
REPEAT_INTERVAL => 'FREQ=MINUTELY;INTERVAL=1', --1분에 1번
COMMENTS => '급여추가 객체'
);
END;
/
--잡 객체 실행 로그 확인하기
SELECT * FROM USER_SCHEDULER_JOBS WHERE JOB_NAME = 'EX_SALARY';
SELECT * FROM USER_SCHEDULER_JOB_LOG WHERE JOB_NAME = 'EX_SALARY' ORDER BY LOG_DATE DESC;
SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'EX_SALARY' ORDER BY LOG_DATE DESC;
-- 잡 실행과 중지하는 방법
BEGIN
DBMS_SCHEDULER.ENABLE('EX_SALARY'); --잡 실행
--DBMS_SCHEDULER.DISABLE('EX_SALARY'); --잡 중지
--잡 삭제
--DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'EX_SALARY',
-- FORCE => FALSE);
END;
/
SELECT MAX(SALMONTH) FROM SALARY;
페이징 처리하기
데이터게 SELECT 되어서 가져오는 시점에 론? 업?(ROWNUM) 이 1부터 매겨진다.
GROUP BY 든지 ORDER BY든지 DBMS 영역에서 되는 것. 그래서 자동으로 론업이 된 게 ORDER BY 하면서 다시 섞임. 그러므로 인라인 뷰 사용해서 다시 론업 해줘야
페이징 연습문제 (혼자 풀어보기)
--일반 직원(상사 제외)들의 급여 중, 8000만원 미만인 급여 정보를 조회하는 쿼리 작성 --조회 항목 : SALMONTH, EMPNO, SAL, COMM --단, 최종 조회시 정렬 방법은 SALMONTH가 가장 최근이면서 급여가 높은 순으로 정렬 -- 페이징 처리를 통해 5번 행부터 10번 행 자료를 조회)
SELECT SALMONTH, EMPNO, SAL, COMM
FROM (
SELECT ROWNUM NUM, A.*
FROM (
SELECT SALMONTH, EMPNO, SAL, COMM
FROM SALARY
WHERE EMPNO NOT IN (SELECT A.EMPNO
FROM EMP A, EMP B
WHERE A.EMPNO = B.MGR)
AND SALMONTH >= '202201'
AND SALMONTH <= '202712'
AND SAL < 8000
ORDER BY SALMONTH DESC, SAL DESC ) A)
WHERE NUM >=5
AND NUM <=10;
❗가장 바깥에 SELECT 로 다시 한 번 감싸줘야 하는 이유...
ROWNUM의 성질 때문임... 1페이지부터 무조건 매겨지는 임시 번호이기 때문에, 2페이지부터 확인이 불가하다. (NUM이 1페이지가 아닌 다른 페이지부터 시작할 수 없음. ) 따라서 다시 한 번 감싸주는 거임...
https://turing0809.tistory.com/48
ROWNUM 이해하기
1. ROWNUM 이란? ROWNUM은 SELECT 해온 데이터에 일련번호를 붙이는 것이다. 테이블이나, 특정 집합에서 원하는 만큼의 행만 가져오고 싶을 때, 행의 개수를 제한하는 용도로 사용한다. 예) 전체 행에서
turing0809.tistory.com
연습문제 2
-- 문제 2 -- 직원 정보 중, 2021년 3월 1일 ~ 2023년 1월 전까지 입사한 직원 중, 2023년 12월 급여액이 평균보다 작거나 같고, 보너스는 평균보다 크거나 같은 직원에 대한 정보를 조회하는 쿼리 작성.
--조회 항목: 직원번호, 직원명, 급여액, 보너스 --단, 급여액 평균과 보너스 평규에 대한 서브쿼리를 사용하고, 소수점 이하는 반올림. 급여액과 보너스 평균 금액 조회 쿼리에서 다중행 서브쿼리로 이용하여 조회함. 직원의 급여가 높고, 보너스가 낮은 순으로 조회하며, 상위 5명의 직원 정보를 조회함.
SELECT EMPNO, ENAME, SAL, COMM
FROM (
SELECT ROWNUM RN, E.EMPNO, E.ENAME, S.SAL, S.COMM, S.SALMONTH
FROM EMP E, SALARY S
WHERE E.EMPNO=S.EMPNO
AND E.HIREDATE >= TO_DATE('20210301','YYYYMMDD')
AND HIREDATE< TO_DATE('20230101','YYYYMMDD')
AND S.SALMONTH = '202312'
AND S.SAL>=(SELECT ROUND(AVG(SAL),0)
FROM SALARY --7185
WHERE SALMONTH ='202312'
AND EMPNO IN (SELECT EMPNO FROM EMP WHERE HIREDATE >= TO_DATE('20210301','YYYYMMDD')
AND HIREDATE< TO_DATE('20230101','YYYYMMDD')))
AND S.COMM >= (SELECT ROUND(AVG(COMM),0) --605
FROM SALARY
WHERE SALMONTH ='202312'
AND EMPNO IN (SELECT EMPNO FROM EMP WHERE HIREDATE >= TO_DATE('20210301','YYYYMMDD')
AND HIREDATE< TO_DATE('20230101','YYYYMMDD')))
ORDER BY SAL DESC, COMM ASC)
WHERE RN <=5;
틀린 점: 평균을 구할 때에도 HIREDATE를 고려해야 하는데 그걸 빼먹음 (근데 문제 이해를 잘못한듯)
+) 위에서 설명했던 ORDERBY 때문에 ROWNUM 이 달라짐...
한번 더 인라인으로묶어야
그래서 진짜 답은 아래
SELECT EMPNO, ENAME, SAL, COMM
FROM (
SELECT ROWNUM RN,EMPNO, ENAME, SAL, COMM
FROM (
SELECT E.EMPNO, E.ENAME, S.SAL, S.COMM, S.SALMONTH
FROM EMP E, SALARY S
WHERE E.EMPNO=S.EMPNO
AND E.HIREDATE >= TO_DATE('20210301','YYYYMMDD')
AND HIREDATE< TO_DATE('20230101','YYYYMMDD')
AND S.SALMONTH = '202312'
AND S.SAL>=(SELECT ROUND(AVG(SAL),0)
FROM SALARY --7185
WHERE SALMONTH ='202312'
AND EMPNO IN (SELECT EMPNO FROM EMP WHERE HIREDATE >= TO_DATE('20210301','YYYYMMDD')
AND HIREDATE< TO_DATE('20230101','YYYYMMDD')))
AND S.COMM >= (SELECT ROUND(AVG(COMM),0) --605
FROM SALARY
WHERE SALMONTH ='202312'
AND EMPNO IN (SELECT EMPNO FROM EMP WHERE HIREDATE >= TO_DATE('20210301','YYYYMMDD')
AND HIREDATE< TO_DATE('20230101','YYYYMMDD')))
ORDER BY S.SAL DESC, S.COMM ASC))
WHERE RN <=5;다음문제
/*
문제 3
입사일이 2021년 1월 1일 이후인 입사자 중, 팀장을 제외한 팀원들만 조회하는 쿼리 작성
조회 항목: 직원번호, 직원명, 직급, 입사일(YYYY.MM.DD)
단, 가장 최근 입사자를 기준으로 조회하며, 최근 3명만 조회하도록 작성
*/
SELECT EMPNO 직원번호, ENAME 이름, JOB 직급, TO_CHAR(HIREDATE,'YYYY.MM.DD') 입사일
FROM ( SELECT ROWNUM RN, EMPNO, ENAME, JOB, HIREDATE
FROM (SELECT DISTINCT EMPNO, ENAME, JOB, HIREDATE
FROM EMP
WHERE HIREDATE >= TO_DATE('20210101','YYYYMMDD')
AND EMPNO NOT IN (SELECT DISTINCT A.EMPNO
FROM EMP A, EMP B
WHERE A.EMPNO = B.MGR)
ORDER BY HIREDATE DESC))
WHERE RN<=3
AND RN>=1;SQL 튜닝 (힌트)
풀스캔이 더 빠른 경우가 있다는 점을 유의해야 함.
지금은 범위조건이 의미가 없으므로 지금처럼 아무리 데이터가 많아도 풀스캔을 하는 게 더 빠른 경우가 있다. (지금처럼 PK와 아무런 연관이 없는 조건인 경우) 분포도가 5:5 일 경우에도 인덱스를 사용하지 않는 것이 좋다. (EX. 성별)
--SQL 튜닝 힌트(HINT) 사용
--*참고
--PK_SALARY
--EX_EMPNO
SELECT * FROM USER_INDEXES;
ALTER INDEX PK_SALARY REBUILD;
ALTER INDEX EX_EMPNO REBUILD;
--SQL 튜닝은 왜 하는걸까? 옵티마이저의 자동 선택을 수동으로 지정, 수정하기 위해 사용
SELECT * --0.064초 (나는 0.108초)
FROM SALARY
WHERE SAL >= 5000;
--오라클 힌트 사용 --0.071 (SKSMS 0.139초)
SELECT /*+ INDEX(S PK_SALARY) */*
FROM SALARY S
WHERE SAL>=5000;
SELECT /*+ INDEX(S EX_EMPNO) */* --0.074(0.134초)
FROM SALARY S
WHERE SAL>=5000;
--지금은 범위조건이 의미가 없으므로 지금처럼 아무리 데이터가 많아도 풀스캔을 하는 게 더 빠른 경우가 있다. (지금처럼 PK와 아무런 연관이 없는 조건인 경우) 분포도가 5:5 일 경우에도 인덱스를 사용하지 않는 것이 좋다. (EX. 성별)
--====================================================================--
--비교연산자인 경우
SELECT * --0.057초 (나는 0.183초), 1666건, FULL, COST-3
FROM SALARY
WHERE SALMONTH >= '202312';
--오라클 힌트 사용 --0.071 (SKSMS 0.053초) COST - 12
SELECT /*+ INDEX(S PK_SALARY) */*
FROM SALARY S
WHERE SALMONTH >= '202312';
SELECT /*+ INDEX(S EX_EMPNO) */* --0.183(0.057초) COST-8
FROM SALARY S
WHERE SALMONTH >= '202312';
--==================================================================--
--같다
SELECT * --0.037초 (나는 0.003초) COST 3
FROM SALARY
WHERE SALMONTH = '202312';
--오라클 힌트 사용 --0.071 (SKSMS 0.002초) COST 5
SELECT /*+ INDEX(S PK_SALARY) */*
FROM SALARY S
WHERE SALMONTH = '202312';
SELECT /*+ INDEX(S EX_EMPNO) */* --0.074(0.007초) COST 12
FROM SALARY S
WHERE SALMONTH = '202312';
왜 쓰냐면... 속도를 빠르게 하게 만들기 위해.
웹은 속도가 중요함.
늦어도 0.1SEC?