악덕고용주의 개발 일기

대용량 데이터베이스 솔루션 조광원 선생님 동영상 강의를 구했다. 책으로만 보기엔 너무 힘들어서...

비록 좀 오래된 강의라 화질이 좋진 않지만 도움이 되겠지? 

  [ 비밀번호 관리 ]

  : 데이터베이스에서 항상 기밀로 유지해야 한다.

    -> 비밀번호 관리 정책이 있어야 한다.

    - 사용자 암호를 관리하기 위해서는 profile을 사용한다.

      - profile 생성 구문 형식 )

          create profile 프로파일명 limit

            옵션1,

            옵션2, .....

      - 사용자에게 profile을 적용하는 방법 )

          - 기존유저일 경우 : ALTER USER

          - 새로추가하는 유저일 경우 : CREATE USER

          명령을 이용해서 profile을 적용할 수 있다.

    - [  암호 관리 유형  ] 

      - 1)비밀번호 입력 횟수 제한하는 방법 : 제한횟수 이상의 로그인 시도시 사용자 계정을 lock 시킴.

      - 2)비밀번호의 유효기간을 설정하는 방법    

          : 유효기간이 지나면 암호를 재설정하여 사용하도록 함.

      - 3)비밀번호의 재사용 금지 방법

          : 암호 재설정시 기존에 사용한 암호를 다시 사용할 수 없도록 하는 방법.

      - 4)복잡한 암호 설정 방법을 사용

          : 0자 이상, 특수문자 포함 등을 포함시켜 패스워드를 생성하게 하는 패스워드 설정 정책

          -> 오라클에서 제공하는 함수를 사용해서 복잡한 암호를 사용하도록 설정할 수 있다.

          -> (password_verify_function 함수) 

*/

-- 1)비밀번호 입력 횟수를 제한하는 profile을 생성

create profile profile_test limit

  FAILED_LOGIN_ATTEMPTS 3 -- 3번 시도 실패하면 

  PASSWORD_LOCK_TIME 5; -- 5일간 패스워드에 lock을 검

-- 만든 profile을 HR(기존에 있는 사용자) 계정에 적용하기

alter user hr profile profile_test;

-- 3번 로그인 실패시 5일 lock이 걸리게 됨

-- lock을 풀어주는 방법

alter user hr account unlock;

-- 다시 lock을 걸기

alter user hr account lock;

-- 사용자 계정에 lock이 걸렸는지 확인해 보기(dba_users에서...)

select username, account_status AS lock유무, to_char(lock_date,'yy/mm/dd hh24:mi') AS 잠긴날짜,

       profile AS 적용된profile

from dba_users

where username = 'HR';

-- 2)profile에 유효기간 설정하기 

create profile profile_test2 limit

  FAILED_LOGIN_ATTEMPTS 3

  PASSWORD_LOCK_TIME 5

  password_life_time 90 -- 90일동안 패스워드가 유효할 수 있도록 설정함

  password_grace_time 5; -- 90일 지난 다음 유예기간을 설정할 수 있음

  -- 이 유예기간 동안은 로그인할 때마다 경고메시지가 뜨게됨

  -- 이때의 5일은 90일 다음날부터 카운트되는 것이 아닌, 사용자가 90일이 지나고

  -- 최초로 로그인을 시도한 날부터 카운트 되게 됨.

-- dba가 계정을 만료시키는 방법)

  --  alter user 사용자ID password expire;

-- 3)비밀번호 재사용을 금지시키는 방법)

create profile profile_test3 limit

  password_reuse_time 30 -- 설정했던 패스워드를 30일간은 사용할 수 없음

  password_reuse_max unlimit; 

