220718 TIL

Yeoni·2022년 7월 18일
TIL
0

국비교육TIL

목록 보기
34/41

국비교육 34일차 Oracle : 컬럼 추가/삭제/데이터타입 변경/default 값, 주석달기, 테이블 휴지통, INDEX, 실행계획, 자동추적, 데이터사전

1. 컬럼 추가, 삭제, 데이터타입 변경

1) 어떤 테이블에 컬럼 추가하기

alter table 테이블명 add 추가할컬럼명 데이터타입;

2) 어떤 테이블에 컬럼 삭제하기

alter table 테이블명 drop column 삭제할컬럼명;

3) not null로 테이블에 새로운 컬럼 추가하기

(1) 해당 테이블에 데이터가 전혀 없는 경우

alter table 테이블명 add 컬럼명 컬럼타입 not null;

(2) 해당 테이블에 데이터가 존재하는 경우

alter table 테이블명 add 추가할컬럼명 데이터타입 DEFAULT 기본값 NOT NULL;

  • 데이터가 있는데 not null인 컬럼을 추가하면 에러가 나기 때문에 default 값을 주어야 한다.

4) 테이블에 존재하는 데이터 타입 변경

(1) 해당 컬럼에 데이터가 모두 null인 경우

alter table 테이블명 modify 컬럼명 변경할새로운타입명;

(2) 해당 컬럼에 데이터가 모두 null이 아닌 값이 들어가 있는 경우

  • 해당 컬럼의 데이터가 모두 null 일 때만 새로운 타입으로 변경이 가능하다.

(3) 데이터 타입의 크기 변경

  • 해당 컬럼에 데이터가 null이 아닌 값이 들어가 있는 경우에는, 데이터타입의 크기의 변경은 가능한데, 축소는 불가하지만 증가는 가능하다.
    alter table 테이블명 modify 컬럼명 타입명(변경할타입크기);

5) 컬럼의 이름 변경

alter table 테이블명 rename column 현재컬럼명 to 새로운컬럼명;

6) 테이블 이름 변경

rename 현재테이블명 to 새로운테이블명

7) 컬럼의 default 값 조회하기

select column_name, data_type, data_default 
from user_tab_columns
where table_name = '테이블명';

8) 컬럼의 default 값 변경하기

alter table 테이블명 modify 컬럼명 default 테이터타입;

9) 컬럼의 default 값 삭제하기

alter table 테이블명 modify 컬럼명 default null;

2. 주석달기

1) 테이블명에 주석문

  • 테이블명에 주석문 조회하기
select *
from user_tab_comments;
  • 테이블명에 주석문 달기
comment on table TBL_JIKWON
is '우리회사 사원들의 정보가 들어있는 테이블';

2) 컬럼명에 주석문

  • 컬럼명에 달린 주석문 조회하기
select * 
from user_col_comments
where table_name = '테이블명';
  • 컬럼명에 주석문 달기
comment on column 테이블명.컬럼명 is '주석문';

3. 테이블 삭제시 휴지통에 버리기

  • 테이블을 삭제할 때 휴지통에 버리면 drop 되어진 테이블을 복구할 수 있다. 
drop table 테이블명; --> 테이블을 영구히 삭제하는 것이 아니라 휴지통에 버리는 것이다. 
-- 테이블명 이(가) 삭제되었습니다.

select * from tab;
-- 결과물에서 TNAME 컬럼에 BIN$로 시작하는 것은 휴지통에 버려진 테이블이다.

  • 휴지통에서 테이블 찾기
    select * from "BIN$0rprtM5JTgCfFbwfb+zelQ==$0";
    - 쌍따옴표 필수

  • 휴지통 조회하기
    select * from user_recyclebin;

  • 휴지통에 있던 테이블 복원하기
    flashback table 테이블명 to before drop;

  • 휴지통의 테이블 영구 삭제
    purge table 테이블명;

  • 휴지통의 모든 테이블 영구 삭제
    purge recyclebin;

  • 테이블 영구 삭제
    - drop purge 되어진 테이블은 복원이 불가하다.
    drop table 테이블명 purge;

