[Database] 18장 Sequence, Synonym, Index, Data Dictionary 객체

 

Sequence 객체

 

: 자동적으로 번호를 맺어주는 객체

특별히 설정되는 PK가 없거나 의미 있게 만들지 않아도 되는 경우와 자동으로 순서적인 번호가 필요한 경우 사용한다.

테이블과 독립적이며 여러 곳에서 사용이 가능하다.

ex) 채팅 등

 

 

- 사용 방법

-- 생성(create), 수정(alter)
(create, alter) sequence 시퀀스명
    increment by 증감값
    minvalue 최소값
    maxvalue 최대값
    cycle; -- 순환 가능 여부

-- 삭제
drop sequence 시퀀스명;


-- 번호 증가 후 그 다음 번호부터 데이터가 삽입된다.

-- 다음 번호 증가
-- 시퀀스명.nextval : 시퀀스 객체의 다음 값(Next Value) 리턴
select 시퀀스명.nextval from 테이블명;

-- 현재 번호 조회
-- 시퀀스명.currval : 시퀀스 객체의 현재 값(Current Value) 리턴
select 시퀀스명.currval from 테이블명;

 

 

---

 

• 시퀀스 사용 전

-- 상품 분류테이블에 'P103', 'USB 제품'을 등록하시오?
-- (단, lprod_id 컬럼의 값은 최대값을 구하여 1을 더한 서브쿼리를 작성하여 입력한다.)
select nvl(max(lprod_id), 0) from lprod;

insert into lprod(lprod_id, lprod_gu, lprod_nm)
    values ((select nvl(max(lprod_id), 0)+1 from lprod), 'P103', 'USB제품');

select * from lprod;

 

결과 화면1

 

 

시퀀스명.nextval : 시퀀스 객체의 다음 값(Next Value) 리턴

시퀀스명.currval : 시퀀스 객체의 현재 값(Current Value) 리턴

 

 

• 시퀀스 사용 후

-- lprod_seq 시퀀스 생성 : 초기값이 15이며 1씩 증가
create sequence lprod_seq
    increment by 1 start with 15;


-- 상품 분류테이블에  'P203' , '아동복' 을 등록하시오?
insert into lprod(lprod_id, lprod_gu, lprod_nm)
    values(lprod_seq.nextval, 'P203', '아동복');
    
select * from lprod;

 

결과 화면2

 

 

---

 

sequence가 사용되는 경우, 제한되는 경우

 

 

-- sequence 변경
-- 최대 50까지 2씩 증가
alter sequence lprod_seq
    increment by 2
    maxvalue 50
    cycle;
-- cycle을 추가 시 처음으로 돌아감

select lprod_seq.nextval from dual;

select lprod_seq.currval from dual;

 

 

-- sequence 제거 (*rollback 되지 않음)
drop sequence lprod_seq;

 

 

-- 다음 요건을 만족하는 시퀀스를 생성하시오? (P.221 참고)
-- 객체명 : cart_seq, 증감값 : 1, 최소값 : 10000, 최대값 : 99999, 순환가능
create sequence cart_seq
    increment by 1
    minvalue 10000
    maxvalue 99999
    cycle;

select cart_seq.nextval from dual;

 

 

 

Synonym 객체

= 동의어, 별칭

 

: 객체에 대한 다른 이름으로 대체한다.

긴 이름의 객체명을 쉬운 이름으로 대체할 때 사용한다.

 

 

객체를 생성할 때 insufficient privileges 오류가 뜰 시 권한을 줘야함.

아래의 링크를 참조하여 권한을 주면 된다.

 

https://forest-of-coding.tistory.com/6

[Database] 1.5장 Oracle user 접속 및 사용권한 부여

 

권한 부여 후 화면

 

 

- 사용 방법

-- 생성
create synonym 별칭
    for 테이블명;

-- 삭제
drop synonym 별칭;

 

 

---

 