create profile profile_test4 limit

  password_reuse_time unlimit

  password_reuse_max 3; -- 지금 사용하는 비밀번호를 3번 바껴야 다시 해당 비밀번호를 사용할 수 있게 됨

  -- password_reuse_time과 password_reuse_max는 서로 상호 베타여서 하나가 값이 있으면 하나는 unlimit으로 되어야 한다.!!

  [ DB 사용자 관리(생성,변경,삭제) 및 사용자 정보 알아보기 ]

  - 사용자 계정 : 논리적인 의미

  - SYS계정

      : 데이터베이스내의 모든 권한을 갖는 최상위 레벨의 사용자

  - SYSTEM 계정

      : SYS 사용자로부터 "DBA권한"을 받은 사용자

      . 새로운 사용자를 추가, 변경, 삭제할 수 있다.

      . 사용자별 공간할당, 패스워드관리, 세션관리 등을 할 수 있다.

      . 데이터베이스 오브젝트(테이블, 뷰, 트리거...)는 "사용자별로" 생성된다.

      . 데이터베이스 오브젝트를 생성한 사용자를 그 오브젝트의 소유자(owner)라 한다.

--[ 사용자 생성 ]

--  : dba권한을 가진 사용자만 가능하다.

-- 형식 ) create user 사용자ID identified by 패스워드

--        해당 사용자가 생성하는 객체가 저장될 테이블 스페이스도 지정할 수 있다.

--        create user 사용자ID identified by 패스워드 default tablespace 테이블스페이스명

--        temporary tablespace temp /*정렬을 위한 temporary tablespace도 생성 가능*/

--        quota 20M on appl_data quota 10M on system; /*tablespace 용량 할당*/

-- 테이블스페이스를 지정하지 않으면, 기본적으로 system tablespace를 사용하게 된다.

create user user1 identified by user1

  default tablespace test_1;

-- 사용자 정보 검색 : dba_users에서 검색

select username from dba_users;

select * from dba_users;

/*

  [ 사용자 변경 ]

  - 패스워드 변경 >

      alter user 사용자명 identified by 바꿀비밀번호;

  - 사용자의 테이블스페이스 변경>

      alter user 사용자명 default tablespace 바꿀테이블스페이스명;

  - 사용가능 용량 변경

      alter user 사용자명 quota 10M on 테이블스페이스명; 

alter user user1 identified by 1234;

alter user user1 quota 10 on test_1;

-- 전체 사용자에 대한 정보를 검색할 때

  -- 테이블스페이스 정보, 패스워드, 아이디를 포함해 사용자의 정보를 볼 수 있다.

select * from dba_users;

-- 현재 로그인한 사용자에 대한 정보만을 검색할 때

select * from user_users;

  [ 사용자 제거 ]

  형식 ) 

        drop user 사용자ID;

        -- 해당사용자가 가지고 있는 객체(뷰,트리거 등)도 전부 삭제하는 방법(cascade)

          -- 잘 사용하지 않는다.(신중하게 해야한다. 대부분 위 방법으로 삭제한다.)

        drop user 사용자ID cascade;

*/

create user user2 identified by user2;

select * from dba_users;

-- 사용자 제거

drop user user2;

select username from dba_users where username = 'user2';

-- 현재 자신의 계정에 대한 정보를 검색할 때

select * from user_users;

  [ 사용자 공간 사용 정보 알아보는 방법 ]

    - 사용자 공간 정보 알아보기 위한 딕셔너리 : user_ts_quotas, dba_ts_quotas

*/

select * from USER_TS_QUOTAS;

select * from SYS.DBA_TS_QUOTAS;

-- MAX_BYTES 항목이 -1인 값의 의미는 최대 용량 제한이 없음을 의미(unlimited)

  [ 사용자의 세션 정보를 알아보는 딕셔너리 ]

  -> v$session

  현재 접속중인 사용자들의 세션 정보를 알 수 있다.

select sid, serial#, username, program from v$session;

-- program : 어떤 프로그램을 통해 접속했는지

-- username : 사용자ID

-- HR에 접속한 것의 세션을 끊어버리기

-- 세션을 중지시키기 : alter system kill session 'SID,SERIALNUM';

alter system kill session '93,275';

select sid, serial#, username, program from v$session;

/*

  [ 인덱스(Index)의 개념/종류/주의사항/활용,관리 ]

  1. 인덱스(Index)란???

    : 어떤 데이터가 HDD(하드디스크)의 어디에 있는지 위치 정보를 가진 주소록과 같은 개념.

    -> (데이터 - 위치주소(ROWID)) 쌍으로 저장하고 관리됨

    - 목적)

      : 빠르게 쿼리 검색을 하오기 위함

    데이터 위치 정보(주소) : ROWID -> 총 10Byte

*/

