[Oracle.11]Database Object - View

Database Object

데이터베이스는 테이블뿐만 아니라 데이터를 저장하고 이를 활용하는 다양한 Object 를 제공하고 있다.

3. View

여러 테이블을 JOIN하여 질의할 때, JOIN하는 테이블의 수가 늘어나면 질의문의 길고 복잡해 진다. 이러한 질의문이 반복해서 쓰인다면 매번 질의문을 작성하는 것도 불편하지만, 유지보수에도 많은 어려움이 있을 것이다.

이렇게 자주 사용되는 질의문은 스크립트로 만들어 두거나, STORED PROCEDURE를 사용해서 데이터베이스 서버에 저장하고 필요할 때만 호출하여 사용하는 방법이 있다.

또 하나의 방법으로 작성된 질의문을 VIEW의 형태로 데이터베이스에 저장하는 방법을 생각해 볼 수 있다. 위 처럼 긴 질의문을 VIEW로 정의하여 사용한다면 소스 코드에서 사용되는 질의문은 간결해 지며, 자연적으로 코드의 양도 줄어들어 유지보수가 편리한 장점이 있다.

- 테이블을 관리, 참조하기 위한 가상의 테이블 이다.

 - DATA를 물리적이 아닌 논리적으로 갖는 집합. 절대로 물리적인 공간을 사용하지 않는다.

 - VIEW를 생성하면 SQL문장과 함께 Dictionary에 저장된다.

 - VIEW를 조회하면 Dictionary에 저장되어 있는 해당 view의 SQL문장을 이용하여 근간이 되는 테이블을 ACCESS한다.

1) VEW 생성

CREATE [OR REPLACE] VIEW 뷰이름 AS 쿼리;

OR REPLACE는 뷰이름이 존재하면 지금의 질의에 대한 정의로 대체하라는 의미이다. 뷰의 내용을 변경할 때 사용된다.

2) VIEW 조회

VIEW 조회 역시 일반 테이블을 조회 하듯이 조회 하면 된다. ( 가상테이블 )

3) VIEW 삭제

DROP VIEW 뷰이름;

4) VIEW 사용 목적

- 검색조건의 단순화, JOIN문장의 단순화 등 여러번 조회 하거나 유지보수를 위하여 VIEW를 만들어 사용한다.

- 미리 튜닝된 SQL문으로 VIEW를 생성하여 수행속도를 향상 시킬 수 있다.

- 보안관리를 위하여 VIEW를 생성하여 접근 권한 및 테이블의 일부 칼럼을 숨기고 보여줄 수 있다.

5) Inline View

SQL > SELECT employee_id, last_name, hire_date

      FROM (SELECT * FROM employees ORDER BY hire_date)

      WHERE ROWNUM < 11;

위와 같이 Inline View는 View의 결과를 전체 질의에서 사용할 수 있도록 해준다. 따라서 매번 별도의 View를 생성하지 않아도 된다.

4. Sequence

Sequence는 연속적으로 어떤 값을 부여하는 것을 의미한다. 예를 들어 회원 테이블에 가입한 신규 회원을 식별하는 식별자로써 회원번호에 일련번호를 할당할 수 있다. 이때 회원번호에Sequence를 적용할 수 있다.

Sequence가 없더라고 중복되지 않고 연속된 일련번호를 부여할 수는 있따.

SQL > SELECT MAX(회원번호) FROM 회원;

이라는 질의 문을 통해 최대 번호를 얻은 다음 증가시키는 연산을 해주면 된다.

하지만 위의 경우 읽어야 하는 회원의 수가 많을 수록 시스템에 부하가 크게 발생할 수 있는 단점이 있다. 따라서 MAX() 함수의 사용시 발생하는 질의의 부하를 최소화할 수 있기 위해Sequence를 사용한다.

1) Sequence 만들기

CREATE SEQUENCE Sequence이름

[START WITH 숫자

INCREMENT BY 숫자] ;

2) Sequence 사용

INSERT INTO Table VALUES ( Sequence이름.NEXTVAL )

NEXTVAL이란 다음 값으로 지정된 값을 자동으로 증가 시킨다. 지정하지 않으면 시작 값 및 증가 값은 1을 가진다.

*** 참조 문헌 : 10g로 시작하는 오라클 SQL & PL/SQL - 6장 데이터를 담을 그릇을 만들자

[Oracle.10]Database Object - Index

Database Object

데이터베이스는 테이블뿐만 아니라 데이터를 저장하고 이를 활용하는 다양한 Object 를 제공하고 있다.

2.Index

테이블의 데이터에 보다 빠르게 접근하기 위해 설계되었다. 따라서 테이블과 연관이 있지만 별개의 저장 공간에 저장되며, Oracle 데이터베이스 서버는 이를 자동으로 관리한다.

