[2022.10.24.월] DDL(시퀀스, 뷰, 테이블, 제약조건, 인덱스)

 

1. DDL 

 

DML 

테이블에 대한 액세스 작업 (테이블 행 조회 / 추가 / 삭제 / 변경) 

SELECT

INSERT INTO

UPDATE

DELETE FROM

 

DDL

데이터베이스 객체 생성/ 삭제/ 수정 

데이터베이스 객체 (테이블, 뷰, 인덱스, 시퀀스, 함수, 프로시저, 동의어 등)

CREATE
DROP
ALTER

 

DCL

 

 

 

2. 주요 데이터베이스 객체 

 

• 테이블 : 데이터의 기본 저장단위, 행과 열로 구성되어 있다.
• 뷰 : 하나 이상의 테이블을 사용해서 만든 가상의 테이블
• 시퀀스 : 일련번호 생성기
• 인덱스 : 검색성능을 향상시키기 위해 데이터에 대한 색인을 가지고 있다.
• 동의어 : 객체에 대한 다른 이름을 제공한다.

 

 

2-1. 시퀀스

: 일련번호를 발행하는 데이터베이스 객체

• 시퀀스 생성하기
  • create sequence 시퀀스명
    increment by value : 한번에 value값 만큼 증가시킨다. 기본값은 1 
    start with value        : 시작값을 value로 지정한다. 기본값은 1
    maxvalue value       : 최대값을 value로 지정한다. 기본값은 nomaxvalue
    minvalue value        : 최소값을 value로 지정한다. 기본값을 nominvalue
    cache value             : 지정된 value개만큼 일련번호를 미리 생성해서 메모리에 캐시(저장)한다.
                                       기본값은 cache 20, nocache로 설정하면 일련번호를 미리 생성해두지 않는다.
• 시퀀스 사용하기
  • 새 일련번호 발행하기
    : 시퀀스명.nextval : 새로운 일련번호를 발행한다.
  • 현재 일련번호 조회하기
    : 시퀀스명.currval : 현재 연결된 세션에서 nextval로 발행했던 일련번호를 curval로 다시 사용할 수 있다.
                                    currval을 사용하려면 반드시 nextval이 실행된 후에만 가능하다. 
• 시퀀스 수정하기
  • alter seqeunce 시퀀스명
    increment by value : 한번에 value값 만큼 증가시킨다. 기본값은 1 
    start with value        : 시작값을 value로 지정한다. 기본값은 1
    maxvalue value       : 최대값을 value로 지정한다. 기본값은 nomaxvalue
    minvalue value        : 최소값을 value로 지정한다. 기본값을 nominvalue
    cache value             : 지정된 value개만큼 일련번호를 미리 생성해서 메모리에 캐시(저장)한다.
                                       기본값은 cache 20, nocache로 설정하면 일련번호를 미리 생성해두지 않는다.
  • * start with 값은 절대로 수정할 수 없다. 
• 시퀀스 삭제하기
  • drop seuence 시퀀스명;

 

--------------------------------------------------------------------------------
-- 시퀀스 : 일련번호를 발행하는 데이터베이스 객체 
--------------------------------------------------------------------------------

-- 시퀀스 생성하기 
create sequence sample_products_seq;
-- 위의 명령어로 시퀀스를 생성하면 자동으로 아래의 기본값이 설정된다.
-- increment by 1
-- start with 1
-- nomaxvalue 
-- nominvalue
-- cache 20

-- 한번에 10씩 증가되고, 일련번호를 캐싱하지 않도록 시퀀스 수정하기 
alter sequence sample_products_seq
increment by 10
nocache;

-- 시퀀스에서 새로운 일련번호 발행하기 (계속 증가)
select sample_products_seq.nextval from dual;

-- 시퀀스에서 현재 일련번호 조회하기 
select sample_products_seq.currval from dual;

-- 테이블에 데이터 추가시 시퀀스 사용하기 (매번 새로운 순번이 자동으로 증가)
insert into sample_products
(product_no, product_name, product_maker, product_price, product_stock)
values
(sample_products_seq.nextval, 'iphone 14 pro max', '애플', 2000000, 10);

 

 

 

