ORCLE_객체(인덱스,뷰,시노님)

오라클 객체

 

(1)인덱스 : SQL명령문의 처리 속도를 향상시키기 위해서 컬럼에 생성하는 오라클 객체

인덱스의 장점

1)검색속도가 빨라진다

2)시스템에 부하를 줄여서 시스템의 전체 성능을 향상시킨다

인덱스의 단점

1)인덱스를 위한 추가 공간이 필요하다

2)인덱스를 생성하는 데 시간이 걸린다

3)데이터의 변경 작업 이 자주 일어날 때는 오히려 성능이 저하된다.

*인덱스는 조회의 성능을 향상시키는 객체

-자동 인덱스: 프라이머리 키 또는 UNIQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스

-수동 인덱스: CREATE INDEX 명령을 실행해서 만드는 인덱스

 

*인덱스를 생성하는 것이 좋은 컬럼은??

1) 테이블의 행의 수가 많을 때

2) WHERE문에 해당 컬럼이 많이 사용 될 때

3) 검색 결과가 전체 데이터의 2~4%일때

4) join에서 자주 사용되는 컬럼이나 null을 포함하는 컬럼이 많은 경우

 B-tree원리

 -주어진 값을 리스트의 중간점에 있는 값과 비교한다. 만약 그 값이 더 크면 리스트의 아래쪽  반을 버린다. 만약 그값이 더 작다면 위쪽 반을 버린다.

 -하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도 반복한다.

 

*다음과 같은 경우에는 인덱스 생성이 불필요 하다.

1)테이블이 작을 때

2)테이블이 자주 갱신될 때

 

인덱스문법

SQL> CREATE [BITMAP/UNIQUE/NON-UNIQUE/] INDEX emp_deptno_indx

ON emp(deptno);

 

비트맵 인덱스

비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 사용하는 것이 좋다.(ex 남,여의 값을 가지는 성별 컬럼)

비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킨다.

테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할수 있다.

 

UNIQUE 인덱스

UNIQUE 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있다.

프라이머리키 와 UNIQUE 제약 조건시 생성되는 인덱스는 UNIQUE 인덱스이다.

 

NON-UNIQUE 인덱스

NON-UNIQUE 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있다.

 

결합 인덱스

복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개 이다.

SQL> CREATE UNIQUE INDEX emp_empno_ename_indx

ON emp(empno, ename);

인덱스의 삭제

인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않는다.

인덱스를 삭제하려면 인덱스의 소유자 이거나 DROP ANY INDEX권한을 가지고 있어야 한다.

인덱스는 ALTER를 할 수 없다.

SQL> DROP INDEX emp_empno_ename_indx;

인덱스 데이터사전

인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색할 수 있다.

 

SQL> SELECT index_name, index_type

FROM USER_INDEXES

WHERE table_name='EMP';

 

INDEX_NAME              INDEX_TYPE

-------------------     -----------

EMP_DEPTNO_INDX         BITMAP

EMP_PK_EMPNO            NORMAL

 

 

(2)VIEW 테이블

VIEW?

- 뷰는 하나의 가상테이블이다

- 뷰는 실제 데이터가 저장되지는 않지만 데이터를 관리 할 수 있다.

- 뷰는 복잡한 쿼리를 통해 얻을 수 있는 결과를 간단한 쿼리로 얻을 수 있게 한다.

- 한 개의 뷰로 여러 테이블에 대한 데이터를 검색 할 수 있다.

- 특정 평가 기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있도록 한다.

 

뷰의 제한 조건

- 테이블에 NOT NULL로 만든 컬럼들이 뷰에 다 포함이 되 있어야 한다.

- ROWID, ROWNUM, NEXTVAL, CURRVAL등과 같은 가상컬럼에 대한 참조를 포함하고 있는 뷰에는 어떤 데이터로 INSERT 할 수 없다.

-WITH READ ONLY옵션을 설정한 뷰도 데이터를 갱신 할 수 없다.

-- 아래 뷰는 읽기만 가능하다.

SQL> CREATE OR REPLACE VIEW read_only

AS

SELECT empno, ename, deptno

FROM  emp

WHERE deptno = 10

WITH READ ONLY;

 

-WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는 데이터만 삽입, 삭제, 수정을 할 수 있다.

-----------부서 번호 10의 데이터를 조회 하는 뷰-------------