2-1) Index ?

색인이라는 뜻으로 해당 테이블의 조회결과를 빠르게 하기 위해 사용한다.

보통 Index를 테이블의 특정 칼럼에 한 개 이상 주게 되면 Index Table이 따로 만들어 진다. 이Index Table에는 인덱스칼럼의 row의 값과 ROWID 값이 저장되어 지며, B-TREE 구조로 저장시켜두어 검색시 좀더 빠르게 해당 데이터를 찾는데 도움을 준다.

2-2) Index 생성시 고려 사항

 - WHERE절이나 JOIN 조건으로 자주 사용되는 칼럼

 - 모든 값이 칼럼 내에서 UNIQUE한 경우

 - 넓은 범위의 값을 가진 칼럼

 - 아주 드물게 존재하는 칼럼

 - 테이블에 관리되는 데이터양이 많고, 대개 질의할 때 전체 행의 10~15% 미만이 검색의 대상이 되는 경우 ( 참고로 Oracle Version에 따라 검색 대상의 Percentage는 다르게 나타난다.)

2-3) Index 생성

CREATE INDEX index_name

ON table_name (clumn_name [,column_name]...)

SQL > CREATE UNIQUE INDEX cust_id_idx

      ON customers_index (cust_id);

위와 같은 인덱스 생성시 Oracle은 내부적으로 아래와 같은 일을 진행한다.

 - 인덱스를 지정한 칼럼 cust_id 칼럼의 값을 모두 읽는다. (Full Table Scan이 발생)

 - cust_id 칼럼의 값에 대해 오름차순 정렬

 - ROWID와 cust_id 값을 저장하기 위해 저장 공간 (EXTENT 단위)을 할당 한다.

 - 할당 후 값을 저장한다. 만약 인덱스를 생성할 때 테이블스페이스를 지정하지 않으면 현재 접속 중인 사용자의 기본 테이블스페이스에 인덱스 cust_id_idx를 생성한다.

2-4) Index 삭제

DROP INDEX 인덱스이름

테이블이 삭제되면 Index도 삭제되며,Index의 소유자와 DROP ANY INDEX권한을 가진 사람만Index 삭제가 가능하다.

*** 참조 문헌 : 10g로 시작하는 오라클 SQL & PL/SQL - 6장 데이터를 담을 그릇을 만들자

[Oracle.9]Database Object - Table

Database Object

데이터베이스는 테이블뿐만 아니라 데이터를 저장하고 이를 활용하는 다양한 Object 를 제공하고 있다.

1. Table

테이블은 열의 집합이며, 테이블에서 데이터는 스프레드시트와 비슷한 행 및 열 서식으로 구성된다. ( Row – 고유한 레코드,  Column – 레코드 내의 필드 )

데이터베이스에서 데이터는 테이블의 형태로 관리된다. 구성요소로는 테이블 이름, 칼럼 이름,데이터 타입이며, 업무 조건에 따라 해당 칼럼에 제약 조건을 지정할 수 있다.

1) Data Type

데이터에는 다양한 값이 존재 한다. 초기 Oracle은 문자, 숫자, 그리고 날자 타입의 데이터를 제공하여 왔다. 그러나 인터넷이 보편화하면서 웹을 통해 제공되는 다양한 콘텐츠를 데이터베이스에서 관리해야 하는 요구사항이 반영되어 LOB(Large Object)타입으로 동영상이나 이미지, 장문의 글 등을 저장할 수 있도록 지원하고 있다.

1-1) CHAR Type, VARCHAR2 Type

문자는 고정 문자열을 저장하는 CHAR와 가변 문자열을 저장하는 VARCHAR2가 사용된다.

CHAR는 지정된 길이 내 문자를 저장하고 나머지는 공백으로 채우고, VARCHAR2는 지정된 길이만큼 문자를 저장하고 저장된 만큼 공간을 사용하는 차이가 있다.

CHAR는 2,000bytes까지 VARCHAR2는 4,000bytes까지 저장이 가능하다.

1-2) NUMBER Type

숫자는 NUMBER를 사용한다. NUMBER의 길이를 지정하는 경우 정수를 대상으로 길이를 제한하고 하는 경우와 소수 값에 대해 설정하는 NUMBER(4,2)와 같이 나누어 진다.

NUMBER(2) : -99부터 99까지의 두자리 정수

NUMBER(4,2) : -99.99부터 99.99까지 4자리에서 소수점이 2자리인 소수

1-3) DATE Type, TIMESTAMP Type

날짜는 DATE타입을 사용한다. 년, 월, 일, 그리고 시, 분, 초 단위까지 저장한다. IMESTAMP타입은 초 단위 이하까지 정교하게 시간을 나타내여 준다.

