데이터와 사람들 DBA 교육 3일차 정리( 5장. 사용자 관리, 6장. 오브젝트 관리, INDEX, 파티션 )

5장. 사용자 관리 ( 203p )

 

 

userid 는 스키마라고 생각하면 됨 이론상 다르지만 오라클에선 같은 느낌으로 씀

 

권한

시스템, 오브젝트, role

 

권한에 대한 조회 필수 3가지

dba_sys_privs, dba_tab_privs, dba_role_privs

 

프로파일

password(보안 - 이거만 좀 알아두면 됨 ), cpu(자원 - 잘안씀)

 

유저 생성할 때 비밀번호가 특수문자나 숫자로 시작하면 "" 주고 써야함. (영문은 상관없음)

 

connect -> 접속 관련 롤

resource -> 테이블 생성 등 관련 롤

unlimited tablespace -> 시스템 관련 룰

 

Cascade Option -> 사용금지..

 

프로파일 파라미터들은 그냥 그런게 있구나 정도만 알고있으면 됨.

 

FAILED_LOGIN_ATTEMPTS -> 5번 로그인 실패하면 락

PASSWORD_LIFE_TIME -> 90일지 지나면 비번 바꿔라

 

any 모든 스키마에 접근 가능한 권한을 부여

 

오브젝트 권한은 on절이 들어감

 

시스템 권한을 이양 하는 것은 하지마셈. (dba권한을 다른계정에 주면 안되니까)

 

dba_users - 계정 조회
dba_roles - role 조회
dba_sys_privs - role 또는 계정에 부여된 system 권한 *
dba_tab_privs- role 또는 계정에 부여된 오브젝트 권한 *
dba_role_privs -계정에 부여된 role 조회 *
role_role_privs - role에 부여된 role 조회

 

기존에 edu01 계정이 CUDR이 가능한 권한을 가지고 있었는데, SYSTEM 계정이 권한을 없애버린다고 바로

해당 SESSION을 가지고 있는 SQL 페이지부터 권한이 적용되지 않고, 새로운 접속 페이지부터 새로 부여받은 권한으로 사용가능 ( 만약 기존 개발자가 CUDR 접근 가능한 세션을 물고있으면 v$session에서 접속 종료 시켜야함)

 

---

 

6장 오브젝트 관리(242p)

 

오브젝트는 데이터를 저장하는 오브젝트와 그렇지 않는 오브젝트 존재
모든 오브젝트는 Schema에 종속

 

데이터를 저장하지 않는 주요 오브젝트 종류
 Stored Procedure, Function, Trigger, View, Package, Backage Body 

 Contraint : Primary Key, Foreign Key
 Sequence, Synonym, Directory, JOB, Schedule

 

sysnonym -> 스키마를 붙이지 않아도 테이블 조회 가능하게 설정하는 것

 

dba들은 트리거 못쓰게 함. ( 장애 일으킬 수도 있어서 위험 부담이 큼 )

 

디스크 하나당 디스크 암이 하나라서 디스크 분산 시키는게 성능을 높이는 방법임.

 

clob 타입의 데이터타입을 만들때는 테이블이랑 인덱스를 분리한다. ( 디스크 암이 두개 도는거랑 같은 원리 )

 

desc [테이블명] -> 테이블 구조 볼 수 있음 ( 디비버는 안됨, 디벨로퍼 가능 )

 

12c이상부터는 varchar2(4000) 이상 사용가능하지만 clob랑 동일하게 관리되니까 clob쓰면 됨. ( 그래도 varchar 쓰려면 파라미터 max_string_size 변경 , alter system set max_string_size = extended scope=spfile )

 

테이블이랑 컬럼에 주석 꼭 달아주기. 그래야 분석하기 쉬움 ( 테이블 정보 schemas에 comment 말하는거임 )

 

메타 같은거 없을때 효율적임.

 

synonym 은 본인 스키마 먼저 검색하고 없으면 synonym이 있는지 검색함

---

 

5. 인덱스

 

