310820

지난 주 복습

 

데이터 베이스의 특징 4가지

1. 공용

2. 통합

3. 저장

4. 운영

 

필요한 데이터를 공동으로 사용할 수 있도록 중복을 최소화하여 통합 / 저장하는 데이터 집합체

 

 

---

create user stuoy identified by (passwords / 문자로 하려면 ' '로 감싸고) ;

 

alter user stuoy identifiedy by (passwords/ 변경할 비밀번호 입력) account unlock ;

 

drop user stuoy

 

truncate table stuoy1 

emp c를 임시 테이블에 저장하고,

emp c를 지우고

임시 테이블을 emp c로 이름을 변경하는 과정이 생략되어 있다.

 

: 비우는 것 / TUPLE의 갯수(Cardinality) 가 많을 때 , 속도가 빠르다.

(JAVA에 SWAP? - 

table일 경우: 

 

권한 부여 : resource / connect > 사전에 관리자 계정으로 접속해야 한다.

p.22

resource :  개체를 생성, 변경, 제거할 수 있는 권한 (DDL, DML 사용 가능)

connect : DB에 연결할 수 있는 권한

 

grant ( ) to stuoy 

connect;

resource;

 

 퉁 치기 위해서

create role role_test01; --role 하나를 만들고

grant connect, resource to role_test01;

grant role_test01 to stuoy1, scott, ....;

 

 

사용자 계정 조회 (관리자 계정으로 접속)

select *

from dba_users

;

 

권한 뺏기

revoke

형태는 grant랑 똑같다.

 

동의어

1. 공개동의어

 

 

 

2. 비공개동의어

 

sys에서 권한 확인 방법

select *

from dba_sys_privs -- 권한에 대한 것들 나열

 

---

DB's OBJECT.

 

INDEX

 

• '색인'이라고도 불리며, DB 객체 중 하나
• table에 index를 작성하게 된다.
• 테이블과 별개의 독립된 객체로 작성되지만 테이블에 의존적이다.
• 기본키나 유일키와 같은 제약 조건이 지정되면 따로 인덱스를 생성하지 않더라도 해당 키에 대한 인덱스가 자동 생성된다.

---

장점

• 검색 속도가 빠르다
• 시스템의 부하를 줄여 전체적인 성능을 향상시킨다.

단점

• 인덱스 생성에 시간이 필요하다
• 인덱스를 위한 추가적인 공간이 필요하다
• 검색(DQL) 외 데이터 변경(DML)이 빈번히 일어날 경우, 성능이 저하될 수 있다.

---

필요한 경우

• 테이블의 행의 수가 많을 경우
• WHERE문에 특정 COLUMN이 많이 사용될 때
• JOIN문에 COLUMN이 많이 사용될 때
• 검색 결과가 전체 데이터의 2 ~ 4 %정도일 때
• NULL을 포함하는 행이 많은 COLUMN일 경우



불필요한 경우

• 데이터가 적은 (수 천 건 미만) 경우
• DQL보다 DML 사용이 잦은 테이블일 경우
• 조회 결과가 전체 행의 15% 이상을 나타낼 경우

---

종류 및 생성법

 

고유 인덱스 (Unique Index)

• 중복되지 않는 데이터를 인덱스의 키 값으로 지정하는 인덱스
• 성능 good
• 인덱스를 생성한 컬럼에 대해서 중복되는 값을 테이블에 삽입하려 하면 에러 발생

고유 인덱스 생성법

CREATE UNIQUE INDEX index_name ON table_name(column_name)
;

 

비고유 인덱스 (Non-Unique Index)

• 중복되는 데이터를 인덱스의 키 값으로 지정하는 인덱스
• 생성

비고유 인덱스 생성법

CREATE INDEX index_name ON table_name(column_name)
;

 

함수 기반

• 인덱스를 사용하려면 WHERE 절의 조건을 다른 형태로 가공해서는 안된다.
• 만약 WHERE절의 조건으로 'sal + 100'과 같이 가공된 형태로 사용하고,  인덱스 역시 생성하고 싶을 때 이 함수 기반 인덱스를 생성한다.
• 인덱스의 생성으로 검색 속도 향상은 가능하지만, DB의 성능 향상을 기대하긴 어렵고, 쿼리 조건이 바뀌면 인덱스를 다시 생성해야 한다는 단점이 있다.

함수 기반 인덱스 생성법

CREATE INDEX index_name ON table_name(column_name 가공형태)
;

--ex
CREATE INDEX index_sal_add_bonus ON emp(sal+100)
;

 

결합

• 두 개 이상의 COLUMN을 합쳐서 인덱스 생성
• WHERE 절의 조건 컬럼이 2개 이상 AND로 연결되어 사용되는 경우 많이 이용된다.

결합 인덱스 생성법

CREATE INDEX index_name ON table_name(column_name1, column_name2,...)
;

 

BITMAP

• 데이터 값의 종류가 적고, 동일한 데이터가 많을 경우 사용 - 도메인 값이 적을 경우                                                                               예) 성별 컬럼: 남, 여
• 데이터의 변경양이 적고 값의 종류가 적을 경우
• 데이터 위치 정보를 BIT로 표시
• BITMAP 인덱스 생성 이후 새로운 컬럼 값이 생긴다면 인덱스를 전부 수정해야 한다는 단점

