Oracle 기초 : 실전(14) Database Objects : Stored View, Sequence, Index

Introdunction: Database Objects

Database Objects란? Oracle 내부적으로 Database를 효율적으로 관리하기 위해 지원하는 여러가지 개체를 의미한다. 주요하게 쓰이는 Database Objects로는 Table, View, package Specification, Database Trigger, Function, Synonym, Sequence, Procedure등이 존재한다. 또한 Data Dictionary 테이블인 all_objects를 통해 더 다양한 종류의 Database Objects에 대해 조회할 수 있다.

---

Syntax and Outline of Stored View & Join View

실제 테이블에 근거하여 원하는 데이터만 선택적으로 볼 수 있는 가상의 테이블. 이는 호출하여 다회 사용이 가능하다. 또한 Stored View는 참조형 타입으로, 데이터를 보여줄 뿐 실제 데이터를 가지고 있지 않다. 하지만 view는 실제 데이터를 참조하고 있기 때문에 실데이터가 변경되면 이는 view에서도 실시간으로 반영된다. view는 다음과 같은 코드로 생성이 가능하며, 이를 위해서는 관리자로부터 view 생성 권한을 받아야 한다. 또한 유저가 가진 view는 Data Dictionary의 user_views 테이블을 통해 조회가 가능하다.

--GRANT PRIVILIGE FOR CREATING VIEW TO ACCOUNT
grant create view to ACCOUNT-NAME;

--CREATE VIEW
create view example_view 
as
select Cols
from employee;

--CREATE OR REPLACE VIEW
create or replace view example_view
as
select emp_id EMP_ID_ALIAS
from employee;

--ADDING DATA TO VIEW
update example_view 
set employee = 'emp_name'
where dept_code = 'D1';

또한 view의 특징으로는 다음과 같은 것들이 있다:

1. 실제 Column뿐만 아니라 산술 연산 처리 등을 거친 가상 열의 생성이 가능하다.
2. join을 통한 join view로 보다 확장된 활용도 가능하다.
3. view를 통해 DML처리 또한 제한적으로 가능하다.
4. or replace 옵션을 통해 해당 view가 존재하지 않는다면 새로 생성, 그렇지 않은 경우 갱신이 가능하다.

2번 항목의 경우, join을 통해 생성판 Query를 바탕으로 다음의 예시와 같이 view를 생성할 수 있다:

create or replace view example_view
as
select emp_no, dept_title
from employee E join department D
on E.dept_code = D.dept_id
where job_code = 'J3'; --add'l Condition

3번 항목에서 제한적으로 가능한 DML 처리란, update문을 통해 view에 데이터를 저장할 수 있음을 의미한다. 이는 실제 테이블에 반영되나(commit 필요) insert into view values(Vals)문을 통한 값 대입시에 Virtual Column의 값 대입은 허용되지 않는다. (SQL Error: ORA-01733: virtual column not allowed here 발생) 또한 not null Column이 존재하며 default값이 존재하지 않는 경우 view에 insert into로 값대입은 불가능하다.

위와 같은 특징으로 인해 타 계정에게 view의 접근 권한을 부여한다는 것은, 대상 Table의 한정된 일부 데이터만 조회 가능한 권한을 부여하는 것과 같으며 이는 곧 보안성 향상으로 이어진다. 또한 복잡한 Query문을 하나의 view로 생성하여 일부 데이터를 손쉽게 별도로 수집한 후 조회도 가능하다.

---

Syntax and Outline of Sequence