4. INDEX

1) 정의

index(==색인)는 예를 들어 설명하면 아주 두꺼운 책 뒤에 나오는 "찾아보기" 와 같은 기능을 하는 것이다.
"찾아보기" 의 특징은 정렬되어 있는 것인데 index(==색인) 에 저장된 데이터도 정렬되어 저장되어 있다는 것이 특징이다.
index(==색인)를 생성해서 사용하는 이유는 where 절이 있는 select 명령문의 속도를 향상 시키기 위함이다.

(1) index 색인과 컬럼

  • index(==색인)은 where 절에서 자주 사용되어진 컬럼에 만들어야 한다.

(2) 선택도(selectivity)

  • 선택도(selectivity)가 높다라는 것은 고유한 데이터일수록 선택도(selectivity)가 높아진다.
  • 예를 들자면 주민등록번호와 성별 중에서는 주민등록번호가 선택도가 높다.

(3) 카디널리티(cardinality)

  • 카디널리티(cardinality)의 사전적인 뜻은 집합원의 갯수를 뜻하는 것으로서, 카디널리티(cardinality)가 높다라는 것은 중복도가 낮아 고유한 데이터라는 뜻이고, 고유한 데이터 일수록 카디널리티(cardinality)가 상대적으로 높다. 카디널리티(cardinality)가 낮다라는 것은 중복도가 높아 중복된 데이터가 많을수록 카디널리티(cardinality)가 상대적으로 낮다 라는 것이다.
  • 주민등록번호와 성명을 비교해 보면 주민등록번호의 경우에는 중복되는 값이 없으므로 카디널리티가 더 높다.

2) unique 한 index 생성하기

  • 어떤 컬럼에 unique 한 index 를 생성하면 그 컬럼에 들어오는 값은 중복된 값은 들어올 수 없으며 오로지 고유한 값만 들어오게 된다. unique 한 index 가 뒤에 나오는 non-unique 한 index 보다 검색속도가 조금 더 빠르다.

3) unique 한 index 문법

create unique index 인덱스명
on 해당테이블명(컬럼명 asc|desc);
  • 유니크한 index 를 만들면 해당 컬럼에 데이터는 고유한 값만 들어온다.

4) 테이블에 생성되어진 index 조회하기

  • user_indexes와 user_ind_columns 테이블을 조인해서 필요한 인덱스 조회하기
select A.index_name, A.uniqueness, B.column_name, B.descend
from user_indexes A JOIN user_ind_columns B
ON A.index_name = B.index_name
where A.table_name = 'TBL_STUDENT_1';

5) non-unique 한 index 생성하기

  • 어떤 컬럼에 non-unique 한 index 생성하면 그 컬럼에 들어오는 값은 중복된 값이 들어올 수 있다는 것이다. non-unique 한 index 는 unique 한 index 보다 검색속도가 다소 늦은 편이다.

6) non-unique 한 index 문법

create index 인덱스명
on 해당테이블명(컬럼명 asc|desc);

