직원에 대한 상세정보가 적힌 가상의 테이블로써 제약조건이 존재하지 않고, 모델도 따로 존재하지 않는다. 실제하지 않는 테이블이라는 뜻이다.
목적
복잡한 SQL문을 간단하게 작성하기 위해서 사용한다.
데이터의 엑세스를 제한하기 위해서 사용한다.(데이터에 대한 보안성 강화)
사용자의 권한과 연관이 있다.
동일한 데이터로부터 다양한 결과를 얻기위해서 사용한다.
뷰 정의 하기 (가상의 테이블)
CREATE OR REPLACE VIEW EMP_SALARY_DETAIL_VIEW AS SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME, E.COMMISSION_PCT, S.GRADE, E.SALARY*12+E.SALARY*NVL(COMMISSION_PCT, 0)*12 ANNUAL_SALARY FROM EMPLOYEES E, DEPARTMENTS D , SALARY_GRADE S WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID AND E.SALARY >= S.MIN_SALARY AND E.SALARY <= S.MAX_SALARY WITH READ ONLY;
CREATE OR REPLACE VIEW를 이용하여 VIEW를 덮어쓸 수 있다.
WITH READ ONLY
읽기만 가능하다는 뜻이다.
AS 이후에는 서브쿼리로 SELECT를 입력했다.
서브쿼리 : SQL을 가상의 테이블이 포장한 데이터를 조회하는 SELECT문이다.
전체사원의 쿼리는 SELECT의 실행결과로 언제나 TABLE의 형태로 보일 수 있다.
SELECT문은 곧 TABLE을 의미한다.
가상의 TABLE은 메모리에 존재하는 SELECT문의 실행결과를 의미한다.
이 결과를 저장하길 원한다.
쿼리 자체의 실행결과를 가상테이블로 보는 것이다.
쿼리실행결과를 가상의 테이블로 만들어 놓는다.
사용방법
뷰를 정의한다.
SELECT * FROM 뷰(가상의 테이블)명칭 WHERE 조건식; --> 방법으로 사용한다.
FROM 절에는 1. 테이블 이름, 2. VIEW이름을 사용할 수 있다.
테이블이름 : 테이블에서 가져온다.
VIEW이름 : 쿼리의 결과를 가상의 테이블로 본다.
쿼리의 실행결과가 데이터에는 테이블처럼 보인다.
이전 진행방법 : 급여 등급별 인원수 조회하기
SELECT S.GRADE, COUNT(*) FROM EMPLOYEES E, SALARY_GRADE S WHERE E.SALARY >= S.MIN_SALARY AND E.SALARY <= S.MAX_SALARY GROUP BY S.GRADE;
뷰를 사용하여 진행하는 방법
SELECT EMP_SALARY_GRADE, COUNT(*) FROM EMP_DETAILS_VIEW GROUP BY EMP_SALARY_GRADE;
뷰의 별칭을 이용하여 간편하게 조회할 수 있다.
사용목적의 복잡한 SQL문을 간단하게 작성하기 위해서 사용한를 충족한다.
뷰는 가상의 테이블의 형태를 가지기 때문에 다른 사람에게 보여주기 싫을 때 뷰로 데이터를 만들어서 보여줄 수 있다.
개발자들은 데이터의 전체적인 부분이나 업무, 업체에 대한 이해도가 낮을 수 있기 때문에 기업에서는 실제 테이블에 대한 접근을 허용하지 않을 수 있다.
WEB PAGE에 필요한 가상의 테이블만 볼 수 있게 개발자에게 가상의 테이블만 전달할 수 있다.
가상의 테이블로 보여주면서
사용목적의 데이터의 엑세스를 제한하기 위해서 사용한다.(데이터에 대한 보안성 강화)을 충족시킬 수 있다.
쿼리중
EMPLOYEES + DEPARTMENTS + SALARY_GRADE
직원의 급여, 연봉상세정보, 부서의 이름을 가상의 TABLE로 획득할 수 있다.
EMPLOYEES + DEPARTMENTS + EMPLOYEES
셀프쿼리로 직원과 매니저의 상세정보를 획득할 수 있다.
EMPLOYEES + DEPARTMENTS + JOBS
직원, 부서, 직종의 상세정보를 획득할 수 있다.
조인조건으로 다양한 결과가 출력된다.
사용목적 중 동일한 데이터로부터 다양한 결과를 얻기위해서 사용을 충족한다.
뷰를 사용하면 좀더 쉽고, 빠르고, 적은 코딩, 빠르고 간단한 명령어로 추적이 가능하다.
뷰의 종류
단순 뷰
한 테이블에서만 데이터를 가져온다.
함수 또는 데이터 그룹을 사용하지 않았음
테이블 1개에서만 사용한다.
DML의 언어를 사용 가능은 하지 만, 대부분의 뷰는 WITH READ ONLY로 변경하여 사용한다.
복합 뷰
여러테이블에서 데이터를 가져온다.
함수 또는 데이터 그룹을 포함하고 있다.
뷰의 수정
CREATE OR REPLACE VIEW NAME AS 서브쿼리 로 생성하고, 만들면 된다. --> 생성도 덮어버리는 것이 좋다.
인라인 뷰
쿼리 실행동안만 가상의 테이블이 생성이 되는 것이다. 쿼리실행이 종료시 삭제된다.
SELECT 테이블1.컬럼 2, 테이블2.컬럼3 FROM (SELECT 컬럼1 FROM 테이블 1 WHERE 조건식 ) 테이블1, 테이블2 WHERE 조건식
인라인 뷰 : FROM절에 씌이는 서브쿼리로 가상의 테이블로 볼 수 있으며, 원칙적으로 1회성으로 사용한다.
쿼리란 TABLE과 TABLE의 join이다. --> SELECT문의 결과를 활용하기 위해 권장되는 방법이다.
SELECT문으로 가상의 테이블을 만들 수 있다.
페이징 처리
데이터를 화면에 표시할때, 화면의 표시양은 제한적이다.
무한 스크롤은 어렵기 때문에 PAGE의 번호를 붙여 10개 출력후 다음 10개출력 방법으로 나오게 만드는 방법
인라인 뷰를 많이 사용한다.
TOP-N분석도 같은 방법이다.
특정컬럼을 오름차순과 내림차순으로 정렬후 1~3등의 사이를 알고싶을때 사용하는 방법이다.
위치에 따른 뷰의 이름
SELECT
스칼라서브쿼리
단순값처럼 취급되는 서브쿼리이다.
FROM
인라인 뷰
뷰를 가상의 테이블로 만들어서 사용하는 서브쿼리이다.
WHERE
서브쿼리
조건식의 값으로 취급되는 서브쿼리이다.
위치에 따라서 역활이 달라지기 때문에 이름이 달라진다.
인덱스
색인
데이터 행의 검색 속도를 향상시키기 위해서 사용되는 데이터 베이스 객체이다.
데이터의 위치를 빠르게 찾는 신속한 경로 엑세스 방법을 사용하여 디스크의 INPUT/OUTPUT을 줄여준다.
인덱스는 테이블과 독립적으로 존재한다.
한번 생성된 인덱스는 ORACLE이 자동으로 유지 관리한다.
테이블이 삭제되면 그 테이블의 데이터를 색인화하고 있는 인덱스도 같이 삭제된다.
인덱스의 생성은
자동생성
테이블을 정의할 때 PRIMARY KEY(기본키) 제약조건, UNIQUE제약조건이 정의된 컬럼의 값들은 자동으로 인덱스가 생성된다.
자동으로 생성이 된다는 것은 제약조건을 자동으로 만들고 관리한다는 것이다.
수동생성
사용자가 행에 대한 엑세스 시간을 줄이기 위해서 특정 열을 대상으로 인덱스를 생성할 수 있다.
사용자가 특정행에 대한 ACESS의 시간을 줄이기 위해서 특정열을 대상으로 인덱스를 생성한다.
병원의 DB의 경우 환자의 번호는 INDEX의 PRIMARY KEY이지만, 환자의 이름은 이름으로 색인 해놓는다. PRIMARY KEY/ UNIQUE의 키를 동명이인이 존재할 수 있기 때문에 설정하지 못한다.
쿼리의 실행속도의 향상을 위하여 수동으로 INDEX를 생성할 수 있다.
그러나 인덱스는 함부로 만들면 안된다.
생성이 필요한 경우
WHERE절이나 조인조건에 자주 사용되는 경우 색인을 지정해주어야한다.
컬럼이 매우 다양한 값을 포함하고 있는 경우
사람의 이름은 값이 다양하기 때문에 동명이인이여도 일반의원은 1개에서 2개가 나온다. 인덱스로 적합하가.
데이터가 아주 많은 테이블을 대상으로 조회작업을 했을 때 대부분의 조회작업에서 검색되는 행이 전체 데이터의 2%~4%인 경우
만약 여자, 남자로 설정한경우 성별은 50%와 50% 로써 INDEX를 설정할 수 없다
DATE의 인덱스가 300까지 존재한다면, 데이터가 너무많아서 인덱스의 확인이 불편하고 인덱스로 시간소모가 많이 될 것이다.
생성이 필요하지 않는 경우
테이블이 작은 경우
테이블이 자주 갱신되는 경우
인덱스화된 열이 표현식의 일부로 사용되지 않는 경우
WHERE절의 조회조건으로 자주사용되지 않는 경우
대부분의 조회작업에서 전체데이터의 2%~4%이상 검색되는 경우
ROWID
데이터의 값과 경로를 출력할 수 있는 테이블에서 행의 고유주소를 나타내는 64진수의 숫자데이터이다.
양쪽값이 균등하지 않는다면 균형이 맞지 않아 2000번 이상을 빨리 찾게 되고 2000번은 더 늦게 찾게되지 않을까?
양쪽값이 균등하도록 조정해서 고루 분포되게 균형잡힌 트리가 될 수 있도록
오라클에서 인덱스에 대해서 알아서 관리하고 있다.
INDEX는 항상 정렬되어있으며, 색인 또한 정렬이 되어있다.
색인이 만들어져있기 때문에 옵티마이저의 COST의 비용도 덜 발생된다. : INDEX설정시
현재 2초의 COST발생의 경우 해당 TABLE의 다른 테이블에도 동일한 이름이 있기 때문에 확인으로 2초의 COST가 발생된다.
오라클의 계획설명
RDBMS 는 옵티마이져가 구동되는 중이다.
SQL을 전달받고 어떻게 실행할지 SQL을 분석 후 최적의 실행방법을 분석한다. 그리고는 실행계획을 수립한다. 옵티마이저의 실행계획이 실행되고, 위에처럼 COST처럼 설명이 나온다.
SQL튜닝 과 분석 : 최적의 쿼리를 작성하면서 인덱스를 추가한다.
오라클의 힌트
옵티마이저는 사람보다는 똑똑하지 않다.
옵티마이저를 분석하고, 실행분석을 수립하고, 가이드를 만들 때 옵티마이저힌트를 작성하여 포함한다.
이러한 형태로 옵티마이저에게 힌트를 전송한다. 개발자의 의도를 옵티마이저에게 전달하여 실행계획 수립시 도움을 준다.
인덱스를 사용하지 않으면 COST가 증가되고, 쿼리의 실행가능이 떨어진다.
INDEX사용시 쿼리의 성능을 높일 수 있다.
서브 쿼리
서브쿼리란 메인 쿼리내부에 정의된 쿼리이다.
다른 SELECT문의 내부에 정의된 SELECT문을 서브쿼리라고 한다.
서브쿼리를 포함시킬 수 있는 곳
WHERE절
SELECT절
HAVING절
FROM 절(인라인 뷰)
SELECT COLUMN, COLUMN FROM TABLE WHERE COLUMN 연산자 (SELECT COLUMN FROM TABLE);
오른쪽의 위치에 위치한다.
실행결과는 메인쿼리의 WHERE절(조건식)에서 사용된다.
쿼리를 통해서 획득할 수 있는 값으로 조건식을 사용해야한다.
서브쿼리는 반드시 괄호로 묶여야한다.
종류는 결과가 몇개나오는지에 따라서 달라진다.
다중행
연산자가 여러개이면 다중행이다.
서브쿼리의 실행결과로 여러행이 반환된다.
단일행
연산자가 하나면 단일행이다.
서브쿼리의 실행결과로 한행만 반환된다.
지금까지 작성한 것이 단일행 서브쿼리이다.
WHERE절이나 HAVING절에서 사용되는 서브쿼리는 조건식의 사용될 값을 제공한다.
조건식에서 사용되는 값이 SQL실행으로 획득되는 경우에는 서브쿼리를 사용한다.
단일행 서브쿼리
다중행 서브쿼리
=
IN
<>
NOT IN
>
>ANY,>ALL
<
<ANY, <ALL
서브쿼리의 특징
서브쿼리는 한번만 실행된다.
서브쿼리는 메인쿼리보다 먼저 실행된다.
서브쿼리의 실행결과는 메인쿼리의 조건식에서 사용된다.
조건식에서 비교값으로 사용되는 값이 쿼리의 실행결과로만 획득할 수 있을 때, 그 비교값을 조회하는 쿼리가 서브쿼리다
서브쿼리 사용시 주의점
서브쿼리는 반드시 괄호로 묶여야한다.
조건식의 오른쪽에 서브쿼리를 위치시키면 가독성이 높아진다.
서브쿼리의 실행겨로가가 단일행인지 다중행인지에 따라서 적절한 연산자를사용해야한다.
괄호가 되어잇는 부분이 먼저 실행되고, 항목이 1개이기 때문에 단일행 서브쿼리이다.
좌우가 컬럼의 갯수가 같아야한다.
하나의 문장에 서브쿼리가 2개가 올 수 있다.
다중행 서브쿼리
조건의 결과가 다중행이다.
다중행 서브쿼리
조인은 한쪽에 있는 것이 나오지 않는다.
양쪽에 있어야만 결과가 나온다.
조인을 사용할 수 있으면 서브쿼리보다 조인을 사용하는 것이 좋다.
서브쿼리보다 조인을 장려한다.
값을 가공없이 사용할 때는 JOIN을 사용하는 것이 좋다.
조인으로 풀수 있는 것은 서브쿼리로 사용할 수 있지만 서브쿼리의 값을 MAIN쿼리에서 가져올 수 없다. ㄱ
괄호안에서만 사용하고 끝이기 때문이다.
조인으로 풀리는 것이 더 많기 때문에 되도록 조인을 사용하는 것이 좋다.
ALL : 서브 쿼리로 조회된 급여보다는 무조건 전체보다 높아야한다는 의미 ALL
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60 AND SALARY > ALL(SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 30);
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60 AND SALARY > (SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 30); --->ALL은 MAX와 동일하다.
ANY : 서브쿼리중 하나라도 높으면 나온다.
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60 AND SALARY > ANY(SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = 30);
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 60 AND SALARY > (SELECT MIN(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 30); --->ANY는 MIN과 동일하다.
다중열 서브쿼리
SELECT * FROM TABLE WHERE (COL1, COL2) IN (SELECT COL3, COL4 FROM TABLE)
여러열을 연결하고 연산자는 IN을 사용해서 서로 비교를 함
자신이 소속된 부서에서 최저급여를 받는 사원의 부서아이디, 사원아이디, 급여를 조회하기
부서에서의 최저급여받는 사람을 서브쿼리로 둬서 사원을 비교하여 조회하겠다
SELECT DEPARTMENT_ID, EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES WHERE (DEPARTMENT_ID, SALARY) IN(SELECT DEPARTMENT_ID, MIN(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL GROUP BY DEPARTMENT_ID) ORDER BY DEPARTMENT_ID ASC;
SELECT DEPARTMENT_ID, COUNT(*) FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL GROUP BY DEPARTMENT_ID HAVING COUNT(*) <= 5 ORDER BY 1;
그룹그룹을 지어놓고 COUNT를 적용시켜서 사용함 5명이하는 HAVING을 사용해서 출력함
WITH절
SQL의 실행결과를 지정된 이름으로 임시보관(캐싱)한다.
서브쿼리를 사용하는 SQL문에서 반복적으로 실행되는 SQL에 대해서 WITH절을 사용하면 실행 성능이 향상된다.
WITH절에서 정의한 가상의 테이블의 아래 SELECT문을 사용할 수 있다.
WITH절은 단독으로 사용할 수 없다.
WITH 해당하는 FROM절에 사용하는 인라인뷰, 일반 뷰이다.
가상의 테이블이라는 의미다.
WITH절을 만들면 SELECT로 전테를 사용할 수 있게 된다.
WHERE절에서도 사용이 가능하다.
COUNT(*)절로 반복적으로 사용된다.
WITH 별칭1 AS (SELECT 문), 별칭2 AS (SELECT 문) SELECT COL, COL FROM 별칭1, 별칭2로 가져와서 사용하면 된다. WHERE조건식
WITH EMP_SALARY AS( SELECT DEPARTMENT_ID AS DEP_ID, AVG(SALARY) AVG_SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL GROUP BY DEPARTMENT_ID )--여기까지는 맞춤
SELECT DAS.DEP_ID, E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, DAS.AVG_SALARY FROM EMPLOYEES E, (SELECT DEPARTMENT_ID AS DEP_ID, AVG(SALARY) AVG_SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL GROUP BY DEPARTMENT_ID) DAS WHERE E.DEPARTMENT_ID = DAS.DEP_ID AND E.SALARY > DAS.AVG_SALARY;
인라인뷰로 사용이 가능하다.
SELECT DAS.DEP_ID, E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY, DAS.AVG_SALARY FROM EMPLOYEES E, ( SELECT DEPARTMENT_ID AS DEP_ID, AVG(SALARY) AVG_SALARY --표현식은 별칭을 사용할 것 FROM EMPLOYEES WHERE DEPARTMENT_ID IS NOT NULL GROUP BY DEPARTMENT_ID) DAS WHERE E.DEPARTMENT_ID = DAS.DEP_ID AND E.SALARY > DAS.AVG_SALARY;
