KH 53일차 - INDEX (***) / 사용자 관리

-- ------------------------------------------------------

-- Index (인덱스) (***)

-- ------------------------------------------------------

--  가. 데이터베이스 성능에서 매우 중요한 역할 담당

--  나. 단순컬럼 또는 복합컬럼에 대해 인덱스 객체 생성가능 (**)

--  다. 인덱스 객체 안에는 크게 아래의 두가지 정보를 가지고 있다: (*)

--      (1) ROWID - 테이블의 각 행의 논리적인 주소값 저장

--      (2) 단순/복합 컬럼 - 인덱스를 생성한 컬럼 데이터

--  라. 실제 데이터(각 행)의 논리적 주소인 ROWID를 사용하면,

--      Table Full Scan 방식이 아닌, Index Scan 방식을 사용하여,

--      검색된 데이터를 테이블에서 랜덤하게 블록에서 접근가능.

--  마. ROWID(각 행의 논리적 주소값)를 저장하고 있는 오라클 객체가 인덱스

--  바. 인덱스 안의 ROWID 정보를 이용하여, 필요한 데이터를 검색하는 방식

--      이를, Index Scan 이라 한다. (***)

--  사. 인덱스는 ROWID + Column Datas 를, B(Balanced)-tree 구조로 저장

--      (B-tree.jpg 이미지 참고)

-- ------------------------------------------------------

--  * 사전 필요지식:

--    a. Oracle Block

--      - 가장 최소단위의 논리적인 구조, 입출력(I/O) 단위

--      - 기본크기: 8KB (설정으로 조정가능)

--      - ***실제 데이터(행들)를 block 단위로 관리***

--      - 각 block 마다, **고유한 아이디(id)** 부여 (= block id)

--      - 한 block 내에, 최대크기(8kb)내에서, **여러 행들 저장**

--      - 한 block 내의 각 행들에, **고유한 행번호** 부여 (= row id)

--    b. Table Full Scan 방식 (Index 객체가 없거나, 사용하지 않을 때)

--      - 검색할 모든 데이터(행들)를 접근하기 위해,

--        처음부터 끝까지 **모든 block들을 검색**

--    c. Index Scan 방식 (Index 객체를 사용하여, 실제 데이터에 접근)

--      - 검색된 데이터(행들)을 가장 빠르게 찾는 방법

--      - 블록 아이디(block id)와 행번호(row id) 이용해서,

--        검색된 데이터(행들)에 접근

--    d. 검색된 데이터(행들)를 제대로 접근(access)하기 위해서는,

--       아래의 **4가지 정보가 반드시 필요** :

--      (1) 파일 아이디 - 어느 *.dbf 파일에 저장되어 있는가?

--          - 실제 데이터가 저장되어 있는 물리적인 파일

--          - 각 DBF 파일마다, **파일 아이디** 부여 (식별위해)