5. Plan(실행계획)

  • SQL PLAN(실행계획)이란?
    select 문이 실행될 때 인덱스를 사용하여 데이터를 얻어오는지 인덱스를 사용하지 않고 Table Full Scan 하여 얻어오는지 알아볼 때, 사용하는 것이 SQL Plan(실행계획)이다.

  • Plan(실행계획) 과 Trace(자동추적) 의 차이
    Plan(실행계획) 은 SQL을 실행하기 전에 Oracle Optimizer(옵티마이저, 최적화기)가 SQL을 어떻게 실행할지를 미리 알려주는 것이고,
    Trace(자동추적) 는 SQL을 실행해보고, Oracle Optimizer(옵티마이저, 최적화기)가 SQL을 어떻게 실행했는지 그 결과를 알려주는 것이다.

    그러므로, 정확도로 말하자면, Trace(자동추적)가 Plan(실행계획) 보다 훨씬 정확한 것이다.
    Plan(실행계획) 은 말그대로 계획이라서 Oracle Optimizer가 계획은 그렇게 세우긴 했으나
    실제 실행할때는 여러가지 이유로 다르게 실행할 수도 있기 때문이다.
    그래서 Trace(자동추적)가 정확하기는 하나 Trace(자동추적)는 한번 실행해봐야 하는것이라서
    시간이 오래 걸리는 SQL인 경우에는 한참 기다려야 하는 단점이 있기는 하다.

  • 실행계획 사용하는 방법
    SQL*Developer 에서는 "SQL편집창(SQL 워크시트)"에 Plan(실행계획) 과 Trace(자동추적) 메뉴가 상단에 있다.
    실행해야할 SQL문을 블럭으로 잡은 후에
    "SQL 워크시트" 의 상단 아이콘들중에 3번째 아이콘( 계획 설명... (F10) )을 클릭하면 현재 SQL의 Plan(실행계획)을 아래에 보여준다.
    COST(비용)의 값이 적을 수록 속도가 빠른 것이다.

6. 자동추적

  • 자동추적 사용하는 방법
    실행해야할 SQL문을 블럭으로 잡은 후에 "SQL 워크시트" 의 상단 아이콘들중에 4번째 아이콘( 자동 추적... (F6) )을 클릭하면 현재 SQL의 Trace(자동추적)을 아래에 보여준다.

  • 자동추적

    Trace(자동추적)을 하면 Plan(실행계획) 도 나오고, 동시에 아래쪽에 통계정보도 같이 나온다.

    오른쪽에 Plan(실행계획)에서는 보이지 않던 LAST_CR_BUFFER_GETS 와 LAST_ELAPSED_TIME 컬럼이 나온다.
    LAST_CR_BUFFER_GETS 는 SQL을 실행하면서 각 단계에서 읽어온 블록(Block) 갯수를 말하는 것이고,
    LAST_ELAPSED_TIME 은 경과시간 정보이다.
    즉, 이 정보를 통해서 어느 구간에서 시간이 많이 걸렸는지를 확인할 수 있으므로, 이 부분의 값이 적게 나오도록 SQL 튜닝을 하게 된다.
    메모리에서 읽어오는 LAST_CR_BUFFER_GET는 작아야 좋은 것이다.

7. INDEX와 DML

  • DML(insert, update, delete)이 빈번하게 발생하는 테이블에 index가 생성되어 있으면 DML(insert, update, delete) 작업으로 인해 Index Split(인덱스 쪼개짐) 이 발생하므로 index 가 많다고 해서 결코 좋은 것이 아니기에 테이블당 index 의 개수는 최소한의 개수로 만드는 것이 좋다.

(1) Index(인덱스)의 상태 확인하기

analyze index 인덱스명 validate structure;-- Index 인덱스명 이(가) 분석되었습니다.

select (del_lf_rows_len / lf_rows_len) * 100 "인덱스상태(Balance)"
from index_stats
where name = '인덱스명';

--> 인덱스상태(Balance) 0 : 0에 가까울 수록 인덱스 상태가 좋은 것
-- 꼭 분석을 마친 후에 select문으로 인덱스 상태를 확인하여야 한다.

2) INDEX Rebuild(인덱스 재건축)

  • DML문으로 인하여 INDEX가 깨지게 되면, 이미 깨져버린 상태에서 실행계획은 믿을 수 없기 때문에, 재건축을 해주어야 한다.
    alter index 인덱스명 rebuild;

3) INDEX 삭제하기

drop index 삭제할인덱스명;