2-2. 뷰

- 테이블 혹은 다른 뷰를 기반으로 하는 가상의 테이블(논리적인 테이블)

- 물리적인 저장소를 가지지 않는다. 

- 뷰를 대상으로 insert, update, delete 작업의 수행이 사실상 불가능하다. 

- 사용목적 : 복잡한 SQL문을 간단하게 작성할 수 있다. 

                    데이터에 대한 액세스를 제한할 수 있다. (데이터에 대한 보안성 강화)

                    동일한 데이터로부터 다양한 결과를 얻을 수 있다.

 

• 뷰 생성하기
  • create or replace view 뷰이름
    as select문;
    * select문의 실행결과로 획득된 데이터로 구성된 가상의 테이블을 정의하는 것 
    (인라인뷰는 임시테이블로서 사용후에 종료되지만, 뷰는 영구적임)
• 뷰 삭제하기
  • drop view 뷰이름;

 

--------------------------------------------------------------------------------
-- 뷰 
--------------------------------------------------------------------------------

-- 뷰 생성하기
-- 부서아이디, 부서명, 관리자아이디, 관리자명, 소속된 사원수, 
-- 소재지아이디, 소재지주소, 소재지우편번호, 소재지도시명, 소재지국가명으로 구성된 뷰(가상의 테이블) 생성하기
create or replace view dept_details_view
as
select d.department_id      as dept_id,
       d.department_name    as dept_name,
       d.manager_id         as manager_id,
       m.first_name         as manager_name,
       nvl(x.emp_counts, 0) as emp_counts,
       l.location_id        as loc_id,
       l.street_address     as address,
       l.postal_code,
       l.city,
       l.country_id,
       c.country_name
from (select department_id, count(*) emp_counts
      from employees
      where department_id is not null
      group by department_id) x, departments d, employees m, locations l, countries c
where x.department_id(+) = d.department_id      
and d.manager_id = m.employee_id(+)
and d.location_id = l.location_id
and l.country_id = c.country_id;


-- 뷰를 활용해서 부서아이디, 부서명, 직원수 조회하기 
select dept_id, dept_name, emp_counts
from dept_details_view;

 

 

 

2-3. 테이블

: 데이터의 실질적인 저장소 역할을 수행하는 데이터베이스 객체

• 테이블과 컬럼의 이름규칙
  • 문자로 시작, 30자까지 가능, A-Za-z0-9_$# 만 허용,
  • 오라클 예약어 사용금지
    
    
• 테이블 생성하기
  
  • create table 테이블명 (
             컬럼명          데이터타입(크기)     제약조건,
             컬럼명          데이터타입(크기)     default 기본값,
             컬럼명          데이터타입,
             ....
    ) ;
    
    
• 오라클의 데이터타입
  • 문자 데이터타입
    varchar2      : 가변길이 문자데이터타입, 최대길이 4000바이트, 크기 지정 필요
    char             : 고정길이 문자데이터타입, 최대길이 2000바이트, 크기 지정 필요 
    long             : 대용량 문자데이터타입, 최대길이 2기가 바이트, 테이블당 하나만 정의할 수 있다.
    clob             : 대용량 문자데이터타입, 최대길이 4기가 바이트
    * 한글은 1글자가 3바이트
    
    
  • 숫자 데이터타입
    number        : 가변길이 숫자 데이터타입, number(p, s)에서 p는 총 숫자개수, s는 소수점이하 자리수
    
    
  • 날짜 데이터타입
    date               : 날짜 및 시간 데이터 타입, 연월일 시분초 정보를 포함한다.
    timestrap      : 날짜 및 시간 데이터 타입, 연월일 시분초 및 소수점 이하 초까지 포함한다. 
    
    
• 테이블 삭제하기
  • drop table 테이블명;
    
    
• 테이블 이름 변경하기
  • rename 테이블명 to 새테이블명;
    
    
• 테이블 잘라내기 : 테이블의 모든 데이터를 삭제하고, 테이블을 최초 생성시점과 동일한 상태로 만들어준다.
  •  truncate 테이블명;

 

 

