[Oracle] SQL Class 10 - TCL, 시퀀스, view, 사용자 정의 view, Index

2023.11.07

 

## TCL

- 물리적으로 남겨두는 파일

- 트렌젝션 관리명령

- 데이터 흐름 제어문, 이벤트 관리명령

- 버퍼(메모리)에 저장: 명령이 저장되는 공간임

 

- autocommit OFF : 기본값.

 OFF 상태여야 SQL 프로그램 성능유지 유용함. but, SQL 명령이

 

새로운 DB를 등록해서 컬럼에 등록된 상황

 

 

[참고]

MS-SQL은 autocommit On 되어 있음

begin tran

         insert ~ ;

         update ~ ;

         delete ~ ;

    end tran

 

## 시퀀스(Sequence)

1) 시퀀스: 숫자를 규칙적으로 부여하는 객체(개발자는 무조건 사용)

• 숫자만 가능, 자동부여 목적          ex. 회원번호/환자번호/주문번호 등
• 사용: 데이터 입력만 사용!!

SQL> create sequence s_deptno

          increment by 3             // 증가치
          start with 101               // 초기치
          maxvalue 999;             // 최대치

          nocycle on (기본값)    // 입력안해도 됨

 

시퀀스가 생성되었습니다.

 

insert 실패해도 시퀀스 값은 증가된다.

currval 로 확인

 

## 뷰(View)

- Data Dictionary View: 데이터 사전뷰

- 사용자 정의뷰

- 오라클의 모든 정보

 

총 3 종류

1) all_객체타입s: 모든 세션이 접근가능(모든 세션 확인가능) --> 아무나 볼 수 있음

• 모든 세션(계정, 유저)이 조회할 수 있음
• 간단한 요약정보
• 쓸일이 별로 없다.

2) user_ 객체타입s : 현재 계정의 상세정보 확인가능            ex. scott..sky...aiden 등에 s를 붙이는게 일반적인 패턴임

• 현재 세션의 모든 정보 조회 가능

3) dba_객체타입s: 모든 세션의 모든 정보(계정에 권한 있어야 가능)

• 모든 세션의 모든 정보 조회 가능
• 데이터베이스 관리자만 접근 가능

4) v$ : 동적성능 뷰: 다이나믹 퍼포먼스 뷰. 현재 서버에 들어와 있는 user가 누구인지 확인.  --> dba만 접근가능

• v$ ~
• 동적 성능 뷰
• 통계정보를 가진 뷰 → dba 만 접근 가능

1) all_

 

2) user_의 모든정보 확인

SQL> desc user_users;

 

3) dba_: 모든 세션의 모든정보를 봄. 단, 권한이 없으면 접근이 안나옴

권한이 없으면 소유자명이 뜨고, 권한자체가 없다면, 객체가 존재하지 않습니다 라고 메시지가 뜸.

sys가 권한자임

4) v$

제약조건의 정보를 보게 함.

 

 

연습1)

모든 user정보를 출력하시오.

user_constraints : 제약조건 정보

user_cons_columns: 

 

 

## 사용자 정의 뷰(view)

- 보안목적: 제한된 엑세스를 구현(보여주고 싶은 정보만 접근 허용)

- 복잡한 쿼리를 단순하게 사용(성능과 무관)

- 컬럼 조작이나 그룹화 작업에 의한 뷰가 아닌 경우, DML명령 가능!(그러나 할 이유는 없음)

 

(참고자료)

: devheyrin TSTROY

https://velog.io/@devheyrin/Oracle-SQL-%EB%AA%85%EB%A0%B9%EC%9D%98-%EC%A2%85%EB%A5%98

[Oracle] SQL 명령의 종류

 

## Index (색인)

<---->  full table scan

         (하나의 테이블 저장 단위인 1 extent = 64K 이하는 권장)

 

- where절에 많이 사용되는 컬럼에 생성.

- PK, UQ 제약조건은 기본값으로 Index 생성.

- 데이터가 실제로 저장되어 있는 곳을 갖고 오려고 씀

- 행 단위로 저장

- 중복값이 적은 컬럼에 사용

- 정적데이터(변경 X) 컬럼에 사용

• DML명령이 발생할때마다 인덱스는 재구성된다.
• 인덱스를 사용하지 않는 검색 → 풀 테이블 스캔 (실무에서 절대 사용하면 안됨)
  • 풀 테이블 스캔이 더 좋은 경우
    • 1 extent (객체 저장단위, block의 집합, 64k(8 block) 이하는 권장)
    • 아주 작은 크기의 테이블인 경우 인덱스 굳이 사용할 필요 X
• 정적데이터(변경 X, 중복값이 적음) 컬럼에 생성

 

** Index 종류 **

 

(기본 Index)

- b*tree index(normal, 기본값): PK/UQ 중복성 낮고, 빈도율도 낮음.

• 기본값 - pk, uq
• 완전 이진트리 구조 → 검색 한번마다 범위가 절반씩 줄어든다.
• 중복값 적은 컬럼에 생성해야 함

- bitmap : 중복성 높고, 빈도  많음.

• 0 or 1
• 중복값이 많은 컬럼이 where절에 많이 사용되는 경우 생성하면 좋다 (ex. 남/녀)

- function-based: 컬럼에 수식(조작)을 포함하는 경우. 함수기반 index.

• ex) sapay * 1.1
• 컬럼에 수식이 포함된 경우 → 수식 자체를 인덱스로 만든다.

- reverse-key(역방향):  b*tree(normal,기본값)의 역방향  → 동적 데이터

• ← 방향으로 저장
• b*tree 의 역방향
• 동적 데이터 컬럼에 사용 (변경이 많이 일어나는 컬럼)
• 0001, 0002, 0100, 0101 (이렇게 정렬되어있는 인덱스가)
  1000, 2000, 0010, 1010 (반대방향으로 저장되어있음) → 변경 발생해도 재정렬 X

 

(고급 index)

- decencting(내림차순 방향): 최근 입력된 데이터(행,row) 위주로 검색하는 경우 사용.  

• 변경된 컬럼이 사용되었기 때문에 function-based 타입 중 하나로 취급된다.
• 최근 데이터에 엑세스하는 컬럼 (날짜)
• 날짜 컬럼에 내림차순 인덱스 만들어놓으면 insert때부터 정렬되어 들어간다

- bitmap-join: 조인조건

• 조인 조건에 생성

 

인덱스(Index)를 사용하려면...

• 생성된 순서대로 사용해야 한다.
• 생성된 컬럼 그대로 (조작하지 않고) 사용해야 한다.

생성된 인덱스(Index)가 사용되지 못하는 경우

• 부정연산자인 not이나  <> 이나 != 을 사용하면, index(인덱스) 사용못함

             like, is null, not(부정 연산자), is null 등

• 컬럼이 조작된 경우(인덱스가 생성된 컬럼 그대로를 사용하지 않은 경우)
• 생성된 인덱스 컬럼의 순서대로 사용하지 않은 경우