Root Node : 가장 상위 노드/ 하위의 Branch Node 수 만큼의 Row
Branch Node : Root와 Leaf 의 연결 고리 / 자기 하위의 Leaf Node 수 만큼의 Row
Leaf Node : Key + RowID로 구성/ Key 순서대로 정렬 / 이전, 이후 Leaf의 Chain

 

인덱스 Rebuild
 대량 삭제 작업이 있었을 경우 또는 Index Skew 또는 Sparse 현상이 심할 경우 진행 

 트랜잭션이 많은 시간은 되도록 피하여 작업
 작업 시 DDL Lock 발생 

 Online 옵션으로 Lock 미 발생 유도
 인덱스를 재 구조화 하는 것
 테이블의 재구성은 Move Tablespace를, 인덱스는 Rebuild를 수행

 

Invisible Index ( 인덱스 테스트 해볼 떄 사용 )
 11g  New Feature
 옵티마이저만 볼 수 없는 인덱스
 모든 다른 기능 동일
 Invisible 인덱스 생성 후 해당 세션에서 invisible 인덱스 사용 가능하도록 설정 후 테스트 

 이상이 없을 경우 visible로 변경하여 적용

 

PCT_Direct_Access 값이 100일 경우 PK 값 대신 Physical Guess 값 사용, 아닌 경우 pk로 수직탐색 함.

 

오라클은 클러스터드 인덱스 안씀, iot 씀 sql 서버 클러스터드 인덱스가 훨씬 좋아서..

 

move tablespace를 진행하면 index가 unusable 되어 인덱스 rebuild 필요

 

인덱스 생성 4단계 참고

1. 인덱스 생성의 속도를 높이기 위해 sort_area_size 확보
alter session set workarea_size_policy=manual;
alter session set sort_area_size = 1000000000;

2. Local 인덱스 생성 (tel_no)
create index edu01.ix_cust_p_01 on edu01.t_cust_p(tel_no) local;

3. non partitioned index 인덱스 생성
create index edu01.ix_cust_p_02 on edu01.t_cust_p(sal) ;

4. pga 설정 원복
alter session set workarea_size_policy=auto;

 

---

윈도우 함수

 

-- 윈도우 함수 예제

SELECT EMPLOYEE_ID

, LAST_NAME

, JOB_ID

, SALARY

, TO_CHAR(HIRE_DATE,'YYYY_MM_DD') AS 입사일자

, DEPARTMENT_ID

, SUM(SALARY) OVER () AS 총급여합계

, SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS 부서별급여합계

, ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS 부서별입사일자

, ROW_NUMBER() OVER (ORDER BY HIRE_DATE) AS 전체입사일자

, COUNT(*) OVER (PARTITION BY DEPARTMENT_ID ) AS 인원수

, SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적급여

-- PRECEDING 위 / FOLLOWING 아래 / CURRENT ROW 나까지

-- BOUNDED 범위가 정해진 / UNBOUNDED 범위가 정해지지 않은(제일 위부터)

-- RANGE BETEEN -> ORDER BY한 값을 기준으로 계산 / ROWS BETEEN 내 행 기준으로 계산

FROM HR.EMPLOYEES e;

 

-- 부서별 입사순위 1위만 뽑기

SELECT *

FROM (

SELECT EMPLOYEE_ID

, LAST_NAME

, JOB_ID

, SALARY

, TO_CHAR(HIRE_DATE,'YYYY_MM_DD') AS 입사일자

, DEPARTMENT_ID

, SUM(SALARY) OVER () AS 총급여합계

, SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS 부서별급여합계

, ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE) AS 입사순위

FROM HR.EMPLOYEES e

)

WHERE 입사순위 = 1;

 

-- GROUP BY절이 있으면 PARTITION BY를 빼고 해도 부서별 급여순으로 정렬이 가능하다.

SELECT DEPARTMENT_ID, SUM(SALARY), COUNT(*)

, ROW_NUMBER() OVER (ORDER BY SUM(SALARY) DESC )

