22/05/11 TIL_ Oracle_SQL

김석진·2022년 5월 11일
TILoraclesql
0

Oracle SQL

목록 보기
7/10

데이터사전(Data Dictinary)란?

  • 대부분 읽기 전용으로 제공되는 테이블 및 뷰의 집합이다.
    - 데이터 베이스 전반의 정보를 제공
  • 오라클 데이터베이스는 명령이 실행 될 떄 마다 데이터사전을 Access한다.
  • DB작업동안 Oracle은 데이터사전을 읽어 객체의 존재 여부와 사용자에게 적합한 Access 권한이 있는지 확인
  • 또한 Oracle은 데이터 사전을 계속 갱신하여 DATABASE 구조, 감사, 사용자 권한, 데이터등의 변경사항을 반영한다.

데이터 사전에 저장되는 내용

  • 오라클의 사용자 정보
  • 오라클 권한과 롤 정보
  • 데이터베이스 스키마 객체(TABLE,VIEW,INDEX,CLUSTER, SYNONYM, SEQUENCE,...) 정보
  • 무결성 제약조건에 관한 정보
  • 데이터베이스의 구조 정보
  • 오라클 데이터베이스의 함수와 프로시저 및 트리거에 대한 정보
  • 기타 일반적인 DATABASE 정보

데이터 사전의 분류

ALL_XXX

ALL_로 시작하는 데이터사전이다. 한 특정사용자가 조회가능한 모든 데이터 사전을 의미
자신이 조회하려는 객체의 주인이 아니더라도 그 객체에 접근 할 수 있는 권한을 가지고 있다면 ALL_XXX 뷰를 통하여 조회가 가능하다.

USER_XXX

USER_XXX 로시작하는 데이터사전이다. 한 특정 사용자에게 종속되어 있고, 그사용자가 조회 가능한 데이터 사전 뷰들로 ALL_XXX데이터사전의 모든 정보의 부분집합이다.

DBA_XXX

DBA권한을 가진 사용자 만이 조회할 수 있는 데이터 사전으로서 모든 오라클 데이터베이스 객체에 대한 정보를 볼 수있다.
SELECT ANY TABLE권한이 있는 사용자 또한 질의가 가능하며 다른 사용자가 질의 하려면 앞에 SYS라는 접두어를 붙여야함

V$_XXX

Dynamic Performance View라고도 하고 현재 Database의 상태에 관한 정보로 주로 DBA에게만 액세스가 허용
주로 DBA의 모니터링 작업용 정보를 제공, X$테이블을 베이스로 하는 뷰이다.

X$_XXXX

X뷰는V뷰는 V뷰가 보여주지 않는 정보를 보여준다.
X$ 테이블은 오라클의 메모리 정보를 볼 수 있는 SQL 인터페이스 뷰들로 Oracle 데이터베이스의 가장 숨겨진 영역중하나이다.

인덱스(Index)

인덱스는 테이블이나 클러스터에 쓰여지는 선택적인 객체이다.

  • 오라클 데이터베이스 테이블내 원하는 레코드를 빠르게 찾아갈수있도록 만들어진 데이터 구조
    - 자동 인덱스: 프라이머리키 또는 UNIQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스
    • 수동인덱스 : CREATE INDEX명령으로 실행해서 만드는 인덱스

인덱스를 생성하는것이 좋은컬럼

  1. WHERE절이나 join조건안에서 자주 사용되는 컬럼
  2. null값이 많이 포함되어 있는 컬럼
  3. WHERE절이나 join조건에서 자주 사용되는 두개 이상의 컬럼

다음과 같은 상황에서는 인덱스 생성이 불필요하다

  1. 테이블이 작을때
  2. 테이블이 자주 갱신될때

오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있다.
B-tree 인덱스는 컬럼안에 독특한 데이터가 많을 떄 가장 좋은 효과를 가진다.
이 알고리즘의 원리는
1. 주어진 값을 리스트의 중간점에 있는 값과 비교함. 만약 그값이 더크면 리스트의 아래쪽 반을 버린다 만약 그값이 더 작다면 위쪽 반을 버린다.
2. 하나의 값이 발견될때까지 또는 리스트가 끝날때 까지 그와 같은 작업을 다른 반쪽에도 반복한다.

비트맵 인덱스

비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한값이 있을 경우 사용하는 것이 좋다.
비트맵 인덱스는 B-tree인덱스가 사용되지 않을 경우에서 성능을 향상시킴
테이블이 매우 크거나 수정/변경이 잘일어나지 않는 경우에 사용할 수 있다.

SQL> CREATE BITMAP INDEX emp_deptno_index
		ON emp(deptno);

UNIQUE 인덱스

UNIQUE 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있다는 장점이 있다.
프라이머리키 와 UNIQUE제약 조건시 생성되는 인덱스는 UNIQUE 인덱스이다.

SQL> CREATE UNIQUE INDEX emp_ename_indx
		ON emp (ename);

NON-UNIQUE 인덱스

NON-UNIQUE인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수ㅇ ㅣㅆ다.

SQL> CREATE INDEX dept_dname_indx
	 ON	dept(dname);

결합인덱스

복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수있는 최대 컬럼값은 16개이다.

SQL> CREATE UNIQUE INDEX emp_empno_ename_indx
     ON emp(empno, ename);

인덱스의 삭제

인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않는다
인덱스를 삭제하려면 인덱스의 소유자가 OR DROP ANY INDEX권한을 가지고 있어야함
인덱스는 ALTER할 수없다.

