1. 시퀀스
- 규칙적인 순서를 갖는 객체(숫자만 가능하고 자동 부여 목적)
- MS-SQL - 컬럼의 속성으로 부여
SQL> create sequence s_dno
2 increment by 3 // 증가치 (증가치 음수이면
3 start with 101 // 초기치
4 maxvalue 999; // 최대치
시퀀스가 생성되었습니다.
SQL> insert into dept(deptno, dname, loc) values(s_dno.nextval, '전략부', '서울');
1 개의 행이 만들어졌습니다.
SQL> insert into dept(deptno, dname, loc) values(s_dno.nextval, '기획2부', '서울');
1 개의 행이 만들어졌습니다.
SQL> select s_dno.currval from dual;
CURRVAL
----------
104- no cycle on (기본값)
select s_dno.currval from dual; // 시퀀스의 현재값 확인 - insert 실패해도 시퀀스는 증가한다
2. 뷰
- 데이터 사전 뷰 : 오라클db에서 정보를 가진 객체
- 정적뷰
- all_(table, constraints, …)
- 모든 세션(계정, 유저)이 조회할 수 있음
- 간단한 요약정보
- 쓸일이 별로 없다.
- user_
- 현재 세션의 모든 정보 조회 가능
- all_(table, constraints, …)
- 정적뷰
이름 널? 유형
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000) select table_name, tablespace_name, num_rows, blocks from user_tables;- dba_
- 모든 세션의 모든 정보 조회 가능
- 데이터베이스 관리자만 접근 가능
// 객체 접근 권한이 없을 때 - "객체는 존재하지 않습니다"
SQL> desc dba_users;
ERROR:
ORA-04043: "SYS"."DBA_USERS" 객체는 존재하지 않습니다.- 동적뷰
- v$ ~
- 동적 성능 뷰
- 통계정보를 가진 뷰 → dba 만 접근 가능
NAME LOG_MODE
--------- ------------
KIBWA NOARCHIVELOG연습
- 제약조건 : user_constraints
- 어느 컬럼에 걸렸는지 확인 : user_cons_columns
- 인덱스 : user_indexes
- 어느 컬럼에 걸렸는지 확인 : user_idx_columns
- 어느테이블, 제약명, 제약조건종류( types), 어느컬럼
SQL> select table_name, constraint_name, constraint_type from user_constraints;
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE
-------------------- -------------------- --------------------
DEPT PK_DEPT P
EMP PK_EMP P
EMP FK_DEPTNO Rselect
uc.table_name "테이블명", uc.constraint_name "제약명", uc.constraint_type "제약종류", ucc.column_name "컬럼명"
from user_constraints uc, user_cons_columns ucc
where uc.CONSTRAINT_NAME = ucc.CONSTRAINT_NAME;
테이블명 제약명 제약종류 컬럼명
------------------------------ -------------------- ------------------------------ ------------------------------
EMP FK_DEPTNO R DEPTNO
EMP PK_EMP P EMPNO
DEPT PK_DEPT P DEPTNO
테이블명 제약명 제약종류 컬럼명
------------------------------ -------------------- ------------------------------ ------------------------------
SAWON SAWON_DEPTNO_FK R DEPTNO
SAWON SAWON_SAMGR_FK R SAMGR
GOGEK GOGEK_GODAM_FK R GODAM
GOGEK GOGEK_GOJUMIN_NO U GOJUMIN
GOGEK GOGEK_GOBUN_PK P GOBUN
SAWON SAWON_SABUN_PK P SABUN
DEPT DEPT_DNAME_NO U DNAME
DEPT DEPT_DEPTNO_PK P DEPTNO
SAWON_EX SYS_C0011063 C //not null -> C SANAME
SAWON_10 SYS_C0011064 C SANAME
SAWON SYS_C0011055 C SANAME
테이블명 제약명 제약종류 컬럼명
------------------------------ -------------------- ------------------------------ ------------------------------
SAWON SAWON_SASEX_CK C SASEX- 사용자 정의 뷰 (보안 객체, create view 권한 필요)
- 뷰를 업데이트하면 원본 테이블도 업데이트 된다.
- 형식
create view sawon_v as select * from sawon; -> as 이하 쿼리문만 저장- 목적
- 제한된 엑세스 제공 - 접근 가능한 보여주고 싶은 정보만 보여줄 수 있다. (보안)
- 복잡한 질의를 간단하게 질의 가능 - 자주 사용하는 쿼리를 뷰로 만들 수 있다. (편리성)
- 기본값 뷰에서 DML가능하나, 컬럼이 조작되거나 집계된 결과의 뷰에서는 불가능!
- sum, avg 등이 사용되면 DML 할 수 없다!
- with read only → DML 가능 뷰도 읽기전용으로 만들기
- with check option → 뷰가 생성될 때 주어진 조건(where 절)으로 체크제약 설정
- where 절에 명시된 조건에 위배되는 명령은 수행할 수 없다.
3. 인덱스
- where 절에서 많이 사용되는 컬럼에 생성
- pk, uq 제약조건은 기본값으로 생성된다.
- 인덱스를 사용하지 않는 검색 → 풀 테이블 스캔 (실무에서 절대 사용하면 안됨)
- 풀 테이블 스캔이 더 좋은 경우
- 1 extent (객체 저장단위, block의 집합, 64k(8 block) 이하는 권장)
- 아주 작은 크기의 테이블인 경우 인덱스 굳이 사용할 필요 X
- 풀 테이블 스캔이 더 좋은 경우
- 정적데이터(변경 X, 중복값이 적음) 컬럼에 생성
- DML명령이 발생할때마다 인덱스는 재구성된다.
인덱스를 사용하려면
- 생성된 순서대로 사용해야 한다.
- 생성된 컬럼 그대로 (조작하지 않고) 사용해야 한다.
종류 ⭐
- b*tree(NORMAL)
- 기본값 - pk, uq
- 완전 이진트리 구조 → 검색 한번마다 범위가 절반씩 줄어든다.
- 중복값 적은 컬럼에 생성해야 함
- bitmap
- 0 or 1
- 중복값이 많은 컬럼이 where절에 많이 사용되는 경우 생성하면 좋다 (ex. 남/녀)
- function-based (함수 기반)
- ex) sapay * 1.1
- 컬럼에 수식이 포함된 경우 → 수식 자체를 인덱스로 만든다.
- reverse-key (역방향-키)
- ← 방향으로 저장
- b*tree 의 역방향키
- 동적 데이터 컬럼에 사용 (변경이 많이 일어나는 컬럼)
- 0001, 0002, 0100, 0101 (이렇게 정렬되어있는 인덱스가)
- 1000, 2000, 0010, 1010 (반대방향으로 저장되어있음) → 변경 발생해도 재정렬 X
- descending (내림차순)
- 변경된 컬럼이 사용되었기 때문에 function-based 타입 중 하나로 취급된다.
- 최근 데이터에 엑세스하는 컬럼 (날짜)
- 날짜 컬럼에 내림차순 인덱스 만들어놓으면 insert때부터 정렬되어 들어간다.
- bitmap-join
- 조인 조건에 생성
user_indexes, user_ind_columns
select table_name, index_name, index_type from user_indexes;
TABLE_NAME INDEX_NAME INDEX_TYPE
-------------------- ------------------------------ ---------------------------
SAWON SAWON_SABUN_PK NORMAL -> b tree
GOGEK GOGEK_GOBUN_PK NORMAL
GOGEK GOGEK_GOJUMIN_NO NORMAL
DEPT DEPT_DEPTNO_PK NORMAL
DEPT DEPT_DNAME_NO NORMALselect table_name, index_name, column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
-------------------- ------------------------------ ------------------------------
DEPT DEPT_DEPTNO_PK DEPTNO
DEPT DEPT_DNAME_NO DNAME
SAWON SAWON_SABUN_PK SABUN
GOGEK GOGEK_GOBUN_PK GOBUN
GOGEK GOGEK_GOJUMIN_NO GOJUMIN인덱스 생성
create index i_sawon_sabun on sawon(sabun);
create bitmap index i_sawon_sasex on sawon(sasex);
create index i_sawon_sapay on sawon(sapay*1.1);
create index i_sawon_samgr on sawon(samgr) reverse;
create index i_sawon_sahire on sawon(sahire desc);
// 생성 후
SQL> select table_name, index_name, index_type from user_indexes;
TABLE_NAME INDEX_NAME INDEX_TYPE
-------------------- ------------------------------ ---------------------------
SAWON SAWON_SABUN_PK NORMAL
SAWON I_SAWON_SASEX BITMAP
SAWON I_SAWON_SAPAY FUNCTION-BASED NORMAL -- 함수기반키
SAWON I_SAWON_SAMGR NORMAL/REV -- 역방향키
SAWON I_SAWON_SAHIRE FUNCTION-BASED NORMAL -- 함수기반키(내림차순)
GOGEK GOGEK_GOBUN_PK NORMAL
GOGEK GOGEK_GOJUMIN_NO NORMAL
DEPT DEPT_DEPTNO_PK NORMAL
DEPT DEPT_DNAME_NO NORMAL
SQL> select table_name, index_name, column_name from user_ind_columns;
TABLE_NAME INDEX_NAME COLUMN_NAME
-------------------- ------------------------------ ------------------------------
DEPT DEPT_DEPTNO_PK DEPTNO
DEPT DEPT_DNAME_NO DNAME
SAWON SAWON_SABUN_PK SABUN
SAWON I_SAWON_SASEX SASEX
SAWON I_SAWON_SAMGR SAMGR
GOGEK GOGEK_GOBUN_PK GOBUN
GOGEK GOGEK_GOJUMIN_NO GOJUMIN
SAWON I_SAWON_SAPAY SYS_NC00009$ -- 임의의 컬럼에 할당된다.
SAWON I_SAWON_SAHIRE SYS_NC00010$생성된 인덱스를 사용하지 못하는 경우
- like, is null, not(부정 연산자) 사용한 경우
- 컬럼이 조작된 경우(인덱스가 생성된 컬럼 그대로를 사용하지 않은 경우)
- 생성된 인덱스 컬럼의 순서대로 사용하지 않은 경우
권한 (privilege)
DCL
- 권한 관련 명령, 데이터 관리(제어 명령)
- grant, revoke, [deny(ms-sql)]
1. 사용가능한 명령 권한
- DBA가 부여해주는 권한이다.
- create user, create session , …
- 권한은 무조건 동사로 되어있다.
- 상속절 : with admin option
- 상속 후 권한 회수 시 개별회수해야 한다. → 권한 상속을 신중히!
2. 객체 엑세스 권한
- 대상객체가 존재! (on절아래에 명시)
- on 절은 , 로 구분해서 여러개를 한꺼번에 줄 수 없다.
- 객체에는 주인이 있다. (소유자)
- 소유자가 부여해주는 권한이다.
- select, insert, update, delete, [execute] on 객체 (대상객체)
- 상속절 : with grant option
- 상속 후 권한 회수 시 일괄회수된다.
형식
grant 권한, ... [on 객체명] to [세션|롤].... [with admin|grant option];
revoke 권한,... [on 객체명] from [세션|롤];4. role
- 관련 privilege 들의 집합
- 롤을 사용하면 권한 유지보수, 관리가 쉽다!
- 기본 시스템 롤 : connect, resource, dba, …
SQL> conn / as sysdba; -- SYSDBA로 접속
연결되었습니다.
SQL> select grantee, privilege from dba_sys_privs
2 where grantee='CONNECT' or grantee='RESOURCE' or grantee='DBA'
3 order by 1;
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
CONNECT CREATE SESSION
DBA ADMINISTER ANY SQL TUNING SET
DBA ADMINISTER DATABASE TRIGGER
DBA ADMINISTER RESOURCE MANAGER
DBA ADMINISTER SQL MANAGEMENT OBJECT
DBA ADMINISTER SQL TUNING SET
DBA ADVISOR
DBA ALTER ANY ASSEMBLY
DBA ALTER ANY CLUSTER
DBA ALTER ANY CUBE
DBA ALTER ANY CUBE DIMENSION
(중략)
RESOURCE CREATE CLUSTER
RESOURCE CREATE INDEXTYPE
RESOURCE CREATE OPERATOR
RESOURCE CREATE PROCEDURE
RESOURCE CREATE SEQUENCE
RESOURCE CREATE TABLE
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
RESOURCE CREATE TRIGGER
RESOURCE CREATE TYPE롤 관리 (create role 권한 필요)
- 롤 생성
- 롤에 관련 권한들을 부여
- 세션(유저)에게 롤 권한을 부여
conn / as sysdba;
grant create role to sky;
conn sky/pass
create role sawon_admin;
grant select to sawon to sawon_admin;
grant select on dept to sawon_admin;
grant select on gogek to sawon_admin;
grant sawon_admin to scott;
conn scott/tiger;
select * from sky.sawon; // 접근 가능 SQL> SELECT * FROM USER_ROLE_PRIVS ; -> 현재 세션 사용자에게 부여된 Role 조회5. Synonym (동의어)
- create synonym 권한 필요 (보안 객체)
- 형식 : create [public] synonym sy_name for table_name;
- public은 관리자만 사용 가능!
- 전역에서 사용가능한 Synonym 만들기
connn / as sysdba;
create **public** synonym s for sky.sawon; // [s = sky.sawon]
conn scott/tiger
select * from s;
개발자 헤이린