2-4. 무결성 제약조건

- 테이블에 유효하지 않은 데이터가 저장되지 않게 한다. 

- 테이블에 데이터 추가/삭제/변경 될 때마다 무결성 제약조건에 위배되는지 검사한다.

 

 

1) 무결성 제약조건의 종류 

 

• NOT NULL 제약조건
  - 해당 컬럼이 NULL값을 가질 수 없도록 함
  - 예) 고객아이디, 고객명, 비밀번호, 상품명 등의 컬럼은 NULL값을 가질 수 없다. 
  - 작성예)
     CREATE TABLE SAMPLE_USERS (
             USER_NAME     VARCHAR2(100)     NOT NULL,
             ...
      );
  
  
• UNIQUE 제약조건 (고유키 제약조건) 
  - 해당 컬럼의 값은 테이블의 모든 행에 대해서 고유한 값을 가져야 한다. (중복값을 허용하지 않는다.) 
  - 둘 이상의 컬럼을 합쳐서 정의할 수도 있다.
  - NOT NULL 제약조건이 정의되어 있지 않으면 NULL 값은 중복이 가능하다. 
  - 예) 이메일 컬럼은 중복값을 가질 수 없다.
  - 작성예) 
     CREATE TABLE SAMPLE_USERS (
            USER_EMAIL     VARCHAR2(255)     UNIQUE,
            ...
      );
  
  
• PRIMARY KEY 제약조건 (기본키 제약조건) 
  - 테이블의 각 행을 대표하는 컬럼에 정의되는 제약조건이다.
  - 테이블 당 하나만 정의할 수 있다.
  - 둘 이상의 컬럼을 합쳐서 정의할 수도 있다.
  - 해당 컬럼의 값은 테이블 전체에서 고유한 값이어야 하고, NULL값을 허용하지 않는다.
  - 일반적으로 해당 컬럼은 숫자타입인 경우가 많다.  
  - 예) 상품번호, 주문번호, 학생번호, 고객아이디 등의 컬럼은 행을 대표하는 컬럼이다.
  - 작성예) 
     CREATE TABLE SAMPLE_USERS (
            PRODUCT_NO     NUMBER(8, 0)     PRIMARY KEY,
            ...
      )
  
  
• CHECK 제약조건
  - 제시된 조건을 만족하는 값만 가질 수 있게 한다.
  - 예) 점수(0~100), 고객등급(골드, 실버, 브론즈, 비회원), 성별(남, 여) 등의 컬럼은 제시된 값만 가져야 한다.
  - 작성예)
     CREATE TABLE SAMPLE_SCORES (
             KOR_SCORE       NUMBER(3, 0)   CHECK  (KOR_SCORE >= 0 AND KOR_SCORE <= 100), 
      )
      CREATE TABLE SAMPLE_USERS (
              USER_GENDER    CHAR(1)          CHECK (USER_GENDER IN ('M', 'F')),
      ) 
• FOREIGN KEY 제약조건 (외래키 제약조건, 참조키 제약조건)
  - 해당 컬럼의 값이 다른 테이블(혹은 같은 테이블)의 특정 컬럼이 가지고 있는 값과 관련있는 값만 가져야 한다. 
  - 다른 컬럼이 참조하는 컬럼은 반드시 기본키 제약조건 혹은 고유키 제약조건이 정의된 컬럼만 가능하다. 
    ( -> 중복된 값이 없어야 참조할 수 있음 )
  - 예시) 직원테이블의 부서아이디는 부서테이블의 부서아이디 컬럼의 값과 일치하는 값만 허용된다. 
              부서테이블의 관리자아이디는 직원테이블의 직원아이디 컬럼의 값과 일치하는 값만 허용된다.
              주문테이블의 주문자아이디는 사용자테이블의 사용자아이디 컬럼의 값과 일치하는 값만 허용된다.
              예매테이블의 공연번호는 공연테이블의 공연번호 컬럼의 값과 일치하는 값만 허용된다. 
  - 작성예) 
     CREATE TABLE SAMPLE_USERS (
            USER_ID    VARCHAR2(20)     PRIMARY KEY,
            ...
      )
     CREATE TABLE SAMPLE_ORDERS (
            ORDER_USER     VARCHAR2(20)    REFERENCES   SAMPLE_USERS (USER_ID),  
            * ORDER_USER 컬럼의 값은 SAMPLE_USERS 테이블의 USER_ID 컬럼의 값과 일치하는 값만 허용
      )
  
  

 