-- 데이터 튜플의 rowid를 검색해 보기

select rowid, empno, ename from scott.emp where empno = 7521;

-- 결과 : 

/*

  ROWID             EMPNO   ENAME

AAAE+3AAEAAAAFfAAC   7521    WARD

*/

-- [ ROWID의 구조 ] : AAAE+3AAEAAAAFfAAC

/*

  -AAAE+3 : 데이터 오브젝트의 번호

  -AAE : 파일 번호

  -AAAAFf : 블럭 번호

  -AAC : ROW(튜플) 번호

*/

/*

  2. [ 인덱스의 생성 원리 ]

    : 전체 테이블을 스캔(Table Full Scan) -> PGA내의 Sort Area에서 정렬(Sort) 공간 부족시 Temporary tablespace 이용해 정렬

                                        -> 정렬한 데이터를 기반으로 HDD Block에 기록

    -> 인덱스는 데이터가 ""정렬"" 되어 들어간다!!

*/

/*

  3. [ 인덱스 구조와 작동 원리(B-TREE 인덱스 기준) ]

    : 테이블(Table)과 인덱스(Index)의 비교

    - 테이블은 컬럼이 여러개, 데이터가 "정렬되지 않고" 입력된 순서대로 들어간다.

                            vs

    - 인덱스(Index)는 컬럼이 "Key컬럼(사용자가 인덱스를 지정하라고 지정한 컬럼)"과 "ROWID컬럼" 두개로 이루어져 있다.

      (오름차순, 내림차순으로 정렬 가능)

*/

select * from emp where empno = 7902; -- 를 찾을 때

/*

  데이터 파일의 블록이 10만개가 있다고 할 때 sql문을 수행한다면,

  1) 서버 프로세스가 구문파싱 과정을 마친 후 DB Buffer 캐시에 empno가 7902인 정보가 있는지를 먼저 확인한다.

  2) 해당 정보가 캐시에 없다면 디스크 파일에서 7902정보를 가진 블럭을 찾아서 DB Buffer 캐시로 가져온 뒤 해당 정보를 사용자에게 보여줌

  이 경우에

    - Index가 없는 경우 -> 7902정보가 디스크 어떤 블럭에 있는지 모름으로 10만개 전부 DB Buffer 캐시로 복사한 뒤 Full Scan으로 찾게 됨.

    - Index가 있는 경우 -> where절의 조건으로 준 컬럼이 Index의 Key로 생성되어 있는지 확인한 뒤, 인덱스에 먼저 가서 7902정보가

                          어떤 ROWID를 가지고 있는지 확인한 뒤 해당 ROWID에 있는 블럭만 찾아가서 db Buffer 캐시에 복사하게 됨.

*/