중복되지 않는 채번 용도의 Database Object. 정수값을 순차적으로 발행한다.
Syntax:
create sequence SEQUENCE-NAME
start with Num --Start Val
increment by Num --Increment Val
nomaxvalue (add'l) --Upper Limit
nominvalue (add'l) --Lower Limit
nocycle (add'l) --Cycle
cache Num; --Caching unit

--CREATE SEQUENCE
create sequence example_sequence
start with 1
increment by 1
nomaxvalue
nominvalue
nocycle
cache 20;

--INSERT VALS INTO TABLE
insert into example_table values (seq_num.nextval, '1');

--LOOK UP THE SEQUENCES INFO. FROM D.D.
select *
from user_sequences;

--LOOK UP THE CURRENT-VAL/NEXT-VAL
select example_seq.nextval,
example_seq.currval
from dual;

--MODIFY THE EXISTING SEQUENCE 
alter sequence example_sequence
increment by Num
nomaxvalue 
nominvalue
nocycle
nocache;

Remark:

1. cache_size는 caching의 단위를 설정하는 것으로, sequence가 메모리상 번호를 미리 가지고 있다가 부여하는 것을 말한다.

2. last_number는 다음 번수에 부여할 번호이다.

3. sequence 수정 시에 start with값은 변경이 불가하므로 sequence를 삭제 후 재생성하여야 한다.

4. 반드시 "연속된 번호"를 얻어야 하는 경우에는 sequence 객체의 cache 기능을 이용하지 말 것. 서버가 재시작된다거나 하는 경우에는 caching된 번호가 유실될 수 있다. 따라서 이러한 경우에는 no cache 선언을 해 주는 것이 권장된다. 또는 수동으로 다음 번호를 얻어올 수도 있다.(select max(no) +1 from table_name;) 단, sequence를 활용하는 경우는 대체적으로 primary key로 사용하기 위함이다. 즉, 각 행을 구문하기 위하여 고유의 식별자로 중복되지 않는 번호를 받기 위함인 것이다. 따라서 caching으로 인하여 sequence를 통해 발급받은 번호가 연속되지 않는다고 하더라도 이 값들은 서로 중복되지 않으므로 primary key로 사용하는데에는 지장이 없다.

---

Syntax and Outline of Index

Introduction - Index:
색인. sql 명령문의 처리 속도 향상을 위해서 Column에 대해 생성하는 Oracle Object로 Key, Val의 한 쌍 형식을 차용하고 있다. Key에는 Column값이 대입되며, value 행에는 저장된 주소값이 대입되어 있다.

Index의 사용은 해당 Database에서 다룰 데이터가 많으면 많을수록 선택이 아닌 필수에 가까워진다. 검색 속도가 빨라지고, 시스템 부하를 줄이므로 전체적인 성능 향상이 가능해지기 때문. 단, Index는 참조의 형태를 취하고 있지 않기 때문에 별도의 물리적 공간이 필요하고, 이로 인한 index 생성 혹은 재생성에 별도의 시간이 소요된다. 따라서 데이터의 변경 작업 빈도수가 높다면 index로 인한 성능 저하가 가능하나, 약 20만건 이상의 데이터가 존재하는 경우 index를 사용하지 않고 Database를 조회하는 경우 검색 속도로 인한 Time Out이 발생할 수 있다.

Generate Index:
선택도가 좋은, 즉 중복이 적고 고유값을 많이 가지는 Column을 기준으로 index를 설정하는 것이 좋다.(e.g.주민번호) 때문에 Oracle 내부적으로 unique, primary key Constraint가 설정된 Column에 대해 사용자가 명시하지 않아도 자동으로 index가 생성된다. 이 때 자동으로 생성된 index의 이름은 Constraint의 이름으로 자동 배정된다.

Remark: Additional Options of Index

• where Condition절에 자주 사용되는 Column을 index로 설정
• join 조건 Column으로 자주 사용되는 Column을 index로 설정
• 한 번 입력된 데이터가 자주 변경되지 않는 경우라면 index로 설정
• 약 20만건 이상의 데이터가 존재하는 경우 상기한 방식의 index 설정은 선택 아닌 필수

index and cost
where Condition문을 통한 자료 조회시에 where절에 커서를 두고 F10을 누르면 Explain plan, 즉 해당 자료 조회를 위해 oracle이 채택한 검색 방식과 그 결과의 확인이 가능하다. 여기에서 표시되는 options에 full(full scan)이라는 정보가 뜨면 이는 모든 데이터를 수동으로 검색하여 비효율적인 방식으로 결과를 도출했다는 의미이다. 그러나 index가 존재하는 Column을 검색하는 경우 options에서 full(full scan)이 아닌 index rowid, unique scan이라는 정보를 확인할 수 있다. 이는 해당 Column에 부여된 Constraint가 unique이기 때문에 자동으로 부여된 index를 통해 index rowid(각 row에 부여된 고유한 주소값)으로 접근하여 조회했다는 의미이다. 또한 options의 왼 편에 위치한 object_name을 살펴보면 해당 index의 명칭 또한 함께 확인할 수 있다. 참고로, where절의 Comparison Condition에서 동등비교, 즉 equal(=)과 like + WildCard(e.g.% , _)\중 선택하여 사용할 수 있는 경우라면 동등비교 equal을 사용하여 조회하는 것이 cost면에서 더욱 효율적이다.

단, 다음의 경우에는 index가 존재하더라도 사용되지 않는다:

1. index가 부여된 Column에 변형이 가해지는 경우. 즉 Virtual Column등의 경우 해당 가상 Column의 기반이 되는 Column이 index를 부여받은 경우라도 index를 통한 검색은 실행되지 않는다. (즉 Database 조회가 full scan으로 진행된다.)
2. null간의 비교시
3. not statements 비교시
4. 실제 DataType과 비교할 Literal이 다른 자료형인 경우. 자동 형변환이 진행되었더라도 입력한 값의 데이터 타입이 검색할 Column의 실제 데이터 타입과 다른 경우 index를 통한 검색이 이루어지지 않는다.
5. 그 외 비용기반 Optimizer(최저 비용으로 가장 빠르고 효율적인 처리 경로를 생성하는 DBMS 내부의 핵심 엔진, 즉 최적화도구)에 의해 index를 통하지 않는 선택인 경우

Syntax:
create index INDEX_NAME on TAB(COL);

--CREATE INDEX
create index IDX_NAME on employee(emp_name);

--LOOK UP THE INDEX BY DATA DICTIONARY
select *
from user_indexes UI join user_ind_columns UIC
using(index_name)
where UI.table_name = 'EMPLOYEE';

---