2) 제약조건 정의하기

 

• 제약조건 정의방법
  • 컬럼레벨 제약조건 정의하기
    
    • CREATE TABLE 테이블명 (
                컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
                컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
                컬럼명 데이터타입 [CONSTRAINT 제약조건별칭] 제약조건,
                ...
       )
  • 테이블레벨 제약조건 정의하기
    
    • CREATE TABLE 테이블명 (
                컬럼명 데이터타입, 
                컬럼명 데이터타입, 
                컬럼명 데이터타입, 
                ...
                [CONSTRAINT 제약조건별칭] 제약조건 (컬럼명), 
                [CONSTRAINT 제약조건별칭] 제약조건 (컬럼명, 컬럼명), 
      
                * NOT NULL 제약조건을 제외한 나머지 제약조건은 테이블레벨 제약조건으로 정의할 수 있다.
                * 하나의 컬럼에 제약조건을 여러개 정의해야 할 경우 테이블레벨 제약조건으로 정의하면 된다.
                * 두개 이상의 컬럼을 이용해서 제약조건을 정의해야할 때 테이블레벨 제약조건으로 정의하면 된다. 
                * 일반적으로 하나의 컬럼에 NOT NULL 제약조건과 다른 제약조건을 같이 정의해야 할 때, 
                   NOT NULL 제약조건은 컬럼레벨 제약조건, 다른 제약조건은 테이블레벨 제약조건으로 정의한다.
                * 테이블을 정의할 때  컬럼레벨 제약조건과 테이블레벨 제약조건을 혼용해서 사용하는 경우가 대부분
          ) 

 

3) insert, depete, update 할 때 무결성 제약조건 검사

 

 

* 별칭을 통해 어떤 테이블의 어떤 무결성 제약조건 위배인지 알아내야 함 

 

• not null 무결성 제약조건 위배
  ORA-01400: NULL을 ("HR"."SAMPLE_USERS"."USER_PASSWORD") 안에 삽입할 수 없습니다
  (hr사용자의 sample_users테이블의 user_password)
  
  
• unique 무결성 제약조건 위배
  ORA-00001: 무결성 제약 조건(HR.USERS_EMAIL_UK)에 위배됩니다 
  (hr계정의 별칭 - users테이블의 email이 unique가 걸려있네) 
  
  
• primary key 무결성 제약조건 위배
  ORA-00001: 무결성 제약 조건(HR.USERS_ID_PK)에 위배됩니다
  
  
• check 무결성 제약조건 위배
  ORA-02290: 체크 제약조건(HR.USERS_GENDER_CK)이 위배되었습니다
  
  
• foreign key 무결성 제약조건 위배
  ORA-02291: 무결성 제약조건(HR.CART_PRODUCT_NO_FK)이 위배되었습니다- 부모 키가 없습니다 
  (참조할 product_no가 없음)
  ORA-02291: 무결성 제약조건(HR.CART_USER_ID_FK)이 위배되었습니다- 부모 키가 없습니다 
  (참조할 user_id가 없음)

 

아래 예시로 확인하기 

 

--------------------------------------------------------------------------------
-- 제약조건
--------------------------------------------------------------------------------

