Notice
Recent Posts
Recent Comments
Link
«   2025/01   »
일 월 화 수 목 금 토
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
Tags more
Archives
Today
Total
관리 메뉴

프로그래밍 삽질 중

SQL 객체종류(인덱스, 뷰, 시퀀스 등) 설명 및 예제 본문

과거 프로그래밍 자료들/SQL(Oracle)

SQL 객체종류(인덱스, 뷰, 시퀀스 등) 설명 및 예제

평부 2021. 3. 23. 23:13

※ 데이터 사전(딕셔너리)
- 데이터를 운영하는데 필요한 데이터
- 자동생성됨
- 사용자가 직접 접근, 작업 허용X
- 오라클 사용자 정보 ~ 기타 일반적인 DATABASE 정보


※ 데이터 딕셔너리 뷰
- ALL_XXXX : 한 특정 사용자가 조회가능한 모든 데이터 사전
- USER_XXXX : 한 특정 사용자에게 종속되어 있고, 그 사용자가 조회 가능한
데이터 사전 뷰들로 ALL_XXXX 데이터 사전의 모든 정보의 부분 집합
- DBA_XXX : DBA 권한을 가진 사용자 만이 조회할 수 있는 데이터베이스 관리를 위한 정보

 


※ 인덱스
- 데이터 검색 성능의 향상을 위해 테이블 열에 사용
- 위치 정보를 목록으로 만든 것
- 인덱스 검색 방법
1) Table Full Scan : 테이블 데이터를 처음 ~ 끝 검색하여 원하는 데이터 찾음
2) Index Scan : 인덱스를 통해 데이터를 찾음

 


※ 인덱스 고려사항
- 인덱스 = WHERE절에 자주 사용되는 속성 OR 조인에 자주 사용되는 속성이어야 함
- 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있음
- 속성이 가공되는 경우 사용X
- 속성의 모든 값이 다를 때 속성의 선택도가 낮을 때 유리

 


※ 뷰(VIEW)
- 하나 이상의 테이블을 조회하는 SELECT 문을 저장한 객체 = 가상의 테이블
- 원하는 결과를 만들어 내기 위한 연산 별칭
- 물리적으로 데이터를 따로 저장X
- 편리성(필요한 정보만 가공하여 뷰로 만듦), 재사용성(뷰로 미리 정의 가능),
보안성(특정 열 노출하지 않을 수 있음) 특징이 있음

※ 인라인 뷰
- CREATE문을 통해 객체로 만들어지는 뷰가 아니라 
SQL문에서 일회성으로 만들어지는 뷰

※ 시퀀스(SEQUENCE)
- 특정 규칙에 맞는 연속 숫자를 생성하는 객체
- EX) 새로운 게시판 번호, 상품 주문 번호

- NEXTVAL : 다음 번호를 생성
- CURRVAL : 마지막으로 생성한 번호, 시퀀스를 생성 후 바로 사용하면 오류 발생

 

 

[예제 1] - 데이터 사전

SYSTEM 계정으로 DBA_접두어 사용하기

1
SELECT * FROM DBA_TABLES;
 cs

 

[예제 2] - 데이터 사전

SYSTEM 계정으로 사용자 정보 살펴보기

1
2
3
SELECT *
FROM DBA_USERS
WHERE USERNAME = 'SCOTT';
 cs

 

[예제 3] - 인덱스

1
2
3
4
5
6
7
8
9
10
-- EMP 테이블의 SAL열에 인덱스 생성하기
CREATE INDEX IDX_BOOK_PRICE
ON BOOK(PRICE);
 
-- 생성된 인덱스 조회하기
SELECT *
FROM USER_IND_COLUMNS;
 
-- 인덱스 삭제하기
DROP INDEX IDX_BOOK_PRICE;
 cs

 

[예제 4] - 뷰(View)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
--VW_EMP20
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO = 20;
 
-- 뷰
SELECT * FROM VW_EMP20;
 
--뷰 생성하기
CREATE VIEW VW_EMP20
AS (SELECT EMPNO, ENAME, JOB, DEPTNO
    FROM EMP
    WHERE DEPTNO = 20);
 
SELECT * FROM VW_EMP20;
 
-- 인라인 뷰(일회성 뷰)
--1) EMP테이블에서 EMPNO을 기준으로 정렬하여 ROWNUM과 같이 출력
WITH E AS (SELECT * FROM EMP ORDER BY EMPNO DESC)
SELECT ROWNUM, E.*
FROM E;
 
--2) 급여가 높은 상위 10명 데이터 출력하기
WITH E AS (SELECT * FROM EMP ORDER BY SAL DESC)
SELECT ROWNUM, E.*
FROM E
WHERE ROWNUM<=10;
 cs

 

 

[예제 5] - 시퀀스

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- SALGRADE 테이블과 열 구성은 같고 데이터는 없는 테이블 생성
CREATE TABLE SAL_SEQUENCE
    AS SELECT * FROM SALGRADE
    WHERE 1<>1;
 
SELECT * FROM SAL_SEQUENCE;
 
-- 10씩 증가하는 SEQUENCE 생성
CREATE SEQUENCE SEQ_SAL_SEQUENCE
INCREMENT BY 10
START WITH 10
MAXVALUE 90
MINVALUE 0
NOCYCLE 
CACHE 2;
 
SELECT * FROM USER_SEQUENCES;
 
--시퀀스 사용
-- CURRAVAL : 마지막으로 생성한 번호, 시퀀스를 생성한 후 바로 사용 시 오류 발생
SELECT SEQ_SAL_SEQUENCE.CURRAVAL
FROM DUAL;
 
--NEXTVAL : 다음 번호를 생성
INSERT INTO SAL_SEQUENCE(GRADE, LOSAL, HISAL)
VALUES(SEQ_SAL_SEQUENCE.NEXTVAL, 1500, 2500)
 
SELECT * FROM SAL_SEQUENCE;
 
-- 시퀀스 변경
ALTER SEQUENCE SEQ_SAL_SEQUENCE
INCREMENT BY 3
MAXVALUE 99
CYCLE; -- 99초과 시 다시 0, 3, 6
cs
저작자표시 비영리 변경금지

'과거 프로그래밍 자료들 > SQL(Oracle)' 카테고리의 다른 글

SQL PL/SQL 설명 및 예제  (0) 2021.03.23
SQL 트랜잭션, 세션, 사용자 관리 설명 및 예제  (0) 2021.03.23
SQL 예제 문제(1)  (0) 2021.03.23
SQL 제약 조건 설명 및 예제  (0) 2021.03.22
SQL INSERT, UPDATE, DELETE & DDL 설명 및 예제  (0) 2021.03.22
'과거 프로그래밍 자료들/SQL(Oracle)' Related Articles more