/*

  4. [ 인덱스의 종류 ]

    1) B-TREE 인덱스(Index)

        : OLTP(Online Transaction Processing : 실시간 트랜잭션 처리)

          -> 실시간으로 데이터 입력과 수정이 일어나는 환경에 많이 사용함.

    2) BITMAP 인덱스(Index)

        : OLAP(Online Analytical Processing : 온라인 분석 처리)

          -> 대량의 데이터를 한꺼번에 입력한 뒤 주로 분석이나 통계 정보를 출력할 때 많이 사용함.

          -> 데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용함

        (1) [ B-Tree 인덱스 ]

          B : binary, balance 의 약자

              ROOT block(branch block에 대한 정보)

               |

            Branch Block(Left Block에 대한 정보)

               |

              Leaf Block(실제 데이터들의 주소)

        (1-1) [ B-Tree 인덱스의 종류 ]

          (A) Unique Index  

              : 인덱스 안에 있는 컬럼Key값에 중복되는 데이터가 없다.(성능이 좋음)

              -> 마치 Unique 제약조건과 유사하다. 따라서, Unique 제약조건 사용시에도 자동으로 UNIQUE INDEX가 생성된다.

또한, 기본키를 생성해도 오라클은 자동으로 UNIQUE INDEX를 생성하게 되는데 이때 UNIQUE나 기본키 객체명과 동일하게 생성된다. 

              - 생성 방법)

                SQL > create unique index 인덱스명

                      on 테이블명(key로지정할컬럼명 1 ASC|DESC, 컬럼명2...);

                ASC : 오름차순 정렬(기본값)

                DESC : 내림차순 정렬

                EX)

                -- dept테이블과 같은 테이블 하나 생성

                SQL > create table dept2 as select * from dept;

                -- dname을 key로하는 unique index를 생성

                SQL > create unique index idx_dept2_dname on dept2(dname); 

                -- 튜플 하나 추가

                SQL > insert into dept2 values(50,'개발부','서울');

                -- 위의 추가한 튜플과 dname값이 일치하는 다른 튜플을 하나 추가할라하면 unique인덱스이기 때문에

                -- 에러가 발생한다.

                SQL > insert into dept2 values(60,'개발부','인천');

                -- 이미 들어가 있는 dname이기 때문!!

            (B) Non Unique Index 

                : 중복되는 데이터가 들어가야 하는 경움(key로 지정한 필드의 중복된 값이 들어갈 수 있다.)

                - 생성 문법)

                SQL > create index 인덱스명 on 테이블명(컬럼명1 ASC|DESC, 컬럼명2....);

                EX)

                   -- 테스트용 테이블 새로 생성

                   SQL > create table dept3 as select * from dept;

                   -- dname을 Key로하는 Non-Unique index 생성

                   SQL > create index idx_dept3 on dept3(dname);

                   SQL > insert into dept3 values(50,'개발부','서울');

                   -- 중복되는 dname을 가진 튜플이 삽입이 가능하다.

                   SQL > insert into dept3 values(60,'개발부','인천');

            (C) FBI 인덱스( Function Based Index ) : 함수기반 인덱스

              : - 인덱스는 where절에 오는 조건 컬럼이나 조인에 쓰이는 컬럼으로 만들어야 한다.

                - 인덱스를 사용하려면 where절의 조건을 절대로 다른 형태로 가공해서 사용하면 안된다.

                예를들자면)

                  where절의 조건이 sal + 100 > 200 이러한 조건일 경우

                  단순히 index컬럼을 sal로만 지정하게 되면 인덱스가 적용되지 않고 검색쿼리가 수행되게 됨

                  -> 따라서, 인덱스도 테이블명(sal+100)의 형태로 "함수기반 인덱스"로 사용해야 함

                  EX)

                    SQL > create index idx_dept_fbi on emp(sal+100);

                    -- 이때, emp테이블에는 sal+100 컬럼은 없지만 인덱스를 만들 때 저 연산을 수행해서 인덱스를 만듬

                    (주의 사항)

                    - 임시적인 해결책은 될 수 있어도 근본적인 처방은 아니다.

                    - sal + 100을 인덱스로 생성했는데 쿼리 조건이 변경되면 인덱스를 다시 생성해야 한다.

                    - FBI는 기존의 인덱스를 활용할 수 없다.(단점)

              (D) Descending Index : 내림차순으로 인덱스를 생성한다.

                    : 큰 값을 많이 조회하는 SQL에 생성하는 것이 좋다.

                    ex) 최근 날짜부터 조회, 회사 매출 조회

                    SQL > create index idx_emp_sal on emp(sal desc);

                    (주의사항)

                      : 하나의 메뉴에 오름차순과 내림차순을 한번에 조회할 경우

                      -> 오름차순, 내림차순 두 개의 인덱스를 만들면 DML의 성능에 악영향을 미침

                      -> 이때는, 힌트를 사용한다.(아래나 위에서부터 읽도록 할 수 있다.)

              (E) 결합 인덱스(Composite Index) 

                  : 인덱스 생성시에 두개 이상의 컬럼을 합쳐서 인덱스를 생성하는 인덱스

                  -> 주로 where 절의 조건이 되는 컬럼이 2개 이상으로 and로 연결되는 경우 사용된다.

                  -> 잘못 생성하게 되면 성능에 영향을 미칠 수 있다.

                    (컬럼의 순서에 따라 효율에 차이가 있다.) -> 보통, 자주 사용하는 컬럼을 앞에 위치시키는 것이 좋다.

                    생성 방식)

                      SQL > create index 인덱스명 on 테이블명(컬럼명1, 컬럼명2);

                    EX) (성별, 이름) 두개의 컬럼으로 인덱스를 생성하는 경우

                      SQL > create index idx_emp_composite on emp(gender,dname);

                      -- 생성된 인덱스는 정렬되어 데이터를 저장시키게 되는데

                      -- 이때, 인덱스 생성시 기술한 컬럼순으로 정렬이 된다. 

                      -- 즉, gender(성별)을 기준으로 asc로 정렬한 뒤 같은 성별일 때

                      -- dname을 asc로 정렬해 인덱스를 생성하는 것

                      -- 생성된 인덱스가 어떤 모습으로 저장되어 있는지 검색해 보자.

                      EX) emp테이블의 empno와 deptno 두개의 컬럼을 조건으로 하는 인덱스 생성

                          SQL > create table emp3 as select * from emp;

                          SQL > create index idx_emp3_composite on emp3(deptno,empno);

                          -- 정렬된 상태로 저장되어있는 인덱스 모습을 보기

                          SQL > select * from emp3 where empno > 0 and deptno > '0';

                          -- 이때, 검색 쿼리가 deptno가 20이면서 empno가 7902인 사원정보를 검색한다면

                          SQL > select * from emp3 where deptno = 20 and empno = 7902;

                          -- 인덱스 생성시 dept 컬럼을 앞에 기술했기 때문에 dept=20을 먼저 찾기위해

                          -- 4번 검사를 하고 이때 20이 나옴으로 empno 7369검사(1번) 다음 deptno 검사 1번

                          -- 다시 empno 검사(1번) 식으로해서 최종 찾는 데이터까지

                          -- 총 9번의 검사를 해 찾게된다.

                          -- 이런식으로 검사가 이루어지기 때문에 주의할 사항이 생기는데

                          (deptno, empno)로 생성할지 (empno, deptno)로 생성할지에 따라 검사 효율이 다르게 나타날 수 있다.

                          이때는 평균적인 검사 효율을 어느정도 계산을 해 바서 인덱스를 생성하는 것이 중요하다!!(신중히 생성하자)

        (2-1) [ BITMAP 인덱스의 종류 ]

            : 데이터 값의 종류가 적고 동일한 데이터가 많을 경우에 많이 사용된다.

            Bitmap Index를 생성하려면 데이터의 변경량이 적어야 하고, 값의 종류도 적은 곳이 좋다.

            일반적으로 OLAP환경에서 많이 생성하게 되는대

            Bitmap Index는 어떤 데이터가 어디에 있다는 지도정보(MAP)를 Bit로 표기하게 된다.

            데이터가 존재하는 곳은 1로 표시하고, 데이터가 없는 곳은 0으로 표기한다.

            정보를 찾을 때 1인 값만 찾게 된다!

            SQL > create bitmap index 인덱스명 on 테이블명(컬럼);

            bitmap index를 생성하면 성별 컬럼 값의 종류대로 map이 생성된다.

            남자 : 1 0 1 0 0 -> 남자데이터가 1,3번 튜플에 있다.

            여자 : 0 1 0 1 1 -> 여자데이터가 2,4,5 튜플에 있다.

            이때 문제되는게

            bitmap index사용하고 있는 상태에서 만약 컬럼 값이 새로 하나 더 생긴다면?

            기존의 Bitmap Index를 전부 수정해야한다.

            -> B-Tree Index는 관련 블럭만 벽여되지만 Bitmap Index는 모든 맵을 다 수정해야 한다는 문제점!

            -> Bitmap Index는 블럭 단위로 lock을 설정해서 같은 블럭에 들어있는 다른 데이터도 수정작업이 안되는 경우가

               종종 발생한다.

*/