-- 제약조건이 정의된 사용자 테이블 생성하기
-- 제약조건
-- 사용자 아이디 : 기본키
-- 사용자 이름, 비밀번호, 이메일 : null 값을 허용하지 않는다.
-- 이메일 : 중복을 허용하지 않는다.
-- 성별 : '남', '여' 만 가능
create table sample_users(
    user_id             varchar2(20)                        constraint users_id_pk primary key,    -- users_id_pk는 제약조건 별칭 (테이블명_컬럼명_제약조건약어)  
    user_password       varchar2(20)                        not null,
    user_name           varchar2(100)                       not null,
    user_email          varchar2(255)                       not null,       -- unique 제약조건은 테이블 레벨 제약조건으로 정의
    user_tel            varchar2(20),
    user_gender         char(3)                             constraint users_gender_ck  check (user_gender in ('남', '여')),
    user_point          number(8)       default 0,
    user_enabled        char(1)         default 'y'         constraint users_enabled_ck check (user_enabled in ('y', 'n')),
    user_created_date   date            default sysdate,
    user_updated_date   date            default sysdate, 
    -- 테이블레벨 제약조건
    constraint users_email_uk unique (user_email)
);    

-- 제약조건이 정의된 상품 테이블 생성하기
-- 제약조건
-- 상품번호 : 기본키
-- 상품이름 : null 값을 허용하지 않는다.
-- 상품가격 : 0보다 큰 값
-- 가격할인율 : 0보다 크거나 같고, 1보다 작다.
create table sample_products(
    product_no              number(8)                       constraint products_no_pk primary key,
    product_name            varchar(255)                    not null, 
    product_maker           varchar2(100), 
    product_price           number(8)                       constraint products_price_ck check (product_price >= 0),
    product_discount_rate   number(2, 2)                    constraint products_discount_rate_ck 
                                                            check (product_discount_rate >= 0 and product_discount_rate < 1), 
    product_stock           number(6),
    product_on_sell         char(1)     default 'y'         constraint product_on_sell_ck 
                                                            check (product_on_sell in ('y', 'n')), 
    product_created_date    date        default sysdate,
    product_updated_date    date        default sysdate
);    


-- 제약조건이 포함된 장바구니 테이블 생성하기
-- 제약조건
-- 사용자 아이디: sample_users 테이블의 user_id 컬럼의 값만 허용
-- 상품번호 : sample_products 테이블의 product_no 컬럼의 값만 허용 
-- 사용자아이디 + 상품번호 : 테이블의 행을 대표하는 값
create table sample_cart_items (
    user_id varchar2(20) constraint cart_user_id_fk references sample_users (user_id),
    product_no number(8),
    item_amount number(3),
    item_created_date date default sysdate,
    item_updated_date date default sysdate,
    
    constraint cart_product_no_fk foreign key (product_no) references sample_products (product_no),
    constraint cart_item_pk primary key (user_id, product_no)
);


--------------------------------------------------------------------------------
-- 무결성 제약조건과 insert, update, delete 
-- * insert, update, delete 작업을 수행할 때마다 무결성 제약조건 위배여부를 반드시 검증한다. 
-- 별칭을 통해 어떤 테이블의 어떤 무결성 제약조건 위배인지 알아내야 함 
--------------------------------------------------------------------------------

-- not null 무결성 제약조건 위배 : not null 제약조건이 정의된 user_password에 null이 삽입되는 경우 
-- ORA-01400: NULL을 ("HR"."SAMPLE_USERS"."USER_PASSWORD") 안에 삽입할 수 없습니다 (hr사용자의 sample_users테이블의 user_password)
insert into sample_users (user_id, user_name, user_email, user_gender)
values ('hong', '홍길동', 'hong@naver.com', '남'); 


-- uniue 무결성 제약조건 위배 : unique 제약조건이 정의된 user_email에 동일한 이메일 주소가 삽입되는 경우 
-- ORA-00001: 무결성 제약 조건(HR.USERS_EMAIL_UK)에 위배됩니다 (hr계정의 별칭 - users테이블의 email이 unique가 걸려있네) 
insert into sample_users (user_id, user_password, user_name, user_email, user_gender)
values ('hong1', 'zxcv1234', '홍길동1', 'hong1@gmail.com', '남');

insert into sample_users (user_id, user_password, user_name, user_email, user_gender)
values ('hong2', 'zxcv1234', '홍길동1', 'hong1@gmail.com', '남');


-- primary key 무결성 제약조건 위배 : primary key 제약조건이 정의된 user_id에 동일한 아이디값이 삽입되는 경우 
-- ORA-00001: 무결성 제약 조건(HR.USERS_ID_PK)에 위배됩니다
insert into sample_users (user_id, user_password, user_name, user_email, user_gender)
values ('hong1', 'zxcv1234', '홍길동1', 'hong1@gmail.com', '남');


