오라클 객체
(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
