[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장 데이터조작의 장애를 제거하라

[Oracle.7]DML (Data Manipulation Language) - Constraints

DML ( Data Manipulation Language )

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

2. Constraints

Constraints란 말 그래도 테이블에 추가, 변경, 삭제되는 데이터를 제약하고자 하는 것이다.

데이터의 Integrity를 유지하기 위해 사용되며, Integrity란 데이터의 결함이 없는 것을 말한다.예를 들어 사원번호라 함은 값이 꼭 할당 되어야 하며, 중복되지 않아야 데이터의 결함이 발생하지 않게된다. Oracle은 이러한 제약을 KEY가 가지는 요건을 만족하는 데이터만을 칼럼에 저장하며, 그 외 값을 받아들이지 않는다. 그러면 이러한 KEY에 대해 살펴 보자.

1) NOT NULL

NOT NULL은 NULL을 허용하지 않으므로 반드시 입력되어야 한다. NOT NULL로 제약조건을 설정하면 데이터베이스에서는 해당 칼럼에 NULL 값이 추가되거나 NULL 값으로 변경되면 에러를 발생시킨다.

2) UNIQUE

UNIQUE 제약 조건은 칼럼에 중복된 값을 허용하지 않는다. 즉 유일한 하나의 값만 저장될 수 있다.

3) PRIMARY KEY

위의 두 가지 사항 ( NOT NULL과 UNIQUE ) 을 모두 만족해야 하는 것이 PRIMARY KEY이다.즉 PRIMARY KEY는 주로 사원 테이블의 사원번호나 부서 테이블의 부서번호와 같이 유일하게 행을 식별하는 식별 자로서 역할을 하는 칼럼에 적용이 된다.

4) FOREIGN KEY

FOREIGN KEY는 의미에서 보듯 외국, 외래의 의미가 담겨 있다. 예를 들어 새로 들어온 사원을 입력할 때 부서번호는 현존 부서의 번호 이여야 한다. 즉 현존하는 부서번호가 아닌 값을 입력 받는다면 그 사람은 현재 존재하지 않는 부서에 속해있는 사람이 되어 데이터에 결함을 가지게 된다. 이를 방지하기 위해 사원 테이블의 부서번호 칼럼에 FOREIGN KEY를 설정하면 값이 입력,변경 될 때 부서 테이블의 주 식별자인 부서번호를 참고하여 부서번호가 존재할 때 사원 테이블 부서번호 칼럼에 값을 받아들일 수 있도록 해 준다.

삭제를 진행할 때에도 부서가 먼저 삭제되면 사원은 부서가 없어지게 되어 데이터의 결함이 발생하므로 FOREIGN KEY로 연결된 사원 데이터 먼저 삭제하고 부서를 삭제해야 한다.

5) CHECK

CHECK 제약조건은 입력되는 값을 체크하여 설정된 값 이외의 값은 받아들이지 않도록 한다. 예를 들어 SALARY > 0 조건을 설정하면 SALARY가 음수인 경우 받아 들이지 않는다.

제약조건	표기	설명
UNIQUE	UK	중복된 값을 허용하지 않습니다.
NOT NULL	NN	NULL 값을 허용하지 않습니다.
PRIMARY KEY	PK	UNIQUE와 NOT NULL의 조건을 만족하는 값을 허용합니다.
FOREIGN KEY	FK	참조하는 테이블의 칼럼의 값이 존재하면 값을 허용합니다.
CHECK	CK	제약조건을 설정할 때 설정한 값만을 받아들입니다.

6) MERGE

MERGE란 하나 혹은 그 이상의 테이블에 대한 INSERT, UPDATE 작업을 위해 하나 혹은 그 이상의 소스로부터 질의할 때 사용하는 명령어 이다. 이 명령어는 여러 개의 동작을 조합하여 실행할 때 유용하며, 여러 번의 INSERT, UPDATE, DELETE 작업을 피할 수 있는 방법이 된다.

MERGE INTO 테이블이름

USING SELECT 구문

ON 조건

WHEN MATCHED THEN

       UPDATE 구문

       [DELETE 구문]

WHEN NOT MATCHED THEN

       INSERT 구문

[WHERE 절];

SQL > MERGE INTO bonuses b

      USING (SELECT employee_id, salary, department_id FROM employees ) e

      ON ( e.employee_id = b.employee_id )

   

      WHEN MATCHED THEN

            UPDATE SET bonus = bonus + salary * 0.1

            DELETE WHERE salary > 8000

      WHEN NOT MATCHED THEN

            INSERT VALUES ( e.employee_id, salary * 0.1 )

            WHERE salary <= 8000;

7) LOCK

DML 작업을 수행하기 위해서 테이블에 질의를 수행하고 데이터를 조작한다. 이때 테이블에 발생하는 Transaction은 먼저 수행한 세션에서 Transaction을 종료하지 않으면 그 이후 같은 행에 대해 이루어지는 Transaction에 대해 LOCK이 발생한다.

이러한 LOCK은 동시에 여러 사용자가 접속해서 사용하는 데이터베이스 환경에서는 제공되어야 하나 Waiting으로 인한 처리 속도의 저하가 문제가 될 수 있다. 따라서 긴 Transaction은 사용자의 사용이 적은 시간대를 선택하여 작업하며, 가능한 한 짧게 Transaction을 수행하는 것이Waiting을 최소화할 수 있는 방법이 될 수 있다. 

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