-- check 무결성 제약조건 위배 : check 무결성 제약조건이 정의된 user_gender에 '남', '여' 외의 값이 삽입되는 경우 
-- ORA-02290: 체크 제약조건(HR.USERS_GENDER_CK)이 위배되었습니다
insert into sample_users (user_id, user_password, user_name, user_email, user_gender)
values ('hong2', 'zxcv1234', '홍길동2', 'hong1@gmail.com', '곰');


-- 외래키 제약조건 테스트를 위한 샘플 데이터 등록 
insert into sample_users (user_id, user_password, user_name, user_email, user_gender)
values ('kim', 'zxcv1234', '김유신', 'kim@naver.com', '남');
insert into sample_users (user_id, user_password, user_name, user_email, user_gender)
values ('kang', 'zxcv1234', '강감찬', 'kang@naver.com', '남');
insert into sample_users (user_id, user_password, user_name, user_email, user_gender)
values ('ryu', 'zxcv1234', '류관순', 'ryu@naver.com', '여');
insert into sample_products (product_no, product_name, product_maker, product_price, product_discount_rate, product_stock)
values (sample_products_seq.nextval, '아이폰14', '애플', 1000000, 0, 10);
insert into sample_products (product_no, product_name, product_maker, product_price, product_discount_rate, product_stock)
values (sample_products_seq.nextval, '아이폰14 프로 맥스', '애플', 1700000, 0, 10);
insert into sample_products (product_no, product_name, product_maker, product_price, product_discount_rate, product_stock)
values (sample_products_seq.nextval, '맥북 프로', '애플', 2700000, 0, 5);


-- foreign key 제약조건 위배 : 외래키 제약조건이 정의된 product_no에는 sample_products의 product_no 컬럼에 있는 값만 삽입가능하다. 
-- ORA-02291: 무결성 제약조건(HR.CART_PRODUCT_NO_FK)이 위배되었습니다- 부모 키가 없습니다 (참조할 product_no가 없음)
insert into sample_cart_items (user_id, product_no, item_amount)
values ('kim', 100, 1);
-- ORA-02291: 무결성 제약조건(HR.CART_USER_ID_FK)이 위배되었습니다- 부모 키가 없습니다 (참조할 user_id가 없음)
insert into sample_cart_items (user_id, product_no, item_amount)
values ('kim1', 71, 1);

insert into sample_cart_items (user_id, product_no, item_amount)
values ('hong1', 71, 1);
insert into sample_cart_items (user_id, product_no, item_amount)
values ('kim', 81, 1);
insert into sample_cart_items (user_id, product_no, item_amount)
values ('kang', 91, 5);