FROM HR.EMPLOYEES e

GROUP BY DEPARTMENT_ID ;

 

ORDER BY한 컬럼이 DUP이 난다면 RANGE BETWEEN 사용 아니면 ROWS BETWEEN 쓰면됨

 

-- ROWS BETWEEN VS RANGE BETWEEN  차이점 쿼리

SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY, TO_CHAR(HIRE_DATE, 'YYYY.MM.DD') AS 입사일자

, DEPARTMENT_ID

, SUM(SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적급여

------------ AND

------------ 2 PRECEDING 2 FOLLOWING

------------ CURRENT ROW CURRENT ROW

, SUM(SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY HIRE_DATE

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 누적급여

FROM HR.EMPLOYEES e ;

 

 

조인에 관한 해석

 

-- 100부터 150까지 나오게 하고싶으면 ON절에 쓰는게 아니라 WHERE절에 써야함 (D 테이블 조건을 걸고 싶으면 WHERE 절에 쓰라는거임)

SELECT E.EMPLOYEE_ID, E.SALARY, E.DEPARTMENT_ID

, D.DEPARTMENT_ID, D.DEPARTMENT_NAME

FROM HR.DEPARTMENTS d LEFT OUTER JOIN HR.EMPLOYEES e

ON D.DEPARTMENT_ID = E.DEPARTMENT_ID

AND D.DEPARTMENT_ID BETWEEN 100 AND 150

;

 

-- 이거는 E테이블 기준으로 급여가 9000이상인걸 뽑고 싶은거니까 ON절에다가 조건을 거는거고 ( ANSI방식 )

SELECT E.EMPLOYEE_ID, E.SALARY, E.DEPARTMENT_ID

, D.DEPARTMENT_ID, D.DEPARTMENT_NAME

FROM HR.DEPARTMENTS d LEFT OUTER JOIN HR.EMPLOYEES e

ON D.DEPARTMENT_ID = E.DEPARTMENT_ID

AND E.SALARY >= 9000

WHERE D.DEPARTMENT_ID BETWEEN 100 AND 150

--AND E.SALARY >= 9000 -- 이렇게 쓰면 아우터의 의미가 사라지게 됨.

;

 

-- 이거는 오라클 방식

SELECT E.EMPLOYEE_ID, E.SALARY, E.DEPARTMENT_ID

, D.DEPARTMENT_ID, D.DEPARTMENT_NAME

FROM HR.DEPARTMENTS d, HR.EMPLOYEES e

WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID (+)

AND E.SALARY (+) >= 9000 -- 샐러리에 (+)가 없으면 아우터의 의미가 사라지게 됨.

AND D.DEPARTMENT_ID BETWEEN 100 AND 150

;

 

즉 조인 당하는 입장이면 WHERE절에, 조인 하러 가는 입장이면 ON절에 쓰면 됨.

쉽게 설명하고자 위 예시에서는 조인 당하는 입장이 D테이블 , 조인 하러 가는 입장이 E테이블인걸로 하겠음

 

외래키(332p)

외래키는 별도의 인덱스 생성 필요

 

파티션(338p)

 

파티션 테이블은 차세대 시작 시점에 정해야함. ( 나중에가서 바꾸기 쉽지않음 )

 

파티션 키컬럼이 WHERE절에 무조건 들어가야 성능이 나옴.

 

LOCAL 인덱스 파티션 -> 파티션 플루닝 신경써서 키컬럼 WHERE절에 넣어줘야 함.

NON 인덱스 파티션 -> 파티션 플루닝 걱정은 없는데 나중에 파티션 지우거나 추가할때마다 인덱스 리빌드 해줘야해서 빡셈 ( GLOBAL 파티션이라고 사람들이 착각하고 있다고 함 GLOBAL 파티션은 쓰는곳 본적 없다고 하심. 대부분 NON 파티션임. )

 

이번주는 대부분 PDF 파일에서 보면 이해되는 내용들이라 많이 정리하지 않았음.