[데이터베이스] 오라클 서브쿼리를 활용한 DDL / VIEW(뷰) 사용 예제/ INDEX(인덱스) 개념

🪅서브쿼리를 활용한 DDL

-테이블의 구조와 정보를 복사하는 방법. 

CREATE TABLE PRODUCTS_TEMPS AS SELECT*FROM PRODUCTS; 

(스키마부터 모든 데이터가 복사되어 새로운 TABLE로 간다. 단, 키만 빼고...)

DESC PFODUCTS_TEMP;

(스키마 확인)

DROP TABLE 

롤백 못하는 테이블 삭제...

 

🚩서브쿼리를 이용한 INSERT 문

 

🚩서브쿼리를이용한 UPDATE 문

UPDATE SALARY_MJ
   SET COMM = NVL(COMM,0)+150
 WHERE SALMONTH='202401'
   AND EMPNO IN (SELECT EMPNO
                   FROM EMP
                  MINUS
                 SELECT DISTINCT A.EMPNO
                   FROM EMP A, EMP B
                  WHERE A.EMPNO=B.MGR );
ROLLBACK;

SELECT S.EMPNO, S.COMM 원본보너스, A.COMM 추가보너스
  FROM SALARY S, SALARY_MJ A
 WHERE S.SALMONTH='202311'
   AND S.EMPNO = A.EMPNO
   AND S.EMPNO NOT IN (SELECT DISTINCT A.EMPNO
  FROM EMP A, EMP B
 WHERE A.EMPNO=B.MGR);
 
commit;

 

제대로 실행되었는지 데이터를 확인하는 쿼리 작성. 

NULL 값을 활용한 예제

--상사를 제외 일반 사원들의 보너스를 기존 보너스 + 150 으로 하여 update 쿼리 작성
--첫번째 WHERE 절에서는 IN으로 처리하여 쿼리 작성함

UPDATE SALARY_MJ
   SET COMM = NVL(COMM,0)+150
 WHERE SALMONTH='202401'
   AND EMPNO IN (SELECT EMPNO
                   FROM EMP
                  MINUS
                 SELECT DISTINCT A.EMPNO
                   FROM EMP A, EMP B
                  WHERE A.EMPNO=B.MGR );

 

🚩서브쿼리를 활용한 DELETE 문

한가지 결과를 다양한 방법을 사용해서 만들어 봅시다. (데이터의 흐름을 정확하게 파악하기)

---

🪅뷰

: 하나의 가상 테이블로 기존의 테이블을 이용하여 생성하는 "데이터보기" 객체를 의미함. 

  즉, 뷰는 기존의 테이블 또는 테이블에서 필요한 정보들을 조회하여 그 조회의 결과를 묶어서 만든 데이터의 조회 집합이    며, 실제 데이터가 저장되는 것은 아니지만 뷰를 통해 테이블을 관리할 수 있음. 

 

뷰명: 생성할 뷰의 이름을 지정

SUB QUERY: 뷰를 생성할 조회(SELECT)문을 나타냄

WITH CHECK OPTION: 조건식에 만족하는 데이터만 INSERT, UPDATE 가능

WITH READ ONLY: SELECT 만 가능한 뷰를 생성

FORCE: 뷰를 생성할 기준 테이블이 존재하지 않아도 강제로 뷰를 만드는 옵션 

 

-뷰를 사용하는 이유: 