-- sample_products 테이블의 상품정보 삭제하기 
-- sample_cart_items 테이블의 product_no가 sample_products의 product_no 컬럼의 값을 참조한다. 
-- sample_cart_items product_no 컬럼에 상품번호가 사용되면(참조하고 있으면), sample_products에서 그 상품정보를 삭제할 수 없다.
-- (sample_products에 상품번호가 있는데 cart_items에서 사용되지 않으면(참조되지 않으면) 상품번호 삭제가능) 
-- ORA-02292: 무결성 제약조건(HR.CART_PRODUCT_NO_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
delete from sample_products
where product_no = 71;

-- sample_users의 사용자 정보 삭제하기
-- ORA-02292: 무결성 제약조건(HR.CART_USER_ID_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
delete from sample_users 
where user_id = 'kang';

-- sample_users의 사용자 정보 변경하기
-- ORA-02292: 무결성 제약조건(HR.CART_USER_ID_FK)이 위배되었습니다- 자식 레코드가 발견되었습니다
-- sample_cart_items의 user_id가 sample_users의 user_id 컬럼을 참조하고 있기 때문에   
update sample_users
set 
    user_id = 'hong'
where user_id = 'hong1';

 

 

 

 

2-5. 인덱스 

 

1) 인덱스

• 데이터 행의 검색 속도를 향상시키기 위해서 사용되는 데이터베이스 객체다.
• 데이터의 위치를 빠르게 찾는 신속한 경로 엑세스 방법을 사용하여 디스크 I/O를 줄여 준다.
• 인덱스는 테이블과 독립적으로 존재한다.
• 한 번 생성된 인덱스는 Oracle이 자동으로 유지 관리한다.
• 테이블이 삭제되면 그 테이블의 데이터를 색인화하고 있는 인덱스도 같이 삭제된다.
• 인덱스 생성
  • 자동 생성
    • 테이블 정의할 때 primary key, unique 제약조건이 정의된 컬럼의 값들은 자동으로 인덱스가 생성된다.
  • 수동 생성
    • 사용자가 행에 대한 엑세스 시간을 줄이기 위해서 특정 열을 대상으로 인덱스를 생성할 수 있다.
    • create index 명령어 사용
• 인덱스 생성이 필요한 경우
  • where 절에 조회 조건으로 자주 사용되는 컬럼
  • 매우 다양한 값을 포함하고 있는 컬럼
  • 데이터가 매우 많은 테이블을 대상으로 조회작업을 수행했을 때 대부분의 조회작업에서 전체 데이터의 2%~4% 미만의 데이터가 조회되는 컬럼 (똑같은 값이 많은 경우에는 인덱스를 사용해서 찾는게 오히려 성능 저하)
• 인덱스 생성이 필요하지 않은 경우
  
  • 테이블에 지정된 데이터가 적은 경우
  • 테이블의 데이터가 자주 갱신되는 경우 (수정이 빈번한 경우)
  • 인덱스가 생성된 컬럼이 표현식의 일부로 사용되는 경우
    (인덱스 컬럼을 substr(~) 이런식으로 쓰면 인덱스에 담긴 그대로의 값이 아니기 때문에 인덱스 못씀 )
  • where절의 조회 조건으로 자주 사용되지 않는 경우
  • 대부분의 조회작업에서 전체 데이터의 2%~4%이상이 조회되는 경우 

 

 

2) 실행 계획 (Execute Plan)

• 사용자가 SQL을 실행해서 데이터를 조회할 때 옵티마이저가 수립하는 작업 절차를 말한다.
  
  * 옵티마이저 (Optimizer)
    - 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 경로를 생성하는 DBMS의 핵심 엔진이다.  
    - where절에서 index를 활용해서 알아서 최적경로를 빠르게 찾음
    - 비슷한 구조의 쿼리가 실행되는 경우 이전에 했던 쿼리를 찾아서 최적경로를 만듦
    - sql 전달할 때 ? 값으로 전달하는 이유 (구조는 똑같이 실행하고, ? 값에 입력되는 값만 실행계획을 짬) 

 

index가 있는 것 -> 계획설명의 cost(비용)가 낮음 ( = 빠름 ) => 검색 성능 향상

index가 없는 것 -> 계획설명의 cost(비용)가 높음 ( = 느림 ) 

 

 

 

3) ROWID 

• 테이블에서 행의 고유주소를 나타내는 64진수 숫자 
  • 구성
    AAAStG + AAH + AAAAGj + AAA = 오브젝트번호 + 파일번호 + 블록번호 + 데이터번호 
    AAAStB + AAH + AAAAGF + AAA
    - 오브젝트 번호 : 객체(테이블)마다 고유한 번호
    - 파일번호 : 해당 데이터가 위치하고 있는 테이블 스페이스의 파일번호
    - 블록번호 : 파일내부의 블록번호
    - 데이터번호 : 데이터가 저장되어 있는 데이터 디렉토리의 슬롯번호

 

select rowid, employee_id, first_name
from employees;

 

 

 

 

select rowid, department_id, department_name
from departments;

 

 

 

 

* 인덱스 생성

-- employees 테이블의 first_name 컬럼에 대해서 인덱스를 생성한다.
create index emp_first_name_idx		-- 인덱스명 정의
on employees (first_name);		-- 어떤 테이블의 어떤 컬럼에 인덱스를 설정할 건지 

select *
from employees
where first_name = 'Steven';