1-4) Table 생성

CREATE TABLE 테이블이름 (

칼럼이름1 데이터타입,

칼럼이름2 데이터타입,

....,

);

1-5) Table 변경

ALTER TABLE 테이블이름

MODIFY 칼럼이름[데이터타입];

1-6) 칼럼 추가

ALTER TABLE 테이블이름

ADD 칼럼이름[데이터타입];

1-7) 칼럼 삭제

ALTER TABLE 테이블이름

DROP COLUMN 칼럼이름;

1-8) Table 삭제

DROP TABLE 테이블이름 [CASCADE CONSTRAINTS];

1-8) 하위질의를 이용한 테이블 복사

CREATE TABEL 테이블이름

AS SELECT 복사할 컬럼이름

FROM 복사할 테이블이름;

1-9) 테이블 이름 변경

RENAME 이전테이블이름 TO 새로운테이블이름;

*** 참조 문헌 : 10g로 시작하는 오라클 SQL & PL/SQL - 6장 데이터를 담을 그릇을 만들자

[Oracle.8]DML (Data Manipulation Language) - Data Dictionary

DML ( Data Manipulation Language )

테이블의 데이터는 조회 및 추가, 수정, 삭제할 수 있어야 한다. DML은 이와 관련된 명령어를 부르는 용어로 데이터 조작 명령을 통해 테이블 내 데이터를 추가, 수정, 삭제 할 수 있다.

3. Data Dictionary

Data Dictionary라는 사전은 Oracle Database 내 저장된 모든 객체의 정보를 제공 한다. SELECT라는 명령문을 통해 테이블에 저장된 데이터를 볼 수 있다.

Data Dictionary는 데이터베이스 생성 시에 만들어 지며, 크게 3가지로 분류 한다.

1) 접두사 ‘USER_’를 갖는 사전 ( User’s View )

현재 데이터베이스에 접속한 사용자가 소유한 객체의 정보를 제공한다. 즉 사용자가 만든 모든 객체의 정보가 USER_로 시작하는 테이블에 저장되어 있다고 생각하면 된다.

SQL > CONN hr

         SELECT table_name

            FROM user_tables;

2) 접두사 ‘ALL_’을 갖는 사전 ( Expanded User’s View )

사용자가 접근 가능한 모든 스키마의 정보를 제공한다. 다른 사람으로부터 특정 객체에 접근할 수 있는 권한을 부여 받았을 때 이런 객체 정보를 ALL_로 시작하는 테이블에서 제공한다.

권한 부여

SQL > CONN scott

      GRANT SELECT ON salgrade TO hr;

조회

SQL > CONN hr

      SELECT table_name

      FROM all_tables

      WHERE table_name = ‘SALGRADE’;

3) 접두사 ‘DBA_’를 갖는 사전 ( Database Administrator’s View )

DBA_는 DBA 권한을 가진 데이터베이스 관리자만이 접근 가능한 테이블이다. 데이터베이스 관리를 위한 정보를 얻을 수 있다.

SQL > CONN system

      SELECT username

      FROM dba_users;

4) 데이터 사전에서 제공하는 내용

- 데이터베이스의 모든 스키마 오브젝트 ( 테이블, View, 시퀀스 등 ) 의 정의

- 스키마 오브젝트에 의해 현재 사용된 공간

- 칼럼들의 기본값

- 제약 조건 정보

- Oracle 사용자 이름

- 각 사용자에게 부여된 권한과 룰

- Auditing 정보

USER_OBJECTS

현재 사용자가 소유하고 있는 모든 객체들의 정보를 보여줍니다.

USER_SOURCE

사용자가 작성한 저장 프로시저의 Text Source 정보를 보여줍니다.

DBA_USERS

모든 사용자의 정보를 보여줍니다.

USER_TABLES

사용자가 소유하고 있는 모든 테이블에 대한 정보를 보여줍니다.

USER_CONSTRAINTS

사용자가 소유한 스키마 내 테이블의 제약 조건에 대한 정보를 보여줍니다.

USER_CONS_COLUMNS

사용자 소유의 테이블 내 어떤 칼럼에 제약 조건이 설정되어 있는지를 보여줍니다.

USER_INDEXES

사용자가 소유한 인덱스의 정보를 보여줍니다.

USER_IND_COLUMNS

사용자 소유의 테이블 내 어떤 칼럼에 인덱스가 설정되어 있는지 보여줍니다.

USER_VIEWS

사용자 소유의 View 정보를 보여줍니다.

*** 참조 문헌 : 10g로 시작하는 오라클 SQL & PL/SQL - 5장 데이터조작의 장애를 제거하라