• 보안강화 (특정 사전 정보만 공유할 경우)
• 쿼리 단순화 (직접 작업할 경우 테이블에 관한 이해 및 분석을 직관적으로 하기 위해 단순화

-뷰를 위한 데이터 사전 뷰

.DBA_VIEWS, USER_VIEWS, ALL_VIEWS

• USER_VIEWS : 현재 사용자가가지고 있는 뷰의 조회에 사용
• ALL_VIEWS : 현재 사용자가 액세스 할 수 있는 모든 뷰의 조회에 사용 

 

✏️ 뷰 사용 예제 1

--VIEW 를 이용하여 해당 팀의 팀원들의 부서명을 조회하는 쿼리 작성
--조회항목: 직원번호, 부서코드, 부서명, 직원명
--단, 부서명을 제외한 나머지 조회항목은 VIEW를 통해서 조회함. 

SELECT V1.EMPNO 직원번호, V1.DEPTNO 부서코드, D.DNAME 부서명, V1.ENAME 직원명
  FROM V_EMP_DEPT20 V1, DEPT D
 WHERE V1.DEPTNO = D.DEPTNO;

 

 

✏️뷰 사용 예제 2

--문제: 급여(SAL)가 6500 이상이면서 20230101 이전 입사자중, 상사를 제외한 팀원들만 조회하는 쿼리 작성 
--조회 항목: 직원번호, 직원명, 직급, 급여액(SAL), 입사일(YYYY-MM-DD)
--단, 직원정보에 관련한 것은 VIEW로 작성. VIEW 이름은 V_EMP_V2 로 한다. 

CREATE OR REPLACE VIEW V_EMP_V2 AS (
SELECT EMPNO, ENAME, JOB, HIREDATE
  FROM EMP
 WHERE HIREDATE < TO_DATE('2023-01-01','YYYY-MM-DD')
   AND EMPNO NOT IN ( SELECT A.EMPNO 
                    FROM EMP A, EMP B
                   WHERE A.EMPNO=B.MGR));
                   
                   
SELECT V2.EMPNO 직원번호, V2.ENAME 직원명, V2.JOB 직급, S.SAL 급여액, TO_CHAR(V2.HIREDATE,'YYYY-MM-DD') 입사일
  FROM SALARY S, V_EMP_V2 V2
 WHERE V2.EMPNO = S.EMPNO
   AND S.SALMONTH='202312'
   AND S.SAL>=6500
 ORDER BY V2.EMPNO;

 

🪅인덱스(INDEX)

: 데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체이며, 일종의 색인 기술

  인덱스를 생성하게 되면 (오라클은 내부적으로 ) INDEX TABLE을 생성해 관리

  테이블에 있는 컬럼 중 하나 이상의 컬럼으로 생성

 

📍UNIQUE 인덱스에 대해서는 컬럼값에 중복 데이터를 허용하지 않음

 ...UNIQUE 인덱스는 PK와 관련된 것이라, PK 지정을 하지 않았을 때 PK와 비슷한 효과를 보고 싶다면 UNIQUE 인덱스를

 ...사용할 수 있다. (...확인 필요) 

: 테이블에 하나 이상의 같은 값이 입력되는 것을 막아줌. 즉, 유일성 제약조건과 함께 컬럼의 유일성을 보장해 줌. 오라클 테이블의 기본키와 유일성 제약조건을 가진 컬럼에 대해 자동으로 생성하는 인덱스가 바로 유일 인덱스.

NON-UNIQUE INDEX : 테이블 컬럼은 중복되는 데이터 값을 가질 수 있다. 

복합(CINCASTEBATED) 인덱스: 한 테이블의 두 개 이상의 컬럼을 지정해서 만드는 인덱스.

 

📍인덱스 관리 (USER_INDEX, USER_IND_COLUMNS, USER_COL_COMMENTS)

• ALL : 현 사용자가 엑세스 가능
• USER : 사용자
• DBA : 모든 인덱스

📍인덱스 삭제

 DROP INDEX[인덱스명];

📍인덱스 리빌드 

사용법) ALTER INDEX[인덱스명] REBUILD;

          

💥인덱스 사용 시 주의사항: 테이블 조회 속도를 향상시키기 위한 용도로 너무 많은 인덱스는 피해야 하며, 인덱스 컬럼으로 컬럼의 중복을 최소화해야 함. 

❗인덱스 지정 조건: WHERE 절에 자주 등장하는 컬럼. 

ROWID = DBMS에서 인덱스를 가지고 해당하는 위치를 찾아가는 명령, ROWID는 INDEX 테이블에서 자체적으로 관리하는 유일무이한 값! (따라서 나름의 활용도가 있음)

 

❗인덱스 리빌드 불러오는 법 (아래 결과랑 위에 하나씩 친 거랑 똑같음)