/*

  5. [ 인덱스 주의사항 ]

    - 인덱스를 사용하면 무조건 효율이 좋을까? NO

    -> [ 인덱스는 DML에 취약 ]

    근거)

      1) INSERT 작업의 경우

        : "index split"현상이 발생할 수 있다.

        - Index Split이란? : 인덱스의 Block들이 하나에서 두개로 나누어지는 현상

        -> 인덱스는 데이터가 순서대로 정렬되어 저장되게 되는데, 기존 블럭에 여유 공간이 없는 상황에서

           그 블럭에 새로운 데이터가 입력되어야 하는 경우

           오라클은 기존 블럭의 내용 중 일부를 새 블럭에다가 기록한 다음 기존 블럭에 빈 공간을 만들어서

           새로운 데이터를 추가하게 된다.

           --> 따라서, 성능면에서 매우 불리하다.

           a)Index Split은 새로운 블럭을 할당 받고 key를 옮기는 복잡한 작업을 수행

           b)Index Split이 이루어지는 동안 해당 블럭에 대해 키 값이 변경되면 안되므로 DML이 블로킹된다.

            enq:TX-index contention 대기 이벤트 발생(RAC-gc current split)

      2) DELETE 작업의 경우

        : 일반적인 테이블에서 데이터가 delete될 경우 해당 위치 데이터가 지워지고 그 공간을 사용 가능하다.

                        vs

          하지만, Index에서 데이터가 delete될 경우 -> "데이터는 지워지지 않고, 사용하지 않는다는 의미의 표시만 해두게 된다!"

            --> 즉, 테이블에 2만건의 데이터가 있었는데 1만건을 삭제해도 Index에는 데이터가 2만건이 존재한다는 말이된다.

            -> 인덱스를 사용해도 수행속도를 기대하기는 힘들다.

      3) UPDATE 작업의 경우

        : 인덱스에는 UPDATE란 작업이 존재하지 않기 때문에

          기존의 데이터를 DELETE한 다음 새로운 값의 데이터를 INSERT하는 두번의 과정으로 작업이 발생하는데

          따라서, 다른 DML작업보다 더 큰 부하를 주게 된다.

[ 최종적으로 인덱스 생성시 고려해야할 사항 ]

1 일반적으로 테이블 전체 로우 수의 15%이하의 데이터를 조회할 때 인덱스를 생성한다.

2 테이블 건수가 상당히 적다면 굳이 인덱스를 만들 필요가 없다. -> 테이블 건수가 적으면 인덱스를 경유하기보다 테이블 전체를 스캔하는 것이 더 빠르다.

3 인덱스 생성시 컬럼은 유일성 정도가 좋거나 범위가 넓은 값을 가진 컬럼을 지정하는 것이 좋다. (NULL값을 많이 갖는 컬럼은 피하는 것이 좋다.)

4 결합 인덱스 생성시에는 컬럼의 순서가 중요하다.

-> 보통, 자주 사용하는 컬럼을 앞에 지정한다.

5 테이블에 만들 수 있는 인덱스의 수는 제한이 없으나, 너무 많이 만들면 오히려 성능 부하가 발생한다.

why? -> 인덱스 생성을 해 놓으면 해당 테이블에 DML 작업(insert, delete, update)시 인덱스에도 수정작업이 동시에 발생하기 때문에 과도하게 많은 인덱스를 생성해 놓으면

오히려 성능 부하가 걸릴 수 있다.

-> 일반적으로, 테이블 당 최대 5개를 넘지 않는 것이 좋다.

6 데이터의 검색보다 수정, 삭제, 삽입 작업이 빈번한 테이블에는 인덱스를 생성하지 않는 것이 좋다.

-> 인덱스는 DML작업에는 성능이 좋지 않기 때문에 검색을 위주로 하는 테이블에 생성하는 것이 좋다.(위에서 언급한 성능 이슈들이 발생할 수 있다.)

7 인덱스 생성시 무엇보다 가장 중요한 점은 SQL 쿼리가 인덱스를 잘 활용할 수 있게끔 짜여져야 한다는 것이다.(쿼리를 잘 짜서 만들자!)       

*/

[ 적용한 트리거의 활성화/비활성화 설정 방법 ]

Alter Trigger 트리거명 ENABLE|DISABLE