[DB_SQL활용] 객체 종류

예지성준·2024년 5월 14일
db

DB

목록 보기
13/19
post-thumbnail

객체 종류

데이터베이스를 위한 데이터를 저장한 데이터 사전

1. 데이터 사전이란?

DICTIONARY 테이블
동의어 DICT

1) 데이터베이스를 구상하고 운영하는 데 필요한 모든 정보를 저장하는 특수한 테이블

2) 데이터베이스가 생성되는 시점에 자동으로 만들어집니다.

2. 접두어

1) 🔖USER_XXXX - 현재 데이터베이스에 접속한 사용자가 소유한 객체 정보

2) 🔖ALL_XXXX - 사용 가능한 모든 객체 정보

3) DBA_XXXX - 데이터베이스 관리를 위한 정보(데이터베이스 관리 권한을 가진 SYSTEM, SYS 사용자만 열람 가능)

4) V$_XXXX - 데이터베이스 성능 관련 정보(X$_XXXX 테이블의 뷰)

3. 요약

1) 데이터 사전은 오라클 데이터베이스를 구성하고 운영하는 데이터를 저장하는 특수한 테이블로서 오라클 사용자가 직접 접근할 수 없습니다.

2) SELECT문으로 데이터를 명령할 수 있도록 데이터 사전 뷰를 제공

3) 대표적인 데이터 사전 뷰 중 현재 접속한 사용자가 소유하는 테이블 목록을 보기 위해서는 USER_TABLES를 사용

4) 사용자가 소유하는 테이블을 포함해 다른 사용자가 소유한 테이블 중 현재 사용자에게 사용 허가가 되어 있는 테이블을 보기 위해서는 ALL_TABLES를 사용

더 빠른 검색을 위한 인덱스

1. 인덱스란?

1) 색인이라는 뜻의 인덱스(index)

index: 목차

  • 빠르게 찾기 위한 물리적 주소
  • 검색이 많이 되는 항목
  • 정렬이 필요한 항목
  • 검색이 향상
  • 조인을 많이 사용하는 컬럼에서 사용

2) 책 내용을 찾는 것과 마찬가지로 오라클 데이터베이스에서 데이터 검색 성능 향상을 위해 테이블 열에 사용하는 객체를 뜻

3) 인덱스 사용 여부에 따라 데이터 검색 방식을 Table Full Scan, Index Scan으로 구분합니다.
- Table Full Scan : 처음부터 끝까지 검색하여 원하는 데이터를 찾는 방식
- Index Scan : 인덱스를 통해 데이터를 찾는 방식

4) SCOTT 계정이 소유한 인덱스 정보 알아보기(SCOTT 계정일 때)

5) SCOTT 계정이 소유한 인덱스 컬럼 정보 알아보기

6) 인덱스는 사용자가 직접 특정 테이블의 열에 지정할 수도 있지만 열이 기본키(primary key) 또는 고유키(unique key)일 경우에 자동으로 생성합니다.

인덱스 생성

  1. 사용법

CREATE INDEX 인덱스명 ON 테이블명(컬럼명[ASC|DESC], 컬럼명 ...);

  1. EMP 테이블의 SAL열에 인덱스를 생성하기

  2. 생성된 인덱스 살펴보기(USER_IND_COLUMNS 사용)
    USER_INDEXES: 인덱스에 대한 정보
    USER_IND_COLUMNS: 인덱스가 적용된 컬럼 정보

CREATE INDEX IDX_EMP_SAL
ON EMP(SAL);
--FULL TABLE SCAN - 전체 레코드 검색
--INDEX SCAN -인덱스를 통한 검색

SELECT * FROM USER_INDEXES;
SELECT * FROM USER_IND_COLUMNS;
SELECT * FROM USER_INDEXES WHERE TABLE NAME = 'EMP';
SELECT * FROM USER_IND_COLUMNS WHERE TABLE NAME = 'EMP';

인덱스 삭제

  1. 사용법

DROP INDEX 인덱스명;

DROP INDEX IDX_EMP_SAL;

인덱스명 확인: 사전 테이블(USER_INDEXES, USER_IND_COLUMNS)

참고)
1) 인덱스 생성이 항상 좋은 결과로 이어지지는 않습니다. 정확한 데이터 분석에 기반을 두지 않는 인덱스의 무분별한 생성은 오히려 성능을 떨어트리는 원인이 되기도 합니다.

