[ SQL ] Oracle 확장 sql문 #4 _ 뷰(view) & 인덱스

[ 뷰(View) ]

 1. 뷰(View) 란 ? 

• 뷰(view)는 테이블에 대한 가상의 테이블이다.
• 뷰는 실제 데이터가 저장되는 것은 아니지만 뷰를 통해 데이터를 관리할 수 있다.
• 뷰는 테이블을 기준으로 생성되며 기본 테이블이 없으면 뷰도 존재할 수 없다.
• 뷰는 테이블의 전체 데이터 중에서 일부만 접근할 수 있도록 제한한다.
• 뷰에 대한 수정한 결과는 뷰를 정의한 기본 테이블에서 적용한다.
• 뷰를 정의한 기본 테이블에서 정의된 무결성 제약조건을 상속받는다.
• 뷰를 사용하는 이유는 기본 테이블에 대한 보안 기능을 설정해야 하는 경우이거나 복잡하며 자주 사용되는 질의를 더 쉽고 간단하게 사용하기 위해서이다.
• 뷰의 장점:
   - 뷰는 데이터베이스의 선택적인 내용을 보여줄 수 있으므로 데이터베이스에 대한 액세스를 제한한다.
   - 복잡한 데이터 질의어를 통해 얻을 수 있는 결과를 간단한 데이터 질의어를 사용한다.
   - 데이터 독립성을 허용한다.
   - 필요한 데이터만 조회할 수 있으므로 사용자의 편의성을 제공한다.
• 뷰의 형태
  - 단순 뷰(simple view): 하나의 기본 테이블에서만 뷰를 생성한다. 단순 뷰에서 실행한 데이터 조작어의 실행 결과는 기본 테이블에 반영한다.
  - 복합 뷰(complex view): 두 개 이상의 기본 테이블에서 뷰를 생성한다. 복합 뷰는 group by문의 여부에 따라 데이터 조작어를 제한적으로 사용한다.

 

 

 2.  뷰의 생성 

• create view 문 내에서 서브쿼리를 내장하여 뷰를 생성한다.
• 서브쿼리는 복합 select 문을 포함할 수 있지만, order by 문은 포함할 수 없다.
• 뷰 생성 시 컬럼 이름을 명시하지 않으면 기본 테이블의 컬럼 이름을 상속받는다.
• 함수나 표현식에 의해 정의된 컬럼은 별칭을 사용하여 명시한다.

create [or replace] view 뷰명
as
서브쿼리;

 

 

 ex  )  emp 테이블과 dept 테이블에서 월급에 관련된 내용만 조회할 수 있는 뷰를 생성한다. scott 계정에는 뷰 생성 권한이 없으므로 최고관리자 SYS 계정으로 접속하여 scott 계정에 grant create view to scott 문으로 뷰 생성 권한을 부여한다.

 

#1 뷰를 생성한다.

CREATE OR REPLACE VIEW SON_EMP AS

#2 월급의 총합, 최대값, 최소값을 함수로 구한다.

SELECT E.DEPTNO, D.DNAME, SUM(SAL) TOTSAL, MAX(SAL) MAXSAL, MIN(SAL) MINSAL FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY E.DEPTNO,D.DNAME; --View SON_EMP이(가) 생성되었습니다.

 

#3 뷰를 조회한다.

SELECT*FROM SON_EMP;

 

 

 

 3.  뷰의 삭제  

• drop view 문으로 뷰를 삭제하며 뷰를 정의한 기본 테이블의 구조나 데이터에는 전혀 영향을 주지 않는다.

drop view 뷰명;

 ex )  생성한 뷰를 삭제한다.

DROP VIEW SON_EMP; --View SON_EMP이(가) 삭제되었습니다.

 

 

 

[ 인덱스 ]

 1. 인덱스란 ? 

• 인덱스(index)는 포인터를 사용하여 행의 조회를 빠르게 할 수 있는 스키마 객체이다.
• 인덱스는 테이블 내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 데이터 구조이다.
• 인덱스는 명시적 생성하거나 자동으로 생성할 수 있다.
• 컬럼에 대한 인덱스가 있으면 테이블 전체를 조회한다.
• 쿼리의 성능을 향상하려면 인덱스 생성을 고려해야 한다.
• 컬럼이나 컬럼의 집합에 값이 유일하게 하려고 인덱스를 사용할 수 있다.
• 인덱스의 특징:
  * 인덱스는 테이블의 값을 빠르게 액세스하도록 하는 데이터베이스 객체이다.
  * 인덱스를 자동으로 사용하고 유지보수 한다.
  * 인덱스를 만들면 사용자가 직접 조작할 필요가 없게 된다.
  * 인덱스는 테이블과는 독립적이다.
  * 인덱스는 언제든지 생성하거나 삭제할 수 있다.
  * 인덱스는 다른 인덱스에 영향을 주지 않는다.
