ORDB 객체
오라클 데이터베이스는 데이터 보관및 관리를 위한 여러기능과
저장공간을 객체를 통해 제공한다
- 테이블
- 더빠른 검색을 위한 인덱스
- 테이블처럼 사용하는 뷰
- 규칙에 따라 순번을 생성하는 시퀀스
- 공식 별칭을 지정하는 동의어
인덱스
테이블에 있는 데이터를 빠르게 찾기위한 객체
테이블과는 다른 메모리에 저장된다
데이터베이스는 데이터양이 많아질수록(JOIN이나 서브 쿼리 사용 시 곱 연산이 일어나 데이터 양이 증가)
데이터를 검색할때 속도가 오래 걸린다 이런경우 인덱스를 활용하면 효과적인데
간단하게 설명하면 테이블과는 별개로 데이터와 그 데이터가 저장된 주소(RowID)를
인덱스기능을 통해 1:1로 연결해서 쉽게 찾을수 있게 하는것이다
인덱스 없이 데이터를 찾으면 큰 카테고리에서 부터 찾으려고 하는 데이터까지의
연결다리를 하나씩 건너가면서 찾는다면 인덱스는 연결다리를 직접 놓아주는 것이다
그렇다고해서 연결다리를 다 하나씩 지어주면 인덱스 자체도 메모리를 사용하기 때문에
오히려 효율이 떨어질 수도 있다
- 계정의 인덱스를 찾는 명령어
SELECT * FROM USER_INDEXES;- 인덱스 컬럼을 찾는 명령어
SELECT * FROM USER_IND_COLUMNS;- EMP 테이블의 SAL 열에 인덱스를 생성하기
CREATE INDEX IDX_EMP_SAL ON EMP(SAL);
/*테이블명(컬럼명) 형삭의 문법이 종종 나오고있다*/인덱스를 삭제하기
DROP INDEX IDX_EMP_SAL;더 자세한 정보를 알고싶으면 SQL TUNNING 참고
뷰
마치 테이블인것처럼 사용하는 객체
하나이상의 테이블을 조회하는 SELECT 문을 저장한 객체
SELECT문을 저장하기 때문에 물리적 데이터를 따로 저장하지는 않는다
보안성을 보장해준다
- 부서번호가 20번인 사원의 사원번호 이름 직책 부서번호를 출력 (뷰 사용할것)
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO = 20;
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM (SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE DEPTNO = 20);
/*첫번째 SQL은 MAIN QUERY에 WHERE까지 사용했고
두번째 SQL은 MAIN QUERY에 WHERE을 사용 안하고 같은 결과를 출력했다*/
SELECT * FROM VM_EMP20; /*만들어져있는 테이블이 아니다 즉 뷰로 만드라는 뜻*/
CREATE VIEW VM_EMP20
AS (SELECT EMPNO, ENAME, JOB, DEPTNO, FROM EMP
WHERE DEPTNO = 20);
/*처음 만들려고 하니 권한이없다고 에러가 나왔다
시스템계정에서 SCOTT 계정에 VIEW생성 권한을 먼저 주어야한다*/
GRANT CREAT VIEW TO SCOTT;
/*권한이 생기면 CREATE VIEW 가 가능해진다*/
- 뷰를 확인하는 방법
SELECT * FROM USER_VIEWS;
SELECT * FROM VM_EMP20; /*가상 테이블이기 때문에 테이블처럼 사용할수 있다*/- USER_VIEWS -> 뷰를 만들면 USER_VIEWS 테이블에 저장된다
SELECT VIEW_NAME, TEXT_LENGTH, TEXT FROM USER_VIEWS;
DROP VIEW VM_EMP20; /*뷰삭제*/- 새로 만들 뷰는 CREATE VIEW 하면 되지만
만약 같은이름의 뷰가 존재해서 에러가 난다면
CREATE OR REPLACE VIEW 하면 기존에 있던 뷰에 덮어쓰기 형식으로 수정할수 있다
ROWNUM
SELECT ROWNUM, E.* FROM EMP E;CREATE 문을 통해 객체로 만들어지는 뷰 외에도
SQL에서 일회성으로 만들어 사용하는 인라인 뷰이다
인라인 뷰와 ROWNUM을 사용하면
ORDER BY 절을 통해 정렬된 결과중 최상위 몇개의 데이터만 출력이 가능
SELECT ROWNUM, E.* FROM EMP E
ORDER BY SAL DESC;
SELECT ROWNUM, E.* FROM (SELECT * FROM EMP /*인라인 뷰*/
ORDER BY SAL DESC) E;
첫번째 SQL은 ROWNUM이 ORDER BY에 의해 뒤죽박죽이 됬다
두번째 SQL은 ROWNUM이 ORDER BY의 영향을 받지 않아서 숫자가 순서대로 출력된다
두번째 SQL문을 TOP N-SQL문이라고 한다(Top N 쿼리 : 상위 n개의 데이터를 추출하는 쿼리)
시퀀스
ORDB에서 특정 규칙에 맞는 연속 숫자를 생성하는 객체
유일한 값을 생성해준다(테이블 밖에 독립적으로 저장되고 생성된다)
기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성 할 수 있다
보통 PRIMARY KEY 값을 생성하기 위해 사용 한다
CREATE TABLE DEPT_SEQUENCE
AS SELECT * FROM DEPT WHERE 1<>1;
CREATE SEQUENCE SEQ_DEPT_SEQUENCE
/*이름이 길어지는건 가독성을 위해서 어떤관계가있는지 전부 명시하는 것이다*/
START WITH 10 /*시퀀스에서 생성할 번호의 시작값(DEFAULT = 1)*/
INCREMENT BY 10 /*시퀀스에서 생성할 번호의 증가값(DEFAULT = 1)*/
MAXVALUE 90 /*최대값*/
MINVALUE 0 /*최소값*/
NOCYCLE /*MAX값에 도달했을때 CYCLE중이면 START WITH 에서 다시 시작*/
/*NOCYCLE면 번호생성이 중단되고, 추가번호 요청시 에러발생*/
NOCACHE; /*시퀀스가 생성할 번호를 메모리에 미리 할당해 놓은 수*/
/*NOCACHE는 미리 생성하지 않도록 설정*/
/*생략시 디폴트 20(선택가능)*/
- 만든 시퀀스를 이용해 값을 넣어보자
INSERT INTO DEPT_SEQUENCE(DEPTNO, DNAME, LOC)
VALUES(SEQ_DEPT_SEQUENCE.NEXTVAL, 'DATABASE', 'SEOUL') /*.NEXTVAL은 지금값의 다음값을 가져온다는뜻*/
/*맨처음 값을 INSERT하면 START WITH 이 10이므로 10의 값이 들어가고
INCREMENT BY가 10이므로 그 다음부터는 20, 30 순으로 들어가게 된다*/
SELECT * FROM DEPT_SEQUENCE시퀀스이름.CURRVAL / 시퀀스이름.NEXTVAL 사용
CURRVAL은 시퀀스에서 마지막으로 생성한 번호를 반환
(시퀀스 생성후 바로 CURRVAL을 사용하면 아직 만들어진 번호가 없기때문에 에러발생
INSERT를 한번 하고난 다음부터 CURRVAL사용 가능)
NEXTVAL은 다음 번호를 반환
- 가장 마지막으로 생성된 시퀀스 확인
SELECT SEQ_DEPT_SEQUENCE.CURRVAL FROM DEPT_SEQUENCE;- 값을 계속 넣다보면 맥스VALUE에 도달하는데 도달시 번호생성이 중단되고 추가 생성이 불가능해진다
- 시퀀스의 속성을 ALTER를 이용해 재수정이 가능하다
ALTER SEQUENCE SEQ_DEPT_SECUENCE
INCREMENT BY 3
MAXVALUE 999
CYCLE;- 시퀀스 조회하기
SELECT * FROM USER_SEQUENCES; /*USER_SECUENCES(테이블) 데이터사전 검색명령어
(데이터 사전검색 명령어는 S가붙는 규칙이있다)*/- 시퀀스 제거하기
DROP SEQUENCE SEQ_DEPT_SEQUENCE;DB관련 작업은 꾸준함이 필요하다
DDL DML DCL 의 QUERY문을 자유자재로 사용할줄 알아야하고
원하는 기능을 구현하기 위한 SQL을 최적화시켜서 작성할 수 있는 능력과
정확한 정보를 바탕으로 핵심 개념들을 이해하고 활용할 수 있어야 한다
Transaction(거래, 처리)
하나의 그룹으로 처리되어야 하는 명령문들을 모아 놓은 논리적인 작업 단위
데이터베이스 응용 프로그램은 트랜잭션들의 집합으로 정의 할 수 있다
여러 단계의 처리를 하나의 처리처럼 다루는 기능
여러 개의 명령어의 집합이 정상적으로 처리되면 정상 종료
하나의 명령어라도 잘못되면 전체 취소(ALL OR NOTHING)
트랜잭션을 쓰는 이유는 데이터의 일관성을 유지하면서 안정적으로 데이터를 복구하기 위함
데이터베이스를 이용해 거래를 한다고 생각해보자
내 계좌의 돈을 다른 계좌로 보낼경우 DB에서 어떻게 처리하는지 보면
1. 내계좌에서 송금한만큼 돈을 뺀다
2. 상대방계좌에서 입금된 만큼 돈을 더한다
단순하게 처리과정만 보자면 가장핵심적인 데이터는 내계좌의 돈을 차감하고
상대방계좌의 돈을 증감시키는 이과정이다
(계좌 비밀번호가 TRUE인지 계좌번호가 맞는지 은행이 맞는지 등등 많은 조건이 생략되있다)
만약 내가 송금한 A은행은 문제가 없었는데 상대방의 B은행에서 DB가 오류가나서 돈을 입금하는
SQL문을 실행하지 못했더니 내계좌에선 돈이 차감됬고 상대방의 계좌에선 돈이 그대로 유지되는
그런 불상사가 생길수 있다
따라서 그런 불상사를 미연에 방지하기위해 모든 SQL명령을 하나의 그룹으로 묶어서
어느 하나의 문제라도 발생한경우 모든 명령을 취소시켜버려서 방금과같은 문제가
발생하더라도 상대가 입금받지 못했으므로 송금자체도 취소가 되는 좋은 기능이다
일단 트랜잭션이라는게 있다는것만 알아두자