BITMAP 인덱스 생성법

CREATE BITMAP INDEX index_name ON table_name(column_name)
;

 

---

 

그 외 활용

제거

인덱스 제거

DROP INDEX index_name
;

재생성

• 대량의 DML 작업을 수행하면 인덱스의 성능이 저하되므로 재설정을 해주는 것이 좋다.
• 꾸준한 관리 필요

인덱스 재생성

ALTER INDEX index_name REBUILD
;

조회

• USER_INDEXES 뷰를 이용해 인덱스 정보 조회 가능

인덱스 조회

SELECT table_name, index_name, index_type
FROM user_indexes
WHERE table_name = '테이블명'
;

 

 

---

 

VIEW

 

• 물리적인 테이블에 근거한 논리적인 가상의 테이블
• 데이터가 없고 테이블에 대한 SQL만 저장되어 있다.
• 사용자가 VIEW에 접근할 경우 SQL이 수행되면서 그 결과를 가져온다.

 

장점

• 여러 테이블에 대한 검색결과를 뷰 하나로 검색할 수 있다.
• 복잡하고 긴 쿼리문을 뷰로 정의하면 접근을 단순화할 수 있다.

활용

VIEW 기본 문법

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_name  
AS subquery
[ WITH CHECK OPTION [ CONSTRAINT 제약 조건] ]
[ WITH READ ONLY]
;

 

CREATE : 뷰 생성

CREATE OR REPLACE : 생성 혹은 같은 이름의 뷰가 존재할 때에는 질의 변경

FORCE : 기본 테이블의 유무에 관계없이 뷰를 생성 -- 뷰는 일반적으로 기본테이블이 존재한다는 가정 하에 쿼리문을 작성하지만, 이 옵션을 이용해서 간혹 존재하지 않는 테이블을 대상으로 뷰를 생성할 수 있다.

 (기본값은 NOFORCE, 생략 가능)

WITH CHECK OPTION : 제약 조건에 맞는 데이터만 입력, 변경 가능

WITH READ ONLY : SQL만 가능한 뷰 생성

 

뷰 정보 조회

뷰 정보 조회

SELECT view_name, txt
FROM user_view
WHERE view_name = '뷰이름'
;

 

 

---

 

SEQUENCE

 

 

• 테이블 내의 유일한, 숫자를 자동으로 생성하는 자동 번호 발생 객체
• 보통 시퀀스를 이용하여 기본키 ( Primary Key) 를 지정

목적

•  unique값을 가지는 사원번호 등을 보다 빠르게 입력하기위해

 

활용

SQUENCE 문법

CREATE SEQUENCE seq_name
[ START WITH n ]
[ INCREMENT BY n ]
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE | NOCACHE ]
;

 

START WITH : 시작 값

INCREMENT BY : 증가 값

MAXVALUE : 최대 값

MINVALUE : 최소 값

CYCLE : 최대 값에 도달했을 경우 순환 여부

 (NOCYCLE은 증가완료 시에 에러를 유발)

CACHE : 메모리 상에서 SQUENCE의 값을 관리(CACHE)할지 여부를 정한다. 기본 값은 20, 원하는 숫자만큼 미리 만들어 CACHE로 상주시킨다. 

 (NOCACHE는 메모리 상에서 SQUENCE의 값을 관리하지 않는다. CACHING을 하는 것이 퍼포먼스 상으로 우위에 있지만, 연속된 번호를 얻으려면 NOCACHE가 바람직하다.)

 > CACHING이 AGING - OUT되거나; DB의 비정상적 종료로 채번이 불연속적으로 될 수 있다. 물론 기본 키 상에 번호가 중복될 경우는 없다.

 

예시

create sequence seq_empno
start with 1000
increment by 1
cache 20
;

 

이렇게 입력해 놓은 후, (시퀀스가 생성이 된 후)

 

select seq_empno.NEXTVAL
from dual
;

 

3번 실행하면, 결과값

1000

1001

1002

를 기대할 수 있다.

 

시퀀스 수정

 

SEQUENCE 수정

ALTER SEQUENCE seq_name
[ INCREMENT BY n ]
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE | NOCACHE ]
;

 

START WITH를 제외한 다른 설정들을 수정할 수 있다.

 

 

시퀀스 삭제

 

SEQUENCE 삭제

DROP SEQUENCE seq_empno
;

 

시퀀스 DATA DICTIONARY

 

SEQUENCE DIC.

SELECT sequence_ name, min_value, max_value, increment_by, cycle_flag, last_number
FROM user_sequences
;

 

LAST NUM 에서 사용자가 CACHE 설정을 해두었다면, CACHE에 의해 채번된 마지막 번호 값을 나타낸다.

 

 

---