2) 인덱스는 데이터 종류, 분포도, 조회하는 SQL의 구성, 데이터 조작 관련 SQL문의 작업 빈도, 검색 결과가 전체 데이터에서 차지하는 비중 등 많은 요소를 고려하여 생성합니다.

3) 선택도가 낮을 수록 인덱스 적용이 좋고 선택도가 높을 수록 인덱스는 불리하다

  • 선택도: 선택 될 가능성
  • 남/여 -> 50%: 인덱스가 적합X

테이블처럼 사용하는 뷰

  1. 뷰란?
  • 가상테이블(virtual table)로 부르는 뷰(view)하나 이상의 테이블을 조회하는 SELECT 문을 저장한 객체를 뜻
  1. 뷰의 사용 목적(편리성)

1) 편리성 : SELECT문의 복잡도를 완화하기 위해
2) 보안성 : 테이블의 특정 열을 노출하고 싶지 않을 경우

  1. 뷰 생성

CREATE VIEW 뷰 명칭 AS SELECT문;

1) 권한부여

SYSTEM 계정 ->
GRANT CREATE VIEW TO SCOTT;

CREATE[OR REPLACE] VIEW

2) 뷰 생성하기
3) 생성한 뷰 확인하기
4) 생성한 뷰 조회하기

5) 뷰 삭제
DROP VIEW VM_EMP;

  • 뷰는 실제 데이터가 아닌 데이터가 아닌 SELECT문만 저장하므로 뷰를 삭제해도 테이블이나 데이터가 삭제되는 것은 아닙니다.

인라인 뷰를 사용한 TOP-N SQL문

-> SAL 정렬대로 번호가 섞여있음
-> 레코드 순번도 재정렬시키기

  1. 인라인 뷰(서브쿼리 사용)

SELECT ROWNUM, E.* FROM(SELECT * FROM EMP ORDER BY SAL DESC) E;

--SAL이 많은 순서대로 정렬되었지만 ROWNUM이 최초의 정렬 순서대로 나와있고
-- 제대로 정렬되지않고있다.
SELECT ROWNUM, E.* FROM EMP E ORDER BY SAL DESC;

-- 급여가 많은 직원 3명 조회
-- 가상의 테이블 인라인뷰로 만들어서 정렬시키기
SELECT ROWNUM, E.* FROM (SELECT * FROM EMP ORDER BY SAL DESC) E; --순번 재구성

SELECT ROWNUM, E.* FROM (SELECT * FROM EMP ORDER BY SAL DESC) E
WHERE ROWNUM <=3; -- 순서번호 3번까지만 출력

--범위 검색 원하는 순서번호대만 출력 3~9
SELECT ROWNUM, E.* FROM (SELECT * FROM EMP ORDER BY SAL DESC) E
WHERE ROWNUM >=3 AND ROWNUM <10;
--이렇게 하면 안나옴 ROWNUM >=3 -> 이상은 조회가 안됨/ ROWNUM <10 이하는 된다

--ROWNUM 자체를 인라인뷰로 만들어줘서 하자
--인라인뷰 안에 인라인뷰가 있는 구조
-- 3번부터 9번까지 정렬된 형태 출력
SELECT * 
FROM (SELECT ROWNUM NUM, E1.* 
	FROM (SELECT * FROM EMP ORDER BY SAL DESC) E1) E
WHERE E.NUM >= 3 AND E.NUM <10;
--RONUM도 별칭주기
  1. 인라인 뷰(WITH절 사용)

규칙에 따라 순번을 생성하는 시퀀스

1. 시퀀스란?

1) 오라클 데이터베이스에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체

  • 회원번호, 게시글 번호, 주문번호

2. 시퀀스 생성