SQL> CREATE OR REPLACE VIEW check_option

AS

SELECT empno, ename, deptno

FROM  emp

WHERE deptno = 10

WITH CHECK OPTION ;

 

-- 부서 번호가 10인 사원만 INSERT, UPDATE할 수 있다.

SQL> INSERT INTO check_option(empno, ename, deptno)

VALUES (10005, 'jain', 30);

 

INSERT INTO check_option(empno, ename, deptno)

*

1행에 오류:

ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

-----------------------------------------------------------------------

 

뷰 문법

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name

AS subquery

[WITH CHECK OPTION [CONSTRAINT 제약조건]]

[WITH READ ONLY]

 

-- 뷰 생성    

SQL> CREATE OR REPLACE VIEW name_query

AS

SELECT a.ename, b.dname

FROM  emp a, dept b

WHERE a.deptno = b.deptno

AND b.deptno = 20;

 

--------뷰를 이용한 조회----------

SQL> SELECT * FROM name_query;

 

ENAME                DNAME

-------------------- ----------

SMITH                RESEARCH

JONES                RESEARCH

 

(3)시퀀스의 이해 및 활용

시퀀스란?

-유일한 값을 생성해주는 오라클 객체이다

-시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다.

보통 PRIMARY KEY 값을 생성하기 위해 사용한다.

-메모리에 Cache되었을 대 시퀀스값의 액세스 효율이 증가한다.

-시퀀스는 테이블과는 독립적으로 저장되고 생성된다.

 

시퀀스 생성

CREATE SEQUENCE sequence_name

     [START WITH n] - 시퀀스의 시작 값을 시정한다

     [INCREMENT BY n]  - 시퀀스의 증가 값을 지정한다

     [MAXVALUE n | NOMAXVALUE] - 시퀀스 최대값

     [MINVALUE n | NOMINVALUE] - 시퀀스 최소값

     [CYCLE | NOCYCLE] - 최대값 도달시 순환 여부

     [CACHE | NOCACHE] - CACHE 여부, 원하는 숫자만큼 미리 만들어 Shared Pool의      Library Cache에 상주시킨다.

시퀀스 수정 ALTER SEQUENCE sequence_name ...

시퀀스 삭제 DROP SEQUENCE seq_empno...

시퀀스 데이터 딕셔너리

USER_TABLES

USER_VIEWS

USER_SEQUENCE

CURRVAL 과 NEXTVAL

1)CURRVAL: 현재 값을 반환합니다.

2)NEXTVAL: 현재 시퀀스값의 다음 값을 반환합니다.

CURRVAL, NEXTVAL 사용하기

1)NEXTVAL로 새로운 값을 생성한다.

SELECT DEPT_DEPTNO_SEQ.NEXTVAL FROM DUAL;

NEXTVAL

-----------

10 

2)시퀀스의 현재값을 알아내기 위해서 CURRVAL을 사용한다

SELECT DEPT_DEPTNO_SEQ.CURRBAL FROM DUAL;

CURRVAL

-----------

10 

3)DEPT_DEPTNO_SEQ.CURRVAL을 조회할 때마다 시퀀스의 값이 계속 증가합니다. 증가치를 10으로 지정하였으므로 실행 할때마다 시퀀스 값은 10씩 증가합니다.

SELECT DEPT_DEPTNO_SEQ.CURRVAL FROM DUAL;

NEXTVAL

-----------

10 

SELECT DEPT_DEPTNO_SEQ.CURRVAL FROM DUAL;

20

SELECT DEPT_DEPTNO_SEQ.CURRVAL FROM DUAL;

30...

 

(4)SYNONYM(동의어)  

동의어를 사용하는 이유

1)데이터베이스의 투명성을 제공하기 위해서, 다른 유저의 객체 참조할 때 사용

2)객체의 긴 이름을 사용하기 편한 짧은 이름으로 해서 SQL코딩을 단순화

3)객체를 참조하는 사용자의 오브젝트를 감출 수 있기 때문에 보안을 유지할 수 있다.

 

종류

1)Private Synonym: 전용 시노님은 특정 사용자만 이용할 수 있다.

2)Public Synonym: 공용 기노님은 공용 사용자 그룹이 소유하며 그 데이터베이스에 있는 모든 사용자가 공유한다.

[출처]http://www.gurubee.net/oracle/sql