SQL> DROP INDEX emp_empno_ename_idx;

인덱스 데이터 사전

인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색할 수있다.

SQL> SELECT index_name, indext_type
	 FROM USER_INDEXES
     WHERE table_name ='EMP';
     
     
INDEX_NAME			INDEX_TYPE
-------------	---------------
EMP_DEPTNO_INDX			BITMAP
EMP_PK_EMPNO            NORMAL

VIEW 테이블

뷰란?

  • 뷰는 하나의 가상 테이블이라 생각하면된다.
  • 뷰는 실제 데이터가 저장되는 것은 아님 뷰를 통해 데이터를 관리할 수 있다.
  • 뷰는 복잡한 Query를 통해 얻을수 있는 결과를 간단한 Query를 통해 얻을 수 있게 함
  • 한개의 뷰로 여러 테이블에 대한 데이터를 검색할 수 있다.
  • 특정 평가 기준에 따른 사용자 별로 다른 데이터를 액세스 할 수 있도록 함

뷰의 제한 조건

  • 테이블에 NOT NULL로 만든 컬럼들이 뷰에 다 포함 되있어야함
  • ROWID,ROWNUM,NEXTVAL,CURRVAL등과 같은 가상컬럼에 대한 참조를 포함하고 있는 뷰에는 얻너 데이터도 INSERT할 수없다.
  • WITH READ ONLY 옵션을 설정한 뷰도 데이터를 갱신할 수없다.
  • WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는데이터만 삽입,삭제,수정 할 수 있다.

뷰 문법

  • FORCE는 기본 테이블 유무에 관계없이 VIEW를 생성하는 옵션이다.
  • WITH CHECK OPTION: VIEW에 의해 엑세스 될 수 있는 행만이 입력되거나 변경될수 있음을 지정한다.
  • WITH READ ONLY: SELECT만 가능한 VIEW생성한다.
  • 함수를 사용한 컬럼은 반드시 ALIAS를 지정해야한다.

뷰 예제

SQL> CREATE OR REPLACE VIEW name_query
	 AS
     	SELECT a.ename,b.dname
        FROM emp a, dept b
        WHERE a.deptno=b.deptno
         AND b.deptno=20;
         
 ---뷰를 이용한 조회
 SQL> SELECT * FROM name_query;
 
 ENAME		DNAME
 --------	-------------
 SMITH		RESEARCH
 JONES		RESEARCH
 ...

WITH CHECK OPTION

뷰의 조건식을 만족하는 데이터만 INSERT또는 UPDATE가 가능하도록 하는 옵션

SQL> CREATE OR REPLACE VIEW check_option
	 AS 
     SELECT empno, ename, deptno
     FROM	emp
     WHERE deptno = 10
     WITH CHECK OPTION;
     
     
---부서 번호가 10인 사원만 INSERT,UPDATE할 수 있다.
SQL> INSERT INTO check_option(empno, ename, deptno)
		VALUES(10005, 'jain' , 30);

=============결과_====================
INSERT INTO check_option(empno, ename, deptno)

1행에 오류: 
ORA-01402: 뷰의 WITH CHECK OPTION의 조건에 위배 됩니다

WTIH READ ONLY

SELECT만 가능한 VIEW를 생성함

---아래의 뷰는 읽기만 가능함
SQL> CREATE OR REPLACE VIEW read_only
	  AS 
      	SELECT empno, ename, deptno
        FROM emp
        WHERE detpno = 10
        WITH READ ONLY;

뷰의 정보 조회

USER_VIEWS데이터 사전을 통해서 뷰에 대한 정보를 조회할 수 있따.

SQL> SELECT view_name, text
	 FROM USER_VIEWS;

시퀀스(Sequence)의 이해 및 활용

시퀀스란?

  • 유일(UNIQUE)한 값을 생성해 주는 오라클 객체이다.
  • 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성할 수 있다.
  • 보통 PRIMARY KEY 값을 생성하기 위해 사용함
  • 메모리에 Cache되었을때 시퀀스 값의 액세스 효율이 증가함
  • 시퀀스는 데이터와 독립적으로 저장되고 생성된다.

시퀀스 생성

---시퀀스 생성 Syntax
CREATE SEQUENCE sequence_name
	[START WITH n]
    [INCREMENT BY n]
    [MAXVALUE n | NOMAXVALUE]
    [MINVALUE n | NOMINVALUE]
    [CYCLE | NOCYCLE]
    [CACHE | NOCACHE]
  • START WITH: 시퀀스의 시작 값을 지정함. n을 1로 지정하면 1부터 순차적으로 시퀀스 번호가 증가한다.
  • INCREMENT BY: 시퀀스의 증가값을 지정한다. n을 2로하면 2씩증가 START WITH 를 1로 INCREMENT BY를 2로 설정하면 1,3,5,7,.. 이렇게 시퀀스번호가 증가한다.
  • MAXVALUE는 시퀀스 최대값을 설정할 수 잇다
  • MINVALUE는 시퀀스의 최솟값을 설정할 수 있다.
  • CYCLE|NOCYCLE은 최대값도달시 순환여부를 설정할 수 있다.
  • CACHE|NOCACHE: CACHE여부, 원하는 숫자만큼 미리 만들어 Shared Pool의 Libaray Cache에 상주시킨다.
profile
김석진
주니어 개발자 되고싶어요
이전 포스트

22/05/10 TIL 집계함수

다음 포스트

Oracle Advanced SQL

0개의 댓글