1) 사용법

  • 권한부여
    CREATE SEQUENCE 권한부여 필요
    GRANT 권한 TO 사용자/스키마;

  • 생성
    CREATE SEQUENCE 시퀀스이름
        INCREMENT BY 숫자 - 숫자씩 증가
        START WITH 숫자 - 숫자에서 부터 증가 시작
        MAXVALUE 숫자 - 최대 숫자 만큼 증가
        MINVALUE 숫자 - 최소 숫자 부터 시작
        CYCLE(최대 숫자 도달시 다음숫자는 MINVALUE 부터 시작) | NOCYCLE(기본값 - 최대 숫자에 도달하면 오류 발생)

    CACHE 숫자 - 기본값(CACHE 20 -> 20개의 숫자를 미리 생성해 놓는다.) | NOCACHE

  • 수정
    ALTER SEQUENCE 시퀀스이름; ...

  • 삭제
    DROP SEQUENCE 시퀀스이름; ...

2) DEPT 테이블을 사용하여 DEPT_SEQUENCE 테이블 생성하기

3) DEPT_SEQUENCE 테이블에 DEPTNO가 10씩 증가할 수 있는 시퀀스 생성

CREATE TABLE DEPT_SEQUENCE AS SELECT * FROM DEPT WHERE 1 != 1;
-- where조건이 false이면 데이터 없이 구조만 복사

SELECT * FROM DEPT_SEQUENCE;

CREATE SEQUENCE SEQ_DEPT_SEQUENCE
INCREMENT BY 10 -- 증감단위 10
START WITH 10 -- 증가 시작 번호
MAXVALUE 90 -- 최대 숫자 만큼 증가
MINVALUE 0; -- 최소 숫자 부터 시작

4) 시퀀스 생성을 확인
USER_SEQUENCES 사전테이블

SELECT * FROM USER_SEQUENCES;

3. 시퀀스 사용

1) [시퀀스 이름.CURRVAL]과 [시퀀스 이름.NEXTVAL]을 사용

2) CURRVAL은 시퀀스에서 마지막으로 생성한 번호를 반환

  • 현재 값이 뭔지 알 수 있다.
    • NEXTVAL이 증가한다면 현재값도 바뀐다.
SELECT SEQ_DEPT_SEQUENCE.CURRVAL FROM DUAL;

3) NEXTVAL는 다음 번호를 생성

  • 시퀀스 객체.NEXTVAL: 숫자 증가 후 값 반환
  • 조회만 해도 NEXTVAL을 할때마다 숫자가 자동 증가한다.
INSERT INTO DEPT_SEQUENCE
	VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE','SEOUL');
	
SELECT * FROM DEPT_SEQUENCE;

INSERT INTO DEPT_SEQUENCE
	VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL,'WEB','INCHEON');

SELECT * FROM DEPT_SEQUENCE;

최대값에 도달하면 INSERT안되고 오류 발생한다.

  • 기본값이 NOCYCLE이기 때문

4. 시퀀스 수정

5. 시퀀스 삭제

DROP SEQUENCE SEQ_DEPT_SEQUENCE;

🎀 다시 생성해보자

CREATE SEQUENCE SEQ_DEPT_SEQUENCE;
TRUNCATE TABLE DEPT_SEQUENCE; --기존테이블 비우기
INSERT INTO DEPT_SEQUENCE
	VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL, 'WEB','INCHEON');	
SELECT * FROM DEPT_SEQUENCE;


SELECT * FROM USER_SEQUENCES;

공식 별칭을 지정하는 동의어

1. 동의어란?

1) 테이블-뷰-시퀀스 등 객체 이름 대신 사용할 수 있는 다른 이름을 부여하는 객체

2) 테이블 이름이 너무 길어 사용이 불편할 때 좀 더 간단하고 짧은 이름을 하나 더 만들어 주기 위해 사용함

사전 테이블 DICTIONARY의 동의어 DICT

2. 사용법

sql*plus에서 사용해보자

3. 권한 부여하기(SQL*PLUS)

CREATE SYNONYM: 현재 사용자에서만 동의어 사용 가능
CREATE PUBLIC SYNONYM: 모든 사용자에게 동의어 공유

CREATE CREATE PUBLIC SYNONYM, CREATE SYNONTM TO SCOTT;

4. 동의어 생성

CREATE SYNONYM 동의어 명칭 FOR 대상 테이블 또는 뷰;

조회 가능!

5. 동의어 삭제

CREATE SYNONYM 동의어 명칭;

profile
예지성준
꽁꽁 얼어붙은 한강 위로 😺
이전 포스트

[DB_SQL활용]데이터 정의어

다음 포스트

[SQL응용]사용자권한, 롤 관리

0개의 댓글