오늘의 코딩순서 + 코딩 포인트
1.데이터 무결성
- 성능보다도 중요한 것이 무결성, 무결성이 좋지 않으면 사용률이 줄어듬
1-1. 데이터 무결성 제약조건⭐⭐⭐
- 정의: 데이터 정확성과 일관성을 보장
- 데이터 정확성을 유지하여 다양한 종류의 업무규칙을 고려한 예
- student 테이블에서 학년 데이터는 1,2,3,4 중의 하나의 값만 입력
- 모든 학번은 유일
- student 테이블의 지도교수 번호는 professor 테이블의 교수 번호 중의 하나와 반드시 일치
- 장점
- 테이블 생성시 무결성 제약조건을 정의 가능
- 테이블에 대해 정의, 데이터 딕셔너리(=데이터사전, 시스템 카탈로그)(밑에 기술)에 저장되므로 응용 프로그램에서 입력된 모든 데이터에 대해 동일하게 적용
- 제약 조건을 활성화, 비활성화 할 수 있는 융통성
1-2. 제약조건(Constraint) 종류⭐⭐⭐
NOT NULL ⊂ UNIQUE KEY ⊂ PRIMARY KEY
tip) KEY란?: 테이블에 있는 tuple을 유일하게 식별하는(uniquely identify) attributes 집합을 의미함
tuple란?: 하나의 레코드를 나타내는 행(row)을 의미함
INDEX란?: 테이블에 있는 tuple을 빠르게 찾기 위해 테이블에 적용되는 추가적인 자료 구조
| 무결성 제약조건 | 설명 |
| NOT NULL | - 데이터 입력 시에 필수로 값이 들어가야 하는 것을 의미함. - 누락이 되어서는 안되는 부분 (예를 들어, 유저 테이블의 이름) 과 같은 부분은 NOT NULL로 컬럼을 정의하여, 데이터가 입력되기 전에 오류를 내도록 함 NULL값이 들어갈 경우 오류가 발생하며 데이터가 아예 입력되지 않음 - 데이터가 무조건 들어가야 하는 컬럼에는 NOT NULL을 사용하여 누락되는 것을 방지 >>>공백과 NULL은 같은 데이터가 아님, NULL 값은 정의할 수 없는 값이며 공백과는 다른 의미임 DB에서 ' '(공백)은 NULL이 아닌, 값이 있는 것으로 인식됨 |
| 고유키(unique(유일성) key) | - 테이블의 모든 행에서 고유한 값을 갖는 열 또는 열 조합을 지정함 - unique: 해당 테이블내에서 존재하는 값이 유일해야 함, INSERT or UPDATE 시 제약이 걸려있는 컬럼에 동일한 데이터가 나올 경우 오류 발생 - NULL 값은 UNIQUE 제약 조건이 적용되지 않음. UNIQUE로 설정된 컬럼이어도 NULL이 가능한 컬럼이라면 NULL 데이터 행이 여러개일 수 있음 - CONSTRAINTS 키웓를 사용하여 제약조건의 이름을 명시해줄 수 있음, 다만 UNIQUE 제약으로 들어가는 컬럼들의 조합은 유일해야 함 |
| 기본키(primary key, pk) ⭐⭐⭐ |
- 한 테이블 내의 PRIMARY KEY는 해당 테이블 내에서 데이터를 식별하기 위한 제약조건 - 해당 칼럼 값은 반드시 존재해야 하며 유일해야 함 - ⭐⭐⭐(면접필수질문) UNIQUE + NOT NULL() + 최소성 제약조건을 결합한 형태 - UNIQUE와는 다르게 한 테이블 내의 하나의 컬럼만 지정 가능 |
| 참조키,외래키 (foreign key, fk) ⭐⭐⭐ (기사시험 단골 손님) |
- 한 열과 참조된 테이블의 열 간의 외래 키 관계를 설정하고 시행함 - 해당 컬럼에 참조하는 테이블로부터 존재하는 값들만 사용한다는 의미의 제약 조건, 만약 참조하려는 데이터가 없을 경우 오류발생 - 여러 개의 컬럼에 중복적으로 적용가능, 즉 하나의 테이블이 여러 개의 테이블을 참조할 수 있음 - 외래키의 조건: Restrict, Cascading Delete, SET NULL (면접필수질문)⭐⭐⭐ |
| CHECK | - 해당 칼럼에 저장 가능한 데이터 값의 범위나 조건 지정 - 조건에 부합하는 데이터만 입력이 가능하도록 하는 제약 조건 - 조건에는 기본연산자, 비교연산자, IN, NOT IN 등등 사용 가능 |
0. 제약조건(Constraint) 적용 위한 강좌(subject) 테이블 인스턴스
CREATE TABLE subject (
subno NUMBER(5) CONSTRAINT subject_no_pk PRIMARY KEY, -- PRIMARY KEY을 빼면 관리가 쉽지 않음
subname VARCHAR2(20) CONSTRAINT subject_name_nn NOT NULL, -- NOT NULL을 빼면 관리가 쉽지 않음
term VARCHAR2(1) CONSTRAINT subject_term_ck CHECK(term IN('1','2')),
typeGUbun VARCHAR2(1)
);
COMMENT ON COLUMN subject.subno IS '수강번호';
COMMENT ON COLUMN subject.subname IS '수강과목';
COMMENT ON COLUMN subject.term IS '학기';
1.INSERT
-- PK Constraint --> Unique
INSERT INTO subject(subno , subname , term , typegubun)
Values(10001 , 'Spring개론' , '1' , '1');
-- >>오류걸림 -- 오류 보고 ORA-00001: unique constraint (SCOTT.SUBJECT_NO_PK) violated
-- ↳ pk에 UNIQUE 제약 조건이 걸리기 때문-- PK Constraint --> NN
INSERT INTO subject(subname , term , typegubun)
Values('Spring개론2' , '1' , '1');
-- >>오류걸림 -- 오류 보고 -ORA-01400: cannot insert NULL into
-- ↳ pk에는 NULL값이 들어갈 수 없다-- subname NN
INSERT INTO subject(subno , term , typegubun)
Values(10003 , '1' , '1');
-->>오류걸림 --오류 보고 - ORA-01400: cannot insert NULL into ("SCOTT"."SUBJECT"."SUBNAME")-- Check Constraint --> term
INSERT INTO subject(subno , subname , term , typegubun)
Values(10003 , 'Spring개론3' , '5' , '1');
-->>오류걸림 --오류 보고 -ORA-02290: check constraint (SCOTT.SUBJECT_TERM_CK) violated
-- ↳ 여기서 term은 1과 2뿐, 그런데 5를 넣으려고 했으니 오류가 남
2.UNIQUE
- Table 선언시 못한 것을 추후 정의 가능
- Student Table 의 idnum을 unique로 선언
ALTER TABLE student
ADD CONSTRAINT stud_idnum_uk UNIQUE(idnum);
-->>오류걸림 --오류 보고 - ORA-02299: cannot validate (SCOTT.STUD_IDNUM_UK) - duplicate keys found
-- ↳ 중복되는 값이 있음-- idnum --> unique constraint
INSERT INTO student(studno , name , idnum)
VALUES (30101 , '대조영' , '8012301036613');INSERT INTO student(studno , name , idnum)
VALUES (30102 , '강감찬' , '8012301036613');
-->>오류걸림 --오류 보고 -ORA-00001: unique constraint (SCOTT.STUD_IDNUM_UK) violatedINSERT INTO student(studno , name)
VALUES(30103 , '강민첨');
--오류 안걸림
3.NOT NULL
-- Student Table 의 name을 NN로 선언
ALTER TABLE student
MODIFY (name CONSTRAINT stud_name_nn NOT NULL);
INSERT INTO student(studno , idnum)
VALUES(30103 , '8012301036614');
-->>오류걸림 --오류 보고 -ORA-01400: cannot insert NULL into ("SCOTT"."STUDENT"."NAME")
4. CONSTRAINT 조회⭐⭐⭐
-- CONSTRAINT 조회 ***
SELECT CONSTRAINT_name , CONSTRAINT_Type
FROM user_CONSTRAINTs
WHERE table_name IN('SUBJECT','STUDENT') --TBL이름은 대문자로 하는것이 좋음
;1-3. Foreign Key의 제약조건⭐⭐⭐
- 서로 다른 테이블 간 관계를 정의하는데 사용하는 제약 조건
- 외래키 제약조건은 두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장해 주는 역할을 함.
외래키 관계를 설정하면 하나의 테이블이 다른 테이블에 의존하게 된다 -
테이블을 생성할 때 FK를 정의하는데, FK가 정의된 테이블이 자식 테이블이고 레퍼런스되는 테이블은 부모 테이블이다
- REFERENCES : 참조할 부모 테이블과 부모 테이블에 있는 컬럼을 정의함
-
부모 테이블은 미리 생성되어 있어야 하며, 참조되는 컬럼에 존재하는 값만을 입력 할 수 있다.
-
부모 테이블은 FK로 인해 삭제가 불가능하다.
-
데이터 타입이 반드시 일치해야 한다.
-
참조되는 컬럼은 PK이거나 UK(Unique key)만 가능하다.
예시) 대충 emp TBL에 뭔가 하나 만들고나서 실행, 대신 emp와 dept의 deptno컬럼에 동일한 값 만들기
DELETE emp
WHERE empno =1000
;
조건 1. Restrict(작업없음) -->>emp TBL>>우클릭>>편집>>제약조건>>외래키>>'작업없음' 확인
- 자식 존재 삭제 안됨 (연관 관계 때문)
- 두 값 모두 남아있음
DELETE dept
WHERE deptno =50
;
조건 2. Cascading Delete(종속삭제) -->>emp TBL>>우클릭>>편집>>제약조건>>외래키>>'종속삭제' 확인
- 같이 죽자: 부모, 자식 값 전부 삭제됨
- 1) 선언 Emp Table에서 REFERENCES DEPT (DEPTNO)
- 2) 예시 integrity constraint (SCOTT.FK_DEPTNO) violated - child record found
DELETE dept
WHERE deptno =50
;
조건 3. SET NULL(널 설정) -->>emp TBL>>우클릭>>편집>>제약조건>>외래키>>'널 설정' 확인
: 1) 종속 NULL 선언
- Emp Table에서 REFERENCES DEPT (DEPTNO) ON DELETE SET NULL
- 자식이 부모와 관계가 끊겨 Null값으로 변경되지만 남아있음, 부모는 삭제됨
DELETE dept
WHERE deptno =50
;2. 인덱스 관리⭐⭐⭐(인덱스 관리가 평판을 좌우한다!)
1. 인덱스의 개념
1. 인덱스란?
- 인덱스는 SQL 명령문의 처리속도를 향상시키기 위해 컬럼에 대해 생성하는 객체
좀 더 쉬운말: TBL의 어떤 컬럼을 향상시키기 위한 객체 - 인덱스는 포인트를 이용하여 테이블에 저장된 데이터를 랜덤 액세스하기 위한 목적으로 사용
- 권장사항: 한 TBL당 7개 이상의 인덱스는 걸지 않는다
2. 인덱스 생성
- UNIQUE: 고유 인덱스 시정
- ASC/DESC: 인덱스 키를 오름차순 또는 내림차순으로 정렬
3. 인덱스의 구조
B-트리 인덱스: 왼쪽으로 갈수록 값이 줄어들고, 오른쪽으로 갈수록 값이 늘어난다
3. 인덱스의 종류
1) 고유 인덱스
- 유일한 값을 가지는 칼럼에 대해 생성하는 인덱스
- 모든 인덱스 키는 테이블의 하나의 행과 연결됨
CREATE UNIQUE INDEX idx_dept_name
ON department(dname); --INDEX IDX_DEPT_NAME이(가) 생성되었습니다.INSERT INTO department VALUES(300 , '이과대학' , 10 , 'kkk');
INSERT INTO department(deptno , dname , college , loc) VALUES(301 , '이과대학' , 10 , 'kkk2');
-- 오류 보고 -ORA-00001: unique constraint (SCOTT.IDX_DEPT_NAME) violated
-- unique 제약 조건과 unique ㅇㅇ가 같이 걸림
2) 비고유 인덱스:
- 중복된 값을 가지는 칼럼에 대해 생성하는 인덱스
- 하나의 인덱스 키는 테이블의 여러 행과 연결 가능
문제 1-1) 학생 테이블의 birthdate 칼럼을 비고유 인덱스로 생성하여라
CREATE INDEX idx_stud_birthdate -- 테이블 생성
ON student(birthdate);
INSERT INTO student(studno , name , idnum , birthdate)
VALUES(30102 , '김유신' , '8012301036614' , '84/09/16');SELECT *
FROM student
WHERE birthdate = '84/09/16'
;
--만약 위의 인덱스를 걸어두지 않았다면,
-- 처음부터 끝까지 모든 컬럼을 다 대조하여 거치느라(Full Scan) 컬럼이 길어질수록 시간이 오래걸린다
3) 단일 인덱스: 하나의 컬럼으로 구성된 인덱스, 하나의 컬럼을 기준으로 인덱싱 되기 때문에 활용 또한 단순함
4) 결합 인덱스(복합 인덱스): 두 개 이상의 칼럼을 결합하여 생성하는 인덱스
문제1-2) 학생 테이블의 deptno, grade 칼럼을 결합 인덱스로 생성, 결합 인덱스의 이름은 idx_stud_dno_grade 로 정의
CREATE INDEX idx_stud_dno_grade --테이블 생성
ON student(deptno , grade);
SELECT *
FROM student
WHERE grade = 2
AND deptno = 101
;
--WHERE grade = 2
--AND deptno = 101
-- 오류 보고 - SQL 오류: ORA-00904: "IDNM": invalid identifier
-- 밑의 Optimizer를 하고 나서야 실행됨>>>> Optimizer 실행
4. optimizer
- 정의
- SQL 개발자가 SQL을 작성하여 실행할 때, 옵티마이저는 SQL을 어떻게 실행할 것인지를 계획하게 된다
- QL 실행 계획(Execution Plan)을 수립하고 SQL을 실행한다
- 옵티마이저는 SQL의 실행 계획을 수립하고 SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어이다
- 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라지기에, SQL 성능에 옵티마이저는 아주 중요한 역할을 한다
- 특징
- 데이터 딕셔너리(Data Dictionary)에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정함
- 옵티마이저는 여러 개의 실행 계획 중에서 최저 비용을 가지고 있는 계획을 선택해서 SQL을 실행함
- 필요성: SQL 개발자가 작성한 SQL문을 어떻게 실행하느냐에 따라 성능이 달라짐
- 실행 방법
- 개발자가 SQL을 실행하면 파싱(Parsing)을 실행해서 SQL의 문법 검사 및 구문 분석을 수행
- 구문 분석이 완료되면, 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립함
- 실행 수립이 완료되면, 최종적으로 SQL을 실행하고, 실행이 완료되면 데이터를 인출(Fetch)
--- Optimizer
--- 1) RBO 2) CBO
-- RBO 변경
ALTER SESSION SET OPTIMIZER_MODE = RULE;
| 구분 | RBO(Rule Based Optimizer) | CBO(Cost Based Optimizer) |
| 특징 | - 통계정보를 사용하지 않고 SQL문 실행시 사전에 정의된 규칙에 따라 실행 - 사후에 RBO 규칙을 변경하기가 어려움 - 예전에는 많이 사용했지만 요즘에는 덜 사용하는 추세 |
실행계획을 미리 예측하기 힘들고, 실행계획을 특정한 처리경로로 유도하는 등의 제어가 어려움 - 최적화된 결정을 위해 정기적으로 통계정보의 갱신 필요 - 요즘에 많이 사용하는 추세 |
| 경로 선택 | - 인덱스 구조나 비교 연산자에 따른 순위여부를 기준으로 최적 경로 설정 | - 처리 방법에 대한 비용을 산정한 후 최소 비용이 소요되는 방법 선택 |
| 동작 방식 | - ROWID를 이용한 직접 접근, INDEX를 이용한 Random Access, Full Table Scan을 이용하는 순차 Scan으로 구분 - 각 경로의 분류는 하나 이상의 경로가 유용할 떄 한 경로를 선택하기 위한 규칙-기반 접근 방식에 의해 사용 |
-시스템 통계정보(CPU, 디스크 엑세스타임)를 이용해서 처리시간으로 환산한 방식을 사용 - SQL 문장에서 대한 여러가지 경우의 수별로 처리시간에 비례한 비용을 산출해내고 이들 비용에서 가장 작은 비용을 갖는 플랜을 결정 |
| 장점 | - 판단이 매우 규칙적이고 분명하여 수립될 실행계획이 예측 가능하기 때문에 사용자가 원하는 처리 경로로 유도하기에 용이 | - 최적화를 깊이 이해하고 있지 않더라도 최소 성능 보장 |
| 단점 | - 통계 정보를 반영하지 않으므로 오차 발생 가능 | - 실행 계획을 미리 예측, 제어가 어려움 |
1. SESSION 상에서 변경하는 Optimizer: 프로그램을 종료해도 값이 남아 저장된다
--SESSION 상에서 변경할때
alter session set optimizer_mode = rule
alter session set optimizer_mode = CHOOSE
alter session set optimizer_mode = first_rows
alter session set optimizer_mode = ALL_ROWS --가장 느림- choose: 명령어에 따라 RBO 또는 CBO를 알아서 선택
- first_rows: 가장 성능이 좋음
- ALL_ROWS: Full Scan으로 돌리느라 아주 오래걸림1
>> 문제 1-2의 구조와 비슷해 보이지만 순서를 바꿈으로서 성능이 바뀐다, 테이블에 따라 순서대로 작성해야 성능↑↑
SELECT *
FROM student
WHERE deptno = 101
AND grade = 2
;
2. SQL Optimizer: : 일회성/ 프로그램을 종료하면 값이 날아간다
SELECT /*+first_rows*/ ename
FROM emp
;SELECT /*+rule*/ ename
FROM emp
;
3. OPTIMIZER 모드 확인
SELECT NAME , VALUE , ISDEFAULT , ISMODIFIED , DESCRIPTION
FROM V$SYSTEM_PARAMETER
WHERE NAME LIKE '%optimizer_mode%'
;
TIP) Execute Plan
참고: https://gngsn.tistory.com/161
- 말 그대로 SQL 문으로 요청한 데이터를 어떻게 불러올 것인지에 관한 계획, 즉 경로를 의미함
- 지름길을 사용해 데이터를 빠르게 찾아낼 것인지, 지름길이 있어도 멀리 돌아가서 찾을 것인지 미리 확인할 수 있음
- 키워드: EXPLAIN, DESCRIBE, DESC
- Execute Plan의 단계는 Step라고 함,각 스텝별로 그 단계에서 어떤 명령이 어떻게 수행되었고, 총 건의 데이터가 처리되었으며, 이를 위해 얼마만큼의 비용과 시간이 들었는지가 표시됨
- 읽는 방법
- 위에서 아래로 읽어 내려가면서 제일 먼저 읽을 스텝 찾기
- 내려가는 과정에서 같은 들여쓰기가 존재하면, 무조건 위에서 아래 순으로 읽기
- 읽고자 하는 스텝보다 들여쓰기가 된 하위 스텝이 존재한다면, 가장 안쪽으로 들여쓰기 된 스텝을 시작으로 하여 한 단계씩 상위 스텝으로 읽어 나오기
2. 인덱스의 효율적인 사용 방법
2-0. 인덱스가 효율적인 경우
- WHERE 절이나 JOIN 조건절에서 자주 사용되는 컬럼
- 전체 데이터 중에서 10~15% 이내의 데이터를 검색하는 경우
- 두 개 이상의 컬럼이 WHERE 절이나 JOIN 조건절에서 자주 사용되는 경우
- 테이블에 저장된 데이터의 변경이 드문 경우
- 열에 NULL 값이 많이 포함된 경우, 열에 광범위한 값이 포함된 경우
-- 학생 테이블에 생성된 PK_DEPTNO 인덱스를 재구성(PK_DEPTNO가 깨졌을때 사용)
ALTER INDEX PX_DEPTNO REBUILD;
2-1. 1.Index 조회
SELECT index_name , table_name , column_name
FROM user_ind_columns
;
2-2. Index 생성 emp(job)
CREATE INDEX idx_emp_job ON emp(job);
2-3. 조회
SELECT * FROM emp WHERE job = 'MANAGER'; -- = Index OK
SELECT * FROM emp WHERE job <> 'MANAGER'; -- <> Index No
SELECT * FROM emp WHERE job Like '%NA%'; -- Like '%NA%' Index No
SELECT * FROM emp WHERE job Like 'MA%'; -- Like 'MA%' Index OK
-- %앞글자를 매길때 컬럼명의 첫글자를 쓰면 Index가 먹여짐
SELECT * FROM emp WHERE UPPER(job) = 'MANAGER'; --UPPER(job) Index No-- 조건따라 자꾸 달라지는 INDEX 삽입. 그렇다면 INDEX를 어떻게 먹일까??
↳ 5) 함수 기반 인덱스
- 함수 기반 인덱스는 오라클 8i 버전부터 지원하는 새로운 형태의 인덱스로 칼럼에 대한 연산이나 함수의 계산 결과를 인덱스로 생성 가능
- UPPER(column_name) 또는 LOWER(column_name) 키워드로 정의된 함수 기반 인덱스를 사용하면 대소문자 구분 없이 검색할 수 있음
- 함수 기반 인덱스는 INSERT, UPDATE 시에는 새로운 값을 인덱스에 추가
CREATE INDEX uppercase_idx ON emp (UPPER(job));SELECT * FROM emp WHERE UPPER(job) = 'SALESMAN';
⭐⭐⭐PK와 INDEX의 비교
| 항목 | PK | INDEX |
| 특성 | unique, NN, 최소성 | Trade off |
| 구조 | cluster | Non-Cluster Index 객체 따로 |
| Count | / | 200개 이상~2^8 이하 (권장 7개 이하) |
| Focus | 무결성 | Performance(성능) |
6) DESCENDING 인덱스: 칼럼별로 정렬 순서를 별도로 지정하여 결합 인덱스를 생성하기 위한 방법
3. Transaction 트랜잭션
Transaction 트랜잭션
- 관계형 데이터베이스에서 실행되는 여러 개의 SQL명령문을 하나의 논리적 작업 단위로 처리하는 개념
- 데이터베이스의 상태를 변화시키기 위해서 수행하는 작업의 단위
- 데이터를 조작하는 업무의 물리적(시간적)단위
- 1개 이상의 명령어(행동, SQL)로 구성된 작업 단위(기간)
- 특징: 원자성, 일관성, 고립성, 지속성
COMMIT : 트랜잭션의 정상적인 종료
- 트랜잭션내의 모든 SQL 명령문에 의해 변경된 작업 내용을 디스크에 영구적으로 저장하고 트랜잭션을 종료
- 해당 트랜잭션에 할당된 CPU, 메모리 같은 자원이 해제
- 서로 다른 트랜잭션을 구분하는 기준
- COMMIT 명령문 실행하기 전에 하나의 트랜잭션 변경한 결과를 다른 트랜잭션에서 접근할 수 없도록 방지하여 일관성 유지
ROLLBACK : 트랜잭션의 전체 취소
- 트랜잭션내의 모든 SQL 명령문에 의해 변경된 작업 내용을 전부 취소하고 트랜잭션을 종료
- CPU,메모리 같은 해당 트랜잭션에 할당된 자원을 해제, 트랜잭션을 강제 종료
4. SEQUENCE
: 고유 키(Primary key), 순번, 특정한 규칙을 가진 수열 등의 생성을 위한 목적으로, SQL에서 정수로 된 등차수열인 '시퀀스' 객체를 생성해줌
1. SEQUENCE 형식
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[MAXVALUE n | NOMAXVALUE]
[MINVALUE n | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE n | NOCACHE];INCREMENT BY n : 시퀀스 번호의 증가치로 기본은 1, 일반적으로 ?1 사용
START WITH n : 시퀀스 시작번호, 기본값은 1
MAXVALUE n : 생성 가능한 시퀀스의 최대값
MAXVALUE n : 시퀀스 번호를 순환적으로 사용하는 cycle로 지정한 경우, MAXVALUE에 도달한 후 새로 시작하는 시퀀스값
CYCLE | NOCYCLE : MAXVALUE 또는 MINVALUE에 도달한 후 시퀀스의 순환적인 시퀀스 번호의 생성 여부 지정
CACHE n | NOCACHE : 시퀀스 생성 속도 개선을 위해 메모리에 캐쉬하는 시퀀스 개수, 기본값은 20
2. SEQUENCE sample 예시
1) SEQUENCE sample 예시 1
CREATE SEQUENCE sample_seq
INCREMENT BY 1 --옵션을 1씩 증가함
START WITH 10000; --10000무터 시작SELECT sample_seq.nextval FROM dual; --next 다음 Value 값을 갖고 와라
SELECT sample_seq.CURRVAL FROM dual; --CURRVAL: Current Value
-- 여기서 다시 nextval을 다시 실행시키면 옵션 1이 증가됨
2) SEQUENCE sample 예시 2 --> 실 사용 예시
CREATE SEQUENCE dept_dno_seq
INCREMENT BY 1
START WITH 76; --dept_dno_seq 시퀸스를 만들고 76을 시작값으로 1씩 증가시켜라
3) SEQUENCE dept_dno_seq를 이용 dept_second 입력 --> 실 사용 예시
INSERT INTO dept_second
VALUES(dept_dno_seq.NEXTVAL , 'Accounting' , 'NEW YORK');
SELECT dept_dno_seq.CURRVAL FROM dual;
4) SEQUENCE dept_dno_seq를 이용 dept_second 입력 --> 실 사용 예시
INSERT INTO dept_second
VALUES(dept_dno_seq.NEXTVAL , 'Accounting' , 'NEW YORK');
SELECT dept_dno_seq.CURRVAL FROM dual;
문제 2-1) '회계','이대' 삽입
INSERT INTO dept_second
VALUES(dept_dno_seq.NEXTVAL , '회계' , '이대');
SELECT dept_dno_seq.CURRVAL FROM dual;
문제 2-2) '인사팀','당산'
INSERT INTO dept_second
VALUES(dept_dno_seq.NEXTVAL , '인사팀' , '당산');
SELECT dept_dno_seq.CURRVAL FROM dual;
3. MAX 전환 (Sequence와 MAX는 섞어쓰지 말기)
예시 1)
INSERT INTO dept_second
VALUES((SELECT MAX(DEPTNO)+1 FROM dept_second)
, '경영팀'
, '대림'
);
⭐ SEQUENCE와 MAX의 비교
| 항목 | SEQUENCE | MAX |
| 주체 | 객체 | 함수 |
| 사용 | PK 입력 | |
| 발행 | - 객체 생성시 규칙을 이용하여 발행함 - 1씩 증가하며 시작번호를 setting함 |
항상 최대값을 발행함 |
| 중복 | 문제 발생 가능성이 매우 적음 권장↑ |
문제 발생할 가능성이 있음 |
| 공통점 | 혼용금지 | |
4) SEQUENCE 삭제
DROP SEQUENCE SAMPLE_SEQ;5) Data 사전에서 정보 조회
SELECT sequence_name , min_value , max_value , increment_by
FROM user_sequences;5. Table 조작
1.Table 생성
CREATE TABLE address
( id NUMBER(3),
NAME VARCHAR2(50),
addr VARCHAR2(100),
phone VARCHAR2(30),
email VARCHAR2(100)
);
↳ TIP) DB TBL 종류⭐⭐(면접질문!)
| 항목 | Master Table (원장성 테이블) | History Table (이력 테이블) | Transaction Table (거래성 테이블) |
| 정의 | 업무의 핵심 개체, 즉 위에서 말한 주체에 대한 정보들을 담고 있는 테이블 | - 마스터 테이블의 변경된 상태 속성을 보관하는 테이블 | - 행위에 대한 테이블 - 부속성 이력성 테이블 존재 X |
| 특징 | - 부속된 많은 자식 테이블 존재 - 이력성 테이블 또한 부속되어 존재함 |
||
| 예시 | 사원의 발령정보, 개인인사정보 | 발령내역 | 거래내역, 입출력 내역, 근태 기록 |
| 데이터 성격 | - 가장 최신의 상태 유지 - 데이터베이스에서 상태라고하면 속성을 가장 최신의 것으로 유지한다고 생각 - 속성에는 가변 /불변의 데이터들이 있는데 최신의 정보를 유지하기 위해 가변속성들을 관리 => 이를 위해 History Table 존재 |
변경된 상태 정보를 구간으로 구분하는 형태(시작~끝) | 등록만 되는 개념이 없으면 변경 불가능 |
+++ ERP와 ERD
ERP (Enterprise Resource Planning)
- 전사적 자원관리라고 함
- 재무, 인사 관리, 제조, 공급망, 서비스, 조달 등 비즈니스의 여러 부문에서 자동화와 프로세스를 지원해 운영 효율성을 높이도록 돕는 소프트웨어 시스템
ERD (Entity Relationship Diagram)
- 엔터티 관계 다이어그램
- 데이터베이스 내의 요소가 어떻게 관련되어 있는지 보여 주는 데이터베이스의 시각적 표현
- ER 다이어그램은 엔터티와 관계라는 두 가지 개체 유형으로 구성되며, 이 컨텍스트의 엔터티는 캔버스에 도형으로 표시되는 데이터 집합의 데이터 구성 요소임
6. 데이터사전(=Data Dictionary)
7-1. 데이터 사전이란?
- 사용자와 데이터베이스 자원을 효율적으로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블의 집합
- 사전 내용의 수정은 오라클 서버만 가능
- 오라클 서버는 데이타베이스의 구조, 감사, 사용자 권한, 데이터 등의 변경 사항을 반영하기 위해 지속적 수정 및 관리
- 데이타베이스 관리자나 일반 사용자는 읽기 전용 뷰에 의해 데이터 사전의 내용을 조회만 가능
- 실무에서는 테이블, 칼럼, 뷰 등과 같은 정보를 조회하기 위해 사용
7-2. 데이터 사전 관리 정보
- 데이터베이스의 물리적 구조와 객체의 논리적 구조
- 오라클 사용자 이름과 스키마 객체 이름
- 스키마?: 데이터베이스의 구조와 제약조건에 관한 전반적인 명세 를 기술한 것
즉, DB 내에 어떤 구조로 데이터가 저장되는 가를 나타내는 데이터베이스 구조를 의미함
- 스키마?: 데이터베이스의 구조와 제약조건에 관한 전반적인 명세 를 기술한 것
- 사용자에게 부여된 접근 권한과 롤
- 무결성 제약조건에 대한 정보
- 칼럼별로 지정된 기본값
- 스키마 객체에 할당된 공간의 크기와 사용 중인 공간의 크기 정보
- 객체 접근 및 갱신에 대한 감사 정보
- 데이터베이스 이름, 버전, 생성날짜, 시작모드, 인스턴스 이름
7-3. 데이터 사전 종류
1. USER_ : 객체의 소유자만 접근 가능한 데이터 사전 뷰
-- user_tables는 사용자가 소유한 테이블에 대한 정보를 조회할 수 있는 데이터 사전 뷰.
SELECT table_name
FROM user_tables
;SELECT *
FROM user_catalog
;
2. ALL_ : 자기 소유뿐만 아니라 권한을 부여 받은 객체까지 접근 가능한 데이터 사전 뷰
SELECT owner , table_name
FROM all_tables
;
3. DBA_ : 데이터베이스 관리자만 접근 가능한 데이터 사전 뷰
SELECT owner , table_name
FROM dba_tables
;
오늘의 숙제
문1) address스키마/Data 유지하며 addr_second Table 생성
>>내가 한 것
CREATE TABLE addr_second
AS SELECT * FROM ADDRESS
;
>>선생님이 한 것
CREATE TABLE addr_second(id,name,addr,phone,email) -- 괄호안의 내용은 안써도 됨
AS SELECT * FROM ADDRESS
;
문2) address스키마 유지하며 Data 복제 하지 않고 addr_seven Table 생성
>>내가 한 것
CREATE TABLE addr_seven
AS SELECT id , NAME , addr, phone , email
FROM address
WHERE 1=0 --따옴표 안써도 된다고 함
;
>>선생님이 한 것
CREATE TABLE addr_seven(id,name,addr,phone,email)
AS SELECT * FROM address
WHERE '1'='2'
;
문3) address(주소록) 테이블에서 id, name 칼럼만 복사하여 addr_third 테이블을 생성하여라
>>내가 한 것
CREATE TABLE addr_third
AS SELECT id , name
FROM address
;
>>선생님이 한 것
문4) addr_second 테이블 을 addr_tmp로 이름을 변경하시오
>>내가 한 것
RENAME addr_second TO addr_tmp;
>>선생님이 한 것
'DB > Oracle' 카테고리의 다른 글
| 2024_07_03_수~ 07_04_목 (0) | 2024.07.03 |
|---|---|
| 2024_07_02_화 (0) | 2024.07.02 |
| 2024_06_28_금 (0) | 2024.06.28 |
| 2024_06_27_목 (0) | 2024.06.27 |
| 2024_06_26_수 (0) | 2024.06.26 |