-- synonym 객체 생성
-- insufficient privileges 오류가 뜰 시 권한을 줘야함.
create synonym mem
    for member;

 

 

-- 두 select 문의 결과는 동일하다.
select * from mem;

select * from member;

 

 

-- 삭제
drop synonym mem;
drop synonym mydual;

 

 

 

ex) 

-- mydual 생성
create synonym mydual for sys.dual;

 

 

select 'Hello World' from mydual;

 

결과 화면3

 

 

select sysdate from mydual;

 

결과 화면4

 

 

-- 삭제
drop synonym mydual;

 

 

 

Index 객체

 

: DB Server의 성능을 가장 많이 좌우하게 되는 요소 중 하나로, 특정 데이터를 빨리 찾기 위해 사용한다.

기본적으로 PK 키와 FK 키를 생성할 때 사용된다.

인덱스를 만드는 데 많은 저장 공간과 시간이 소요되기에 사용 시 주의해야 한다.

index는 수정할 수 없기에 수정을 원할 시 삭제하고 다시 생성해야 한다.

 

 

index 필요, 불필요 컬럼

 

 

 

 

- 사용 방법

-- index는 수정할 수 없기에 수정을 원할 시 삭제하고 다시 생성해야 한다.

-- 생성
create index 별칭
    on 테이블명(컬럼명);

-- 삭제
drop index 별칭;

-- 재생성 : 삭제 후 사용 불가 -> 생성 한 뒤 사용해야함.
alter index 별칭 rebuild;

 

 

---

 

-- index 객체
-- 회원 생일이 조건절에 자주 사용되어 index를 생성
create index idx_member_bir
    on member(mem_bir);

 

결과 화면5

 

 

select mem_id, mem_name, mem_job, mem_bir
    from member where to_char(mem_bir, 'YYYY') = '1998';

 

결과 화면6

 

 

-- 회원생일에서 년도만 분리하여 인덱스를 생성(Function-based Index)
create index idx_member_bir_year
    on member(to_char(mem_bir, 'YYYY'));

 

drop index idx_member_bir;

drop index idx_member_bir_year;

 

 

-- index 재생성 : 삭제 후 사용 불가 -> 생성 한 뒤 사용해야함.
alter index idx_member_bir rebuild;

 

 

• 추천 사용 방법

-- index 객체
-- 바로 하단의 방식보단 재구성의 방식으로 하는 것을 권유한다.
-- 아래의 코드 결과 값은 같다.
select cart_no, cart_prod, cart_qty from cart where substr(cart_no, 1, 8) = '20200405';

-- 재구성
select cart_no, cart_prod, cart_qty from cart where cart_no like '20200405%';

-- 또는
select cart_no, cart_prod, cart_qty from cart 
    where cart_no > '2020040500000'
    and cart_no < '2020040599999';

 

 

 

Data Dictionary

 

: DataBase를 위해 Meta 정보를 관리한다.

 

주요 정보

 

사용 방법

 

 

-- all_objects의 모든 컬럼 상세
desc all_objects;

 

결과 화면7

 

 

select table_name, comments from dictionary
    where table_name like 'ALL_%';

 

결과 화면8

 

 

select object_name, object_type, created
    from all_objects
    where owner = 'PC10'
    order by object_type asc;

 

결과 화면9

 

 

---

 

자주 사용되는 user뷰 : user 뷰 명칭

 

 

-- USER_TABLES의 컬럼 상세를 확인하고 각 테이블 전체 레코드 개수를 출력.
-- (테이블명, 레코드 수)
desc user_tables;

select table_name, rownum
    from user_tables;

 

결과 화면8

 

 

-- USER_CONSTRAINTS, USER_CONS_COLUMNS의 컬럼 상세를 확인하고 상품 테이블의 제약조건을 출력하시오?
-- (컬럼명, 제약명, 타입, 제약내용)
desc user_constraints;
desc user_cons_columns;

select * from all_constraints
    where table_name='PROD';

 

결과 화면9