• 인덱스의 형태:
  자동 인덱스: primary key 제약조건이나 unique 제약조건에 의해 자동으로 생성되는 인덱스이다.
  수동 인덱스: create index 문을 실행해서 만드는 인덱스이다.
• 인덱스를 생성하는 것이 좋은 컬럼은 null 값이 많이 포함된 컬럼이나 조인 조건에서 자주 사용되는 컬럼이다.
• 인덱스를 생성하면 오히려 성능이 저하되는 컬럼은 테이블의 크기가 작거나 테이블이 자주 갱신되는 테이블의 컬럼이다.
• 오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있으며 B-tree 인덱스는 컬럼 안에 독특한 데이터가 많을 때 가장 좋은 효과를 낸다.
• B-tree 인덱스의 알고리즘 원리는 주어진 값을 리스트의 중간 지점에 있는 값과 비교하고 만약에 그 값이 더 크면 리스트의 아래쪽 반을 버리고 만약 그 값이 더 작다면 위쪽 반을 버린다.
• 하나의 값이 발견될 때까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도 반복한다.

 

 

 

 2. 인덱스의 생성  

• create index 문으로 인덱스를 생성한다.
• on 명령어로 인덱스를 부여할 컬럼을 지정한다.

 

< 인덱스의 형태 >

• (1) bitmap 인덱스:
  각 컬럼에 대해 적은 개수의 독특한 값이 있을 때 가장 잘 작동한다.
  bitmap 인덱스는 B-tree 인덱스가 사용되지 않을 때 성능을 향상한다.
  bitmap 인덱스는 테이블이 매우 크거나 수정이 잘 일어나지 않을 때 사용할 수 있다.
  질의문이 or 연산자를 포함하는 여러 개의 where 문을 자주 사용할 때 유리하다.
• (2) unique 인덱스:
  unique 인덱스는 인덱스를 사용한 컬럼의 중복 값들을 포함하지 않고 사용할 수 있는 장점이 있으며 primary key 제약조건이나 unique 제약조건에 의해 자동으로 생성되는 인덱스이다.
• (3) non-unique 인덱스
  non-unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터값을 가질 수 있다.

create [bitmap][unique] index 인덱스명 on 테이블(컬럼)

---

▼ SELECT * FROM USER_IND_COLUMNS ;

---

 ex )   자동 인덱스로 인덱스를 생성한 테이블의 인덱스와 인덱스 컬럼을 확인한다.

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME FROM USER_IND_COLUMNS  WHERE TABLE_NAME IN('EMP');

 

 ex )   행에 대한 액세스 속도를 빠르게 하려고 컬럼에 non_unique 인덱스를 생성한다.

#1 emp 테이블을 복사한다

CREATE TABLE EMP_INDEX AS SELECT*FROM EMP; --Table EMP_INDEX이(가) 생성되었습니다.

 

#2 시간 체크를 설정한다.

SET TIMING ON;

 

#3 사원명을 조회한다.

SELECT EMPNO, ENAME FROM EMP_INDEX WHERE ENAME='SMITH';

 

#4 인덱스를 생성한다.

CREATE INDEX IDX_EMP ON EMP_INDEX(ENAME); --Index IDX_EMP이(가) 생성되었습니다.

 

 

#5 인덱스를 통해서 사원명을 조회한다.

SELECT EMPNO, ENAME FROM EMP_INDEX WHERE ENAME='SMITH';

 

 

 3. 인덱스 삭제  

• drop index 문으로 인덱스를 삭제한다.
• 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않는다.
• 인덱스를 삭제하려면 인덱스의 소유자이거나 인덱스 삭제 권한을 가지고 있어야 한다.
• 인덱스는 변경할 수가 없고 삭제만 가능하다.
  
  

drop index 인덱스명

 

 ex )   생성한 인덱스를 삭제한다.

DROP INDEX IDX_EMP; --Index IDX_EMP이(가) 삭제되었습니다.