--          - $ORACLE_HOME/oradata/<cdbname>/*.dbf

--          - $ORACLE_HOME/oradata/<cdbname>/<pdbname>/*.dbf

--          - 테이블들 저장

--      (2) 객체 아이디 - 어느 table 에 저장되어 있는가?

--          - 각 테이블 마다, **객체 아이디** 부여 (식별위해)

--          - 테이블 내에, 실제 데이터가 최종적으로 저장/관리됨

--      (3) 블록 아이디 - 어느 blocks 에 저장되어 있는가?

--          - 각 테이블 내의 실제 데이터(행들)는, 다시

--            해당 테이블의 최소 입/출력 단위인, block 내에 저장됨

--          - 각 block 마다, **block id** 부여 (식별위해)

--      (4) 행 번호 - 찾은 blocks 내에서, 몇번 째로 저장되어 있는가?

--          - 각 block 내에는, 최대8kb 내에서, 여러행 들 저장

--          - 각 block 내의 각 행마다, **row num** 부여 (식별위해)

--    e. ROWID (의사컬럼) - 각 행의 논리적인 주소를 가진 의사컬럼

--      - 위 d 의 4가지 정보(파일아이디/객체아이디/블록아이디/행번호)를 가지고 있음

--      - 인덱스(Index) 객체가 ROWID 를 하나의 요소로 가지고 있음

--      - 인덱스는 안에 있는 ROWID를 이용하여, 검색된 데이터를 빠르게 접근가능

-- ROWNUM은 SELECT절이 실행될 때 할당되게 되는데, 후에 ORDER BY로 정렬이 되면 순서가 이상하게 될 수도 있다.

-- Sub-range DQL은 ROWNUM을 사용하여 각 페이지의 행을 요청한다.(*)

---

-- ------------------------------------------------------

-- 1. Index 생성

-- ------------------------------------------------------

--  가. 빠른 데이터 검색을 위해 존재하는 오라클 객체 (**)

--  나. 명시적으로 생성하지 않아도, 자동 생성되기도 함

--      (PK(PRIMARY KEY)/UK(UNIQUE) 제약조건 생성시, Unique Index 자동생성) (**)

--  다. PK/UK 제약조건에 따른, 자동생성 Unique Index:

--      a. 데이터 무결성을 확인하기 위해, 수시로 데이터 검색 필요

--      b. 따라서, 빠른 검색이 요구됨

--  라. 명시적인 인덱스 생성이 우리가 할 일!!!

-- ------------------------------------------------------

-- 인덱스는 테이블의 작은 테이블이다.

-- 테이블에는 인덱스가 최소 1개 이상있다. (N개 이상 가능하다.)

-- 하지만 인덱스가 많다고 해서 좋은 것은 아니다.

-- where 절에 자주 작성되는 컬럼의 경우 인덱스를 붙이는 것이 좋다.

-- ------------------------------------------------------

-- Basic syntax:

--  CREATE [UNIQUE] INDEX 인덱스명

--  ON 테이블(컬럼1[, 컬럼2, ...]);     -- 테이블의 컬럼에 대해서 인덱스를 붙이는 것이다.

-- ------------------------------------------------------

--  (1) Unique Index

--      a. CREATE UNIQUE INDEX 문으로 생성한 인덱스

--      b. Index 내의 Key Columns에 중복값 허용하지 않음

--      c. 성능이 가장 좋은 인덱스(*)

--      d. (*주의*) 중복값이 허용되는 테이블 컬럼에는 절대로 사용불가!!(*)

--  (2) Non-unique Index

--      a. CREATE INDEX 문으로 생성한 인덱스

--      b. 중복값이 허용되는 테이블 컬럼에 대해,

--         일반적으로 생성하는 인덱스

-- ------------------------------------------------------

-- Index 없이, Table Full Scan 방식을 통한, 데이터 조회

SELECT

    *

FROM

    emp

WHERE

    ename = 'SMITH';

-- 이렇게 되면 emp 테이블 전체를 가지고 쿼리를 실행하게 된다.

-- 특정 쿼리의 실행계획(Execute Plan) 보기

-- (반드시, Oracle Developer에서 수행해야 한다.)

-- 위의 쿼리를 디벨로퍼에서 실행시키게 되면, 계획설명이라는 것을 볼 수 있는 탭을 생성할 수 있다.

-- 계획설명은 안쪽에서 바깥쪽으로 순서대로 실행하게 된다.

-- ------------------------------------------------------

-- Index 생성하여, Index Scan 방식을 통한 데이터 조회

DROP INDEX emp_ename_idx;

CREATE INDEX emp_ename_idx

ON emp(ename);

-- 사원이름에 대한 인덱스 형성 (이렇게 되면, 인덱스에 접속하여 실행하게 된다.)

-- ------------------------------------------------------

SELECT

    *

FROM

    emp

WHERE

    TRIM(ename) = 'SMITH';

-- 인덱스가 생성된 컬럼을 가공하게 되면 인덱스를 사용할 수 없다. ( 값이 가공되기 때문에 ) (**)

-- 인덱스는 가공이 되기 전의 값을 가지고 있지, 가공된 후의 값을 가지고 있지 않다.  (**)

---

-- ------------------------------------------------------

-- 2. USER_INDEXES 데이터 사전

-- ------------------------------------------------------

--  가. 생성된 인덱스의 정보 저장

-- ------------------------------------------------------

-- USER_INDEXES 데이터 사전조회 (emp, dept table의 인덱스 정보)

DESC user_indexes;

SELECT

    index_name,

    table_name

FROM

    user_indexes

WHERE

    table_name IN ('EMP','DEPT');

-- 데이터 사전을 이용할 때에는 테이블명을 반드시 대문자로 사용해야 한다. (***)

---

-- ------------------------------------------------------

-- 3. USER_IND_COLUMNS 데이터 사전

-- ------------------------------------------------------

--  가. 생성된 인덱스의 정보 저장

--  나. 인덱스가 생성된 테이블의 컬럼에 대한 정보까지 저장

-- ------------------------------------------------------

DESC user_ind_columns;

-- USER_IND_COLUMNS 데이터 사전조회 (emp, dept table의 인덱스 정보)

SELECT

    index_name,

    table_name,

    column_name,

    column_length,

    char_length,

    descend

FROM

    user_ind_columns

WHERE

    table_name IN ('EMP','DEPT');

-- 현재 생성되어 있는 인덱스에 대한 정보를 알려주고 있다.

 

---

-- ------------------------------------------------------

-- 4. Index 적용시점

-- ------------------------------------------------------

--  가. Index 를 사용하면, 검색속도 향상 기대가능

--  나. (**주의1**)index 를 사용한다고, 무조건 검색속도가 향상되는 것은 아니다!! (*)

--      - 한 테이블에 너무 많은 인덱스를 생성하면, 오히려 성능저하 발생!!

--  다. (**주의2**) 컬럼의 NULL 값은, 해당 컬럼에 인덱스 생성시, 인덱스에 저장이 안된다!! (*)

--      - 따라서, 생성된 Index의 크기가 감소가능.

--      - 그러므로, 이런 컬럼에 인덱스 생성 권고(필요시)

--  라. (**주의3**) 테이블에 DML 작업이 많은 경우, 해당 테이블의 컬럼에 대해서

--      관련된 모든 인덱스도 함께 변경되어야 함 -> 검색/DML 속도 모두 저하!! (*)

--      - DML 작업이 많다 > 테이블 변경이 자주발생 > 인덱스의 B트리구조가 변경

--        > 매우 큰 성능저하 발생

--  마. (**주의4**) Index 가 생성된 컬럼일지라도, 함수를 사용하여 컬럼을 가공하거나,

--      NOT과 같은 부정 논리연산자를 사용하면, Index 적용이 안됨. (*)

-- 인덱스는 꼭 필요한 컬럼에만 생성해야 한다.

-- ------------------------------------------------------

---

-- ------------------------------------------------------

-- 4-1. Index 적용시점 (언제 index를 생성/사용해야 하는가?) (***)

-- ------------------------------------------------------

--  a. 테이블 데이터가 많을 때. (*)

--  b. 특정 컬럼값의 범위가 넓을 경우. (도메인이 클 경우) (*)

--  c. WHERE절에 사용되는 컬럼 (*)

--  d. Join 조건에 사용되는 컬럼 (*)

--  e. DQL 쿼리결과의 크기가, 전체 데이터의 2 ~ 4 % 이내를

--     검색하는 경우 (대용량의 결과셋을 출력하는 쿼리라면, 인덱스는 효과는 떨어진다.)

--  f. NULL 값을 많이 포함하는 컬럼의 경우 (*)

-- ------------------------------------------------------

---

-- ------------------------------------------------------

-- 4-2. Index 비적용시점 (언제 index를 사용하지 말아야 하는가?) (***)

-- ------------------------------------------------------

--  a. 테이블 데이터가 적을 때.

--  b. 특정 컬럼값의 범위가 좁을 경우.

--  c. WHERE절에 사용되는 컬럼이 자주 사용되지 않는 경우.

--  d. 테이블에 DML 수행이 많은 경우.

--  e. DQL 쿼리결과의 크기가, 전체 데이터의 10 ~ 15 % 이상을

--     검색하는 경우

--  f. Index 가 생성된 컬럼이, 함수/NOT(부정) 연산자와 같이

--     사용되는 경우

-- ------------------------------------------------------

-- To select data NOT using index for emp.empno

-- 사유) 인덱스가 걸린 컬럼에 대해서, 함수로 가공처리

SELECT

    *

FROM

    emp

WHERE

    to_number(empno) = 7369;

-- 인덱스가 생성된 칼럼이 가공되었기에 인덱스로 점속하는 것이 아니라 테이블 전체에 접속한다.

-- 그리고 이 경우 숫자로 변환이 필요없는 칼럼에 대해 변환하였기에, 불필요한 과정이 추가된다.

-- ------------------------------------------------------

-- 부정 연산자 적용시 인덱스 사용불가 (***)

SELECT

    *

FROM

    emp

WHERE

    empno != 7369;

-- 부정연산자는 인덱스가 적용된 컬럼을 부정한다는 의미이기에, 인덱스를 사용할 수 없다. (*)

-- 인덱스가 사용되었는지, 사용되지 않았는지 확인하기 위해서는 디벨로퍼를 사용하는 것이 좋다. (*)

---

-- ------------------------------------------------------

-- 5. Index 삭제(DROP)

-- ------------------------------------------------------

-- Basic syntax:

 

--  DROP INDEX <삭제할 인덱스명>;

-- ------------------------------------------------------

DROP INDEX emp_ename_idx;

SELECT * FROM tab;

-- 잘 삭제되었는지 확인해 보기

---

-- ------------------------------------------------------

-- 6. 사용자 관리

-- ------------------------------------------------------

--  가. 다수의 사용자들이 하나의 데이터베이스를 공유하여 사용

--  나. 정보의 공유상,

--      a. 정보의 유출

--      b. 불법적인 접근

--      에 대한 접근통제(Access Control) 필요

--  다. Oracle은 접근통제(보안대책)을 위해, 아래 접근통제를 사용

--      a. 인증(Authentication)

--      b. 인가(Authorization)

--      각 사용자의 DB 접근/사용에 대해 보안 유지

--  라. 인증(Authentication)

--      사용자 계정의 생성/암호변경/DB자원사용을 통제를 위해,

--      DB 접근 및 사용을 통제하는 것을 의미

--  마. 인가(Authorization)

--      DB 객체에 대한 사용자의 접근/사용을 통제하는 포괄적 개념

-- ------------------------------------------------------

---

-- ------------------------------------------------------

-- 6-1. 사용자 계정 생성

-- ------------------------------------------------------

--  가. DB 관리자(SYSDBA role 가진)만 가능

--  나. 생성된 사용자는 아무런 권한(Privileges) 도 없음

--  다. 생성된 사용자는, 가장 기본적인 DB 접속조차 불가

-- ------------------------------------------------------

-- Basic syntax:

--  CREATE USER 사용자계정

--  IDENTIFIED BY 비밀번호;

-- 현 상태로는 아무런 권한을 주어지지 않았기에, 접속도 불가능하다.

-- ------------------------------------------------------

-- ------------------------------------------------------

-- 사용자 계정 생성시, 암호의 길이가 12 바이트 미만인 경우 오류발생

-- ------------------------------------------------------

-- *** 암호생성규칙 (Oracle12c 이상부터) ***

-- ------------------------------------------------------

--  a. 총 12문자 이상

--  b. 최소 1개 이상의 영문 대문자와 소문자 포함

--  c. 최소 1개 이상의 숫자 포함

--  d. 이전 암호 재사용 불가

--  e. 사용자계정명을 암호에 포함불가

-- 암호를 지정할때 특수문자는 절대 사용하지 않는것이 좋다!!(오류 발생)

-- ------------------------------------------------------

CREATE USER user01

IDENTIFIED BY user01;            -- XX : 암호생성규칙을 지키지 않았다.

-- IDENTIFIED BY Oracle123456;         -- OK : 암호생성규칙준수

---

-- ------------------------------------------------------

-- 6-2. 사용자 계정 암호변경

-- ------------------------------------------------------

--  가. 생성된 사용자 계정의 암호를 변경

-- ------------------------------------------------------

-- Basic syntax:

--  ALTER USER 사용자계정

--  IDENTIFIED BY 비밀번호;

-- ------------------------------------------------------

ALTER USER user01

IDENTIFIED BY oracle;           -- XX : 암호생성규칙미달

---

-- ------------------------------------------------------

-- 6-3. 시스템권한 할당전 접속시도

-- ------------------------------------------------------

--  가. 생성된 사용자 계정은 아무런 권한(Privileges)이 없음

--  나. 생성된 사용자 계정으로 DB에 접속하려면,

--      아래의 시스템 권한(System Prvilges) 할당받아야 함

--      CREATE SESSION 시스템 권한    

--  다. 시스템 권한은, DB관리자(SYSDBA role)만이 할당가능

-- 시스템 권한 CREATE SESSION 을 할당받기 전에 접속시도

-- ------------------------------------------------------

-- ORA-01045: user USER01 lacks CREATE SESSION privilege; logon denied

-- {VSC | Oracle SQL*Developer | Oracle SQL*PLUS} 통해 접속시도     -- XX : 접속실패

-- ------------------------------------------------------

---

-- ------------------------------------------------------

-- 6-4. 시스템권한 부여 (****)

-- ------------------------------------------------------

GRANT connect, resource, unlimited tablespace TO scott

IDENTIFIED BY Oracle12345678;

-- 권한부여와 계정생성을 한번에 다 해결해 준다.

ALTER USER scott DEFAULT TABLESPACE users;

ALTER USER scott TEMPORARY TABLESPACE temp;

ALTER USER scott IDENTIFIED BY Oracle123;

GRANT create view, create synonym, create sequence TO scott;

GRANT select, update, insert, delete ON emp TO hr;

ALTER USER scott ACCOUNT LOCK;      -- 지정된 계정 잠그기(lock)

ALTER USER scott ACCOUNT UNLOCK;      -- 지정된 잠긴계정 풀기(unlock)

-- 아래 문장으로 비밀번호도 변경하고, 계정을 잠그거나(lock)/풀거나(unlock) 가능

ALTER USER scott

   IDENTIFIED BY Oracle123

             ACCOUNT UNLOCK/LOCK;

-- 이러한 과정은 ADMIN 계정으로 실행시켜야 한다.

-- + 권한을 취소할 때에는 REVOKE ~ FROM을 사용하면 된다.