중급 (4-2)서브쿼리2, VIEW, inline view, PLAN보기, view merging, 스칼라서브쿼리, ROWNUM, 실행계획 해석, 캐싱&튜닝
---
https://youtube.com/playlist?list=PLyQR2NzLKOCbOYNJHKpCax4ija38yCqBN
이거 보는 중
---
(34)
https://youtu.be/MxwGWiuN3H8 - 무늬만 테이블? 데이터베이스 VIEW 생성, 변경, 삭제
inline view 설명 전에 view에 대해 먼저
쓰임새
1)공통모듈처럼 사용하기 위해서. VIEW를 하나 생성해놓으면 이 VIEW를 참조하는 애플리케이션단의 소스를 수정하지 않고도 쿼리를 수정할 수가 있다. 예를 들어서 어떤 쿼리에 대한 조건이 변경이 됐다거나, 컬럼이 추가됐다거나 라고 할 때 애플리케이션 단에 소스 수정 없이 VIEW만 수정을 해서 변경이 가능하기 때문에 공통모듈처럼 사용할 수가 있다
2)간단한 QUERY보다는 복잡한 QUERY를 뷰로 생성한 다음, QUERY에서 간단하게 VIEW 명으로만 표시를 해주면서 쓰면 소스단에 있는 쿼리가 매우 간결해짐
3)★보안상의 이유
타부서에 VIEW 생성을 요청한 적이 있어요. 쇼핑몰 구축할 때 쇼핑몰에서 임직원 정보를 연동하는게 필요했다. 쇼핑몰 회원 중 임직원인 사람들한테 할인 혜택을 주는 프로모션을 할 것이기 때문에 임직원 정보 테이블을 관리하고 있는 타부서에 이 테이블을 연동할 수 있게 VIEW를 열어주세요 라고 요청함.
그럼 그 사람은 임직원 테이블에 있는 모든 컬럼들을 열어주지 않고, (보안상 이슈)정말로 필요로 하는 컬럼들만 간추려서 VIEW로 생성한 다음 그것만 열어주면 된다.
---
VIEW는 데이터베이스의 SELECT문을 저장한 OBJECT
쿼리문에서 테이블처럼 쓰인다
예를 들며 설명
이걸로 할거야
뷰 생성
CREATE OR REPLACE VIEW ((뷰이름)) AS ((여기에SELECT문))
만들어짐.
생성이 된 VIEW에 대해 스키마를 한번 봐보면
컨트롤 누르면 이렇게 클릭이 됐었나
컬럼들,
SQL탭을 보면 내가 아까 생성한 VIEW에 대한 설명들이 보임
이런 query가 view로 생성이 돼 있어요~라고 보여주는 것
모든 컬럼으로 생성했는데, 당연히 컬럼을 한정지어 줄 수 있다
만들고, select해봄
VIEW를 테이블 여러개로 만들 수 있어 JOIN을 해서
처음에 JOIN문 먼저 만들어놓고
SELECT~ AND전까지 써서 쳐보고 뭐 나오나 보고, 그 다음 AND 쳐서 나오는거 보고
그 앞에 CREATE OR REPLACE VIEW ~ AS 치는 방식으로 만듦
VIEW삭제
---
댓글 :
Q.궁금한점이 있습니다! 뷰는 출력위주로만 사용하고 수정을 하지말라고 배웠는데요 현업에서는 뷰를 수정하나요?
A.음.. 현업은 보통 요구사항을 내는 사람들을 의미하는데.. 현업이 뷰에 대해 수정을 요청하면 dba나 담당 개발자가 수정을 하지요~
=>수정 가능
---
(9)
https://youtu.be/efkQFP0wj08 - 인라인 뷰만 잘써도 당신은 이미 고수!
(4)의 댓글
Q.(인라인 뷰) from 절에 서브쿼리가 들어간 경우 alias를 해줘야 하고, join 을 하게 되면 A , B table을 join 할때 A, B table 둘다 alias를 해줘야 한다고 알고 있습니다. 이때 A 테이블이 인라인 뷰 구조라면, from 절에서 as 한번, 그리고 join 때문에 또한번 as를 해줘서 두번 해줘야 하나요? (두번 해줘야 한다면 두번째 AS를 위해서 괄호 처리라든지 이런게 필요할까요?)
A.아뇨 한번만 하시면 됩니다~
댓글
Q.만약 쿼리가 10 여개의 인라인뷰로 하나의 select 쿼리를 만들어야하는 구조라면.. 이 인라인뷰들을 인라인뷰 대신 각각의 with절로 빼서 불러오는 형태로 구현해도 될까요? 인라인뷰와 with절 사용을 동일한거라 봐도 될지요.. 성능에도 차이가 많이 있을지..궁금
A.10개의 인라인 뷰가 정말 필요한지부터 고려해봐야할 것 같구요~ WITH절은 수행 횟수에 따라 내부적으로 MATERIALIZE 혹은 INLINE VIEW 방식으로 풀리는데요, 그렇기 때문에 어떤게 더 효율적인지는 케바케이고 일반적으로 데이터 추출 건수는 적은데 Buffer I/O가 많을때 쓰면 좋습니다. 실행 계획을 보면서 요리조리 튜닝해 보세용~
Q.서로 다른 인라인 뷰끼리 조인하는 방법이 있을까요?제품 고객 채널 과 같이 3개 이상의 테이블을 조인할 때가 많거든요
A.네~ 인라인뷰도 테이블처럼 컬럼 이용하여 조인 가능합니다~
Q.인터페이스 테이블과 업무 테이블의 차이점좀 알려주세요.. 업무테이블 뒤에 _IF라고 붙여서 사용하던데 무슨 의미인지 모르겠어요
A.IF 테이블은 보통 타사이트와의 IF 연동 프로그램에서 사용하는 테이블입니다.
---
INLINE VIEW = FROM절에 쓰이는 SUBQUERY
(마지막 내용)남발하면 문제가 되겠지만 적절하게 활용하면 비용절감에 큰 효과가 있기도 하다
두 테이블을 조인한다고 했을 때, 전체 데이터를 조인해서 필요없는 부분을 버리는 것 보다는
먼저 필요한 데이터만 INLINE VIEW로 생성을 해서 그것만 조인을 하면 비용 절감 효과
VIEW는 OBJECT로 생성이되고, 그걸 SQL에서 불러다가 쓰는 구조인데
INLINE VIEW는 따로 OBJECT를 생성하진 않는다. SQL내에 직접 기술을 해서 쓰이는 일회성 VIEW이다.
부서별 급여의 평균값을 내는 쿼리를 쓸거야
DEPARTMENT_NAME이랑 그에 따른 SALARY의 평균 값
JOIN을 하려면 DEPARTMENT_ID로
여기서 급여를 부서별로 평균을 내야되니까 INLINE VIEW가 들어가게 됨.
INLINE VIEW만 SELECT
소수점 너무 기니까 반올림하고 ROUND로
ALIAS 지정해줘야 한다
SELECT에 B.AVG_SAL추가하고
JOIN을 해줘야 되니까 WHERE A. = B. DEPARTMENT
원하는 결과값이 나왔다
이렇게 쓰이는게 INLINE VIEW다
생각해볼 것은 이것도 저번에 말했던 중첩 sub query처럼 join으로 풀릴 수가 있다 옵티마이저에 의해서
뭘로 실행됐는지 plan을 봐보자
VIEW라는게 보여. 풀리지 않았다.
해석 : 7번부터 거꾸로 보시네
EMPLOYEES의 테이블을 FULL SCAN해서
GROUP BY를 한 다음
VIEW로 만든 것
DEPARTMENTS 테이블이 드라이빙 돼서 INDEX FULL SCAN을 한 다음
뷰와 함께 SORT JOIN
VIEW에서 먼저 GROUP BY를 하고 JOIN을 했다!
---
INLINE VIEW에서 풀리는 것과 관련된 HINT가 있다
VIEW MERGING이 됐다고 표현한다
뷰 머징 더 자세히 : https://jungmina.com/756
풀리게끔 힌트를 줘보면
이렇게, 확인해보면
VIEW라는 단어가 보이지 않아 -> 메인쿼리 레벨로 풀린 것.
간략하게 해석해보면
DEPARTMENTS TABLE이 INDEX FULL SCAN을 타고 역시 드라이빙 테이블이 되고
EMPLOYEES TABLE을 FULL SCAN한 다음에
SORT JOIN해서
JOIN한 다음에 GROUP BY를 한 것
풀리는게 좋은지 안풀리는게 좋은지는 그때마다 다르고 기본은 옵티마이저의 판단에 맡기면 된다
안풀리는게 좋은데 가끔 풀리는 경우가 있는데, 그럴 땐
NO_MERGE라는 HINT를 줘서 네 맘대로 풀지 말아라 강제 지정 할 수 있다
---
그리고 가끔 서브쿼리가 남발이 되는 경우가 있음
초창기에 쿼리를 짤 떈 보기좋고 심플하게 짜는데
변경 요청이 현업으로부터 들어오게 되면 다른 개발자에 의해 수정이 되고 덧붙여지다 보면 sub query가 떡칠이 돼 있음
가독성 떨어지고, 다른 사람이 봤을 때 아 이걸 어디서부터 건드려야되나 막막해짐. 똥 쿼리라고 함. 주의하셔요
---
inline view가 유용하게 사용되는 경우가 또 하나 있는데
입사한 달 별로 1월에 몇명이 입사했는지... 2월에... 이런걸 카운트 해놓은 쿼리
입사일을 달로 뽑아서 그걸 1월이면 카운트하고 2월이면 카운트하고...
그걸 가로로 풀어낸 것.
가로로 풀어내려고 하다보니 행이 많아짐
이걸 한줄로 만들어서 보기좋게 표현하려고 하는데 이때 INLINE VIEW를 이용하시면 됩니다.
보면 첫번째 열은 첫번째 행만 숫자가 있고 둘 - 둘 이고 셋 - 셋 임.
이럼 다 sum해버리면 한줄로 표현이 되겠죠
그럼 얘를 인라인으로 만들어서 한줄로 바꿀 수가 있다
ORDER BY는 필요가 없어서 지움
이런식으로 INLINE VIEW를 사용할 수가 있다!
---
(8/44)
https://youtu.be/_VY05qoz2eA - 스칼라 서브쿼리 캐싱 기능 있는거 알아? 튜닝 기법까지 알랴줌
Scalar sub query
하나의 레코드당 하나의 값을 리턴하는 SUB QUERY
보통 SELECT절에 많이 쓰이지만 그 외에도 컬럼값이 올 수 있는 모든 자리에 쓸 수가 있다
▽
??
(4/44)where절에 사용하는 중첩 subquery - 1탄
(9/44)from절에 사용하는 inline view - 2탄
(8/44)select절에 사용 : scalar subquery - 3탄
아니 SELECT뒤에만 오는거 아니면 다른거랑 어떻게 구분해? 살다보면 알게 되겠지 뭐
---
스칼리 서브쿼리를 이용해서 쿼리를 작성 해볼 것.
HR 계정의 EMPLOYEES, DEPARTMENT를 이용해서
EMPLOYEES 원하는 컬럼만 조회
DEPARTMENT_ID만 갖고는 얘가 어떤 구성인지 짐작하기 힘들다.
그래서 ID대신에 NAME을 가져오고 싶은데
EMPLOYEES테이블에서는 DEPARTMENT_NAME이라는 컬럼이 없어
그래서 DEPARTMENT 테이블에서 가져오고 싶어
얜 이렇게 생겼고
이럴 때 스칼라 SUB QUERY를 쓸 수가 있다
SUB QUERY안에는 ALIAS를 주는 게 좋다
이렇게 ID대신 NAME을 출력했다.
▽아니 컬럼 자리에다 SELECT를 했어 - 이렇게 해도 그 레코드에 그 레코드가 맞나봐??
아니 일단 A를 가져왔잖아 그 중에서 SALARY > 5000;인 레코드들을 가져왔어
FIRST_NAME, LAST_NAME, SALARY, 그리고 여기 스칼라 서브쿼리를 해야하는데, A.DEPARTMENT_ID와 같은 B의 레코드의 DEPARTMENT_NAME을 가져오니까 아 되겠구나?
---
여기서 NAME말고 다른것도 출력하고 싶어하면
에러가 나. 스칼라 서브 쿼리는 하나의 값만 리턴하는 서브쿼리
---
이 상황에서는 B.DEPARTMENT_ID가 PK여서 중복이 날 일은 없는데
중복의 소지가 있는 컬럼이면 저렇게만 쓰면 오류가 날수도 있어
그래서
WHERE에 ROWNUM = 1로 하나만 갖고오라고 써주는 방식으로 많이 쓴다
ROWNUM : https://m.blog.naver.com/roropoly1/221219162711
ROWNUM은 데이터베이스에 저장되지 않고, SELECT절에 의해 추출되는 ROW에 붙는 순번이다
ROWNUM = 2라면 처음 한 건 추출해서 ROWNUM이 2인지 비교하는것인데, 맞지 않으므로 버린다. 버렸기 때문에 또 새로 추출되는 레코드는 1이고 또 버리고... ROWNUM = 2는 도달할 수 없는 값이다. ROWNUM = 1만 동작한다.
---
실행계획 확인
실행 계획은 안쪽부터 위에서부터 아래로. 저걸로 예를 들면 2 -> 1 -> 3 이런식으로 가게 된다
그러면
DEPT_ID를 뒤져서
DEPARTMENTS부터 ACCESS를 했다
근데 스칼라 서브쿼리에선 예외가 있다. 위에서부터 해석을 한다기보다는 스칼라 서브쿼리가 나중에 ACCESS하게되는 테이블이다 라고 해독을 해주셔야 됩니다.
왜냐면 스칼라 서브 쿼리 안에 상수값으로 메인테이블에 있는 컬럼이 쓰였기 때문에(A.DEPARTMENT_ID) 스칼라 서브쿼리가 먼저 나올수가 없잖아 상식적으로
그래서 메인 테이블인 EMPLOYEES A가 드라이빙 테이블이 되는거고
DEPARTMENT 테이블이 INNER TABLE이 되는 것
그래서 우리가 생각해볼 땐
EMPLOYEES 테이블 FULL SCAN한 다음
거기서 나온 DEPT_ID를 갖고
DEPARTMENTS테이블의 PK INDEX를 스캔해서 TABLE ACCESS한 다음 (SELECT STATEMENT)값을 가지고 왔다 해석해야 맞는 해석이다.
---
스칼라 서브쿼리의 캐싱 기능 - 반드시 기억
이게 뭐냐면 DEPARTMENTS 테이블을 스칼라 서브쿼리에 썼을 때
메인 쿼리에 있는 DEPARTMENT_ID값이 상수값으로 입력이 됐잖아
그걸 입력값으로 하고
스칼라 서브쿼리에서 SELECT를 했던 DEPARTMENT_NAME을 출력값이라고 하면
이 입력값과 출력값을 오라클에서 캐시에다가 저장을 해놓는다
1번은 인사팀이고, 2번은 영업팀이야 라는 것을 최초 쿼리가 됐을 때 한번 저장을 해 놓고
다음 번에 쿼리를 수행했는데 DEPARTMENT_ID가 2번인 부서를 출력을 해달라는 쿼리가 요청되면
쿼리를 수행하지 않고 캐시에서 바로 출력값을 뽑아서 리턴해주는 기능이다
---
그럼 생각해볼 수 있는게
아까같은 경우 부서의 DISTINCT 수가 10개 정도 있다라고하면 매우 적은 수인데
그럴 경우 캐싱 기능이 굉장히 큰 효과
부서가 엄청 많은 회사면 쿼리를 수행할 때 같은 부서가 조회될 확률이 현저히 떨어지니까 캐싱 기능이 오히려 성능을 떨어뜨리는 경우가 생길 수 있다. 왜냐면 한번 수행하면 될 것을 캐시를 한번 거쳐야되니까
캐시를 뒤졌는데 입력값과 출력값이 저장이 돼 있지 않아. 그럼 어차피 쿼리를 수행해야되니까 일을 두번하는 격
서브쿼리의 캐싱 기능은 적절한 상황에 써야 성능에 큰 효과가 있다~
튜닝 기법 중에 함수를 이용해서 데이터를 출력할 때 이 스칼라 서브쿼리의 캐싱 기능을 이용하는 기법이 있는데
보통 SELECT FUNCTION(XXX) FROM TABLE; 이런식으로 쿼리를 짜는데,
아까 말했듯이 이 함수의 입력값과 출력값이 한정이 돼 있으면, 비교적 적으면, 이 함수를 한번 더 서브쿼리로 싸는 것
이런걸 컬럼갑승로 해서 SELECT FROM TABLE 안에다 집어넣는 기법도 쓰이고 있다
Q.부서가 적을때 쓰고, 많을 때는 안쓰고 싶으면 쓰지 않는 선택사항인가요? 아니면 스칼라쿼리를 쓰면 자동적으로 실행되는건가요?
A.자동 실행이기 때문에 부서가 적을 경우에만 스칼라로 작성하는 것이 유리합니다~