8. 복합인덱스(Composite index) 생성하기

  • 2개 이상의 컬럼으로 묶어진 인덱스를 말하는 것으로서 where 절에 2개의 컬럼이 사용될 경우 각각 1개 컬럼마다 각각의 인덱스를 만들어서 사용하는 것보다 2개의 컬럼을 묶어서 하나의 인덱스로 만들어 사용하는 것이 속도가 좀 더 빠르다.
  • 복합인덱스(composite index) 생성시 중요한 것은 선행컬럼을 정하는 것이다. 선행컬럼은 맨처음에 나오는 것으로 아래에서는 name 이 선행컬럼이 된다. 복합인덱스(composite index)로 사용되는 컬럼중 선행컬럼으로 선정되는 기준은 where 절에 가장 많이 사용되는 것이며 선택도(selectivity)가 높은 컬럼이 선행컬럼으로 선정되어야 한다.
  • 복합인덱스 생성하기
create index 인덱스명
on 테이블명(name, address);   -- name 컬럼이 선행컬럼이 된다.

9. 함수기반 인덱스(function based index)

  • 함수기반 인덱스는 인덱스를 통하지 않고 table full scan하여 조회해 온다.
select *
from tbl_student_1
where substr(name, 2, 2) = '길동';
  • 자주 쓰는 경우에는 아예 함수기반 인덱스를 만들어서 사용하는 것이 효율적이다. 
create index idx_func_tbl_student_1_name
on tbl_student_1( substr(name, 2, 2) )
  • 맨앞에 % 또는 _ 가 나오면 인덱스 IDX_TBL_STUDENT_1_NAME 을 사용하지 않고 Table Full Scan 하여 조회해온다.

10. 제약과 인덱스

  • 어떤 테이블의 어떤 컬럼에 Primary Key 제약 또는 Unique 제약을 주면 자동적으로 그 컬럼에는 unique 한 index가 생성되어진다. 인덱스명은 제약조건명이 된다.
  • Primary Key 제약 또는 Unique 제약으로 생성되어진 index 의 제거는 drop index index명; 이 아니라 alter table 테이블명 drop constraint 제약조건명; 이다. 제약조건을 삭제하면 자동적으로 index 도 삭제가 된다.

11. 데이터 사전(Data Dictionary)

select * 
from dictionary;
-- 또는
select * 
from dict;

--  USER_CONS_COLUMNS
--  ALL_CONS_COLUMNS
  • DBA_로 시작하는 것
    ==> 관리자만 조회가능한 것으로 모든오라클사용자정보, 모든테이블, 모든인덱스, 모든데이터베이스링크 등등등 의 정보가 다 들어있는 것.
  • USER_로 시작하는 것
    ==> 오라클서버에 접속한 사용자 소유의 자신의오라클사용자정보, 자신이만든테이블, 자신이만든인덱스, 자신이만든데이터베이스링크 등 의 정보가 다 들어있는 것.
  • 내가 만든 것만!
  • ALL_로 시작하는 것
    ==> 오라클서버에 접속한 사용자 소유의 즉, 자신의오라클사용자정보, 자신이만든테이블, 자신이만든인덱스, 자신이만든데이터베이스링크 등의 정보가 다 들어있는 것
    과(와)
    자신의 것은 아니지만 조회가 가능한 다른사용자의오라클사용자정보, 다른사용자소유의테이블, 다른사용자소유의인덱스, 다른사용자소유의데이터베이스링크 등등등 의 정보가 다 들어있는 것.
  • 내가 만든 것과 내가 접속과 조회가 가능한 것

(1) 찾는 정보 조회하기

  • 키워드에 찾는 키워드(column, index 등)을 넣으면 원하는 테이블을 찾을 수 있다. 
select *
from dict
where table_name like 'USER_%' and lower(comments) like '%키워드%';
profile
Yeoni
이런 저런 기록들
이전 포스트

220715 TIL

다음 포스트

220719 TIL

0개의 댓글