★비만고양이

[ 인덱스 ]
- 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블 내의 원하는 레코드를 빠르게 찾아갈수 있도록 만들어진 데이터 구조

- 자동인덱스 : 프라이머리 키 또는 uinque 제한 규칙에 의해 자동적으로 생성되는 인덱스
- 수동인덱스 : create index 명령을 실행해서 만드는 인덱스

- Index를 생성하는 것이 좋은 Column
① WHERE절이나 join조건 안에서 자주 사용되는 컬럼
② null 값이 많이 포함되어 있는 컬럼
③ WHERE절이나 join조건에서 자주 사용되는 두 개이상의 컬럼

- 다음과 같은 경우에는 index 생성이 불필요할때
① table이 작을 때
③ 테이블이 자주 갱신될 때

* 오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반
   B-tree인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과

 이 알고리즘 원리는

 ① 주어진 값을 리스트의 중간점에 있는 값과 비교합, 만약 그 값이 더 크면 리스트의 아래쪽 반을 버림, 만약 그 값이 더 작다면 위쪽 반을 버림
 ② 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도 반복

* 인덱스는 B-tree 구조를 가지며 크게 다음 네 가지로 분류

① Bitmap 인덱스
  각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 가장 잘 작동
  그러므로 비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상
  테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용
 예_) SQL>CREATE BITMAP INDEX emp_deptno_indx
        ON emp(deptno);

② Unique 인덱스
  인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점
  프라이머리키 와 Unique 제약 조건시 생성되는 인덱스는 Unique 인덱스
 예_) SQL>CREATE UNIQUE INDEX emp_ename_indx
        ON  emp(ename);

③ Non-Unique 인덱스
   Non-Unique 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질수 있음
 예_) SQL>CREATE INDEX  dept_dname_indx
        ON  dept(dname);

④ 결합 (Concatenated(=Composite)) 인덱스
   복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질수 있는 최대 컬럼값은 16개
 예_) SQL>CREATE UNIQUE INDEX emp_empno_ename_indx
        ON  emp(empno, ename);

* 인덱스의 삭제 
- 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않음
- 인덱스를 삭제하려면 INDEX의 소유자이거나 DROP ANY INDEX권한을 가지고 있어야 함
- INDEX는 ALTER를 할 수 없음
 예_) DROP INDEX emp_empno_ename_indx ;

* 인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색
 예_) SELECT index_name , index_type
        FROM USER_INDEXES
        WHERE table_name=’EMP’;

[ 뷰 ]
- 하나의 가상 테이블
- 실제 데이터가 저장 되는 것은 아니지만 뷰를 통해 데이터를 관리
- 복잡한query를 통해 얻을 수 있는 결과를 간단한 query를 써서 구할 수 있음
- 한개의 뷰로 여러 테이블에 대한 데이터를 검색
- 특정 평가기준에 따른 사용자 별로 다른 데이터를 액세스할 수 있도록 함

* 뷰의 제한 조건
- 테이블에 NOT NULL로만든 컬럼들이. 뷰에 다 포함이 되 있어야 함
- ROWID, ROWNUM, NEXTVAL, CURRVAL등과 같은 가상컬럼에 대한 참조를 포함하고 있는 뷰에는 어떤 데이터도 Insert할 수 없음
- WITH READ ONLY 옵션을 설정한 뷰도 데이터를 갱신할수 없음
- WITH CHECK OPTION을 설정한 뷰는 뷰의 조건에 해당되는 데이터만 삽입, 삭제, 수정

- FORCE : 기본 테이블 유무에 관계없이 VIEW를 생성
- WITH CHECK OPTION : VIEW에 의해 엑세스될 수 있는 행만이 입력되거나 변경될 수 있음을 지정
- WITH READ ONLY : SELECT만 가능한 VIEW의 생성
- VIEW를 정의하는 질의어에는 ORDER BY절을 사용 할 수 없음
- 함수를 사용한 컬럼은 반드시 ALIAS를 지정

* VIEW에 대한 DML문 사용 규칙
 VIEW가 다음 사항을 포함하는 경우 행의 삭제 불가
 - JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령

 VIEW가 다음 사항을 포함하는 경우 데이터 수정 불가
 - JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령, 식으로 정의된 컬럼(EX SAL * 0.1)

 VIEW가 다음 사항을 포함하는 경우 데이터 추가 불가
 - JOIN조건, 그룹함수, GROUP BY절, DISTINCT명령, 식으로 정의된 컬럼, VIEW에 선택되지 않는 NOT NULL컬럼
 
 

* 뷰의 정보 조회
USER_VIEWS 데이터 사전을 통해서 뷰에 대한 정보를 조회
예_) SELECT view_name , text
       FROM  USER_VIEWS;

* 뷰의 삭제
예_) DROP VIEW Read_Only;
       view dropped.
 

[ 시퀀스 ]
- 유일(UNIQUE)한 값을 생성해주는 오라클 객체
- 시퀀스를 생성하면 기본키와 같이 순차적으로 증가하는 컬럼을 자동적으로 생성
- 보통 primary key 값을 생성하기 위해 사용
- 메모리에 Cache되었을 때 Sequence 값의 액세스 효율이 증가
- Sequence는 테이블과는 독립적으로 저장되고 생성, 따라서 하나의 sequence를 여러 테이블에서 쓸 수 있음

* 시퀀스 생성
- START WITH : 시퀀스의 시작 값을 지정합니다. n을 1로 지정하면 1부터 순차적으로 시퀀스번호가 증가
- INCREMENT BY : 시퀀스의 증가 값을 말합니다. n을 2로 하면 2씩 증가
                           START WITH를 1로 하고 INCREMENT BY를 2으로 하면 1, 3, 5,7,.. 이렇게 퀀스  번호가 증가
- MAXVALUE n | NOMAXVALUE : MAXVALUE는 시퀀스가 증가할수 있는 최대값
       NOMAXVALUE는 시퀀스의 값을 무한대로 지정합니다.
- MINVALUE n | NOMINVALUE : MINVALUE는 시퀀스의 최소값을 지정
     기본값은 1이며, NOMINVALUE를 지정할 경우 최소값은 무한대

* 사용규칙
NEXTVAL, CURRVAL을 사용할 수 있는 경우
 - subquery가 아닌 select문
 - insert문의 select절
 - insert문의 value절
 - update문의 set절
 
 NEXTVAL, CURRVAL을 사용할 수 없는 경우
 - view의 select절
 - distinct 키워드가 있는 select문
 - group by, having, order by절이 있는 select문
 - select, delete, update의 subquery
 - create table, alter table 명령의 default값

* 시퀀스의 수정 및 삭제
- START WITH는 수정할수 없음, START WITH 절이 없다는 점을 빼고는 CREATE SEQUENCE와 같다
예_) ALTER SEQUENCE emp_seq
       INCREMENT BY 2
       CYCLE;

[ SYNONYM 시노님 ]
- 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(Alias)
- Synonym은 실질적으로 그 자체가 Object가 아니라 Object에 대한 직접적인 참조

- 시노님을 사용하는 이유
① 데이터베이스의 투명성을 제공하기 위해서 사용 한다고 생각, 시노님은 다른 유저의 객체를 참조할 때 많이 사용
② 만약에 실무에서 다른 유저의 객체를 참조할 경우가 있을 때 시노님을 생성해서 사용을 하면은
    추후에 참조하고 있는 오프젝트가 이름을 바꾸거나 이동할 경우 객체를 사용하는 SQL문을 모두
    다시 고치는 것이 아니라 시노님만 다시 정의하면 되기 때문에 매우 편리
③ 객체의 긴 이름을 사용하기 편한 짧은 이름으로 해서  SQL코딩을 단순화
④ 또한 객체를 참조하는 사용자의 오브젝트를 감추 수 있기 때문에 이에 대한 보안을 유지 
    시노님을 사용하는 유저는 참조하고 있는 객체를에 대한 사용자의 object의 소유자,
    이름, 서버이름을  모르고 시노님 이름만 알아도 사용

* Synonyms을 사용하는 경우
- 오브젝트의 실제 이름과 소유자 그리고 위치를 감춤으로써 database 보안을 개선하는데 사용
- Object에의 Public Access를 제공
- Remote Database의 Table, View, Program Unit를 위해 투명성을 제공
- Database 사용자를 위해 SQL 문을 단순화

* 시노님에는 두가지 종류
① Private Synonym  : 전용 시노님은 특정 사용자만  이용할 수 있음
② Public Synonym : 공용 시노님은 공용 사용자 그룹이 소유하며 그 Database에 있는 모든 사용자가 공유

* 시노님 생성 문법(Syntax)
- PUBLIC : 모든 사용자가 접근 가능한 시노님을 생성, PUBLIC  시노님의 생성 및 삭제는 DBA만이 할 수 있음

* scott USER의 emp테이블을 test USER가 사용 하는 예제.
 1. 먼저 scott/tiger USER로 접속해서 test USER에게 emp테이블을 조작할 권한을 부여합니다.
  예_) GRANT ALL ON  emp TO  test;
        권한이 부여되었습니다.

         test user에 대하여 scott의 emp테이블을 조작할 수 있는 권한을 부여합니다.
         권한이 있어야 select하거나 update, insert할수 있습니다.

 2. test USER로 접속해 동의어를 생성합니다.
 예_) connect test/test
 예_) CREATE SYNONYM  scott_emp FOR  scott.emp ;
        시노님이 생성되었습니다.

          scott USER가 소유하고 있는 emp 테이블에 대해 scott_emp라는 일반시노님을 생성했습니다.
          scott 사용자의 emp테이블을 test 사용자가 scott_emp라는 동의어로 사용 합니다. .

* 시노님을 이용한 쿼리
예_) SELECT empno,  ename FROM  scott_emp;

* 일반 테이블을 쿼리
예_) SELECT empno,  ename FROM  scott.emp;
       이 두 쿼리의 결과는 같습니다.

* 동의어 삭제
예_) DROP SYNONYM   scott_emp;
       시노님이 삭제되었습니다.

예_) SELECT empno,  ename FROM  scott_emp;
       라인 1 에 오류:
       ORA-00942: 테이블 또는 뷰가 존재하지 않습니다

[ 서브쿼리 ]
- Subquery는 다른 하나의 SQL 문장의 절에 NESTEDED된 select문장
- select, update, delete, insert와 같은 dml문과 created table 또는 view에서 이용 될 수 있음
- 알려지지 않은 조건에 근거한 값들을 검색하는 select문장을 작성하는데 유용
- subquery는 main query가 실행되기 이전에 한번 실행

[ Guidelines ]
- 서브쿼리는 괄호로 묶어야 함
- 두 종류의 비교 연산자들이 서브쿼리에 사용
 (단일 행 연산자  =, >, >=, <, <=, <>, !=)
 (복수 행 연산자 IN, NOT IN, ANY, ALL, EXISTS)
- 서브쿼리는 연산자의 오른쪽에 나타나야 함

[ SUBQUERY의 유형 ]
- 단일 행(Sing-Row) 서브쿼리 : SELECT문장으로부터 오직 하나의 행만을 검색하는 질의
- 다중 행(Multiple-Row) 서브쿼리 : SELECT문장으로부터 하나 이상의 행을 검색하는 질의
- 다중 열(Multiple-Column) 서브쿼리 : SELECT문장으로부터 하나 이상의 컬럼을 검색하는 질의
- FROM절상의 서브쿼리(INLINE VIEW) : FROM절상에 오는 서브쿼리로 VIEW처럼 작용
- 상관관계 서브 쿼리 : 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식

[ 단일 행(Sing-Row) 서브쿼리 ]
- 오직 한개의 행(값)을 반환 (Return only one row)
- 단일 행 연산자 ( =, >, >=, <, <=, <>, !=)만 사용 할 수 있음

예문_) SELECT ename,job
          FROM emp
          WHERE job = (SELECT job
                              FROM emp
                              WHERE empno = 7369);           // empno가 7369인 job을 먼저 검색하고, job이 CLERK인 사원의 이름과 직업을 반환...

select DATA_01, DATA_03
from TESTTABLE02
where DATA_01 = (select DATA_01
                from TESTTABLE02
                where DATA_03 = 10);

[ 다중 행(Multiple-Row) 서브쿼리 ]
- 하나 이상의 행을 return하는 subquery를 다중 행 subquery라고 함
- 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)를 사용 할 수 있음

- IN 연산자
 예_) SELECT empno,ename,sal,deptno
        FROM emp
        WHERE sal IN (SELECT MAX(sal)
          FROM emp
   GROUP BY deptno);

select DATA_01,  DATA_02, DATA_03, DATA_04
from TESTTABLE02
where DATA_03 in (select max(DATA_03)
                  from TESTTABLE02
                  group by DATA_01);

- ANY 연산자 : 서브쿼리의 결과값 중 어느 하나의 값이라도 만족이 되면 결과값을 반환
 예_) SELECT ename, sal
        FROM emp
        WHERE deptno != 20 AND sal > ANY(SELECT sal FROM emp WHERE job=’SALESMAN’); 

select DATA_01, DATA_03
from TESTTABLE02
where DATA_04 <> 16
 and DATA_03 > any (select DATA_03
      from TESTTABLE02
      where DATA_01='test01');

- ALL 연산자 : 서브쿼리의 결과값 중 모든 결과 값이 만족 되야만 결과값을 반환
 예_) SELECT ename, sal
        FROM emp
        WHERE deptno != 20 AND sal > ALL(SELECT sal FROM emp WHERE job=’SALESMAN’);

select DATA_01, DATA_03
from TESTTABLE02
where DATA_04 <> 16

 and DATA_03 > all ( select DATA_03
    from TESTTABLE02
    where DATA_01='test01');

- EXISTS 연산자 : 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재나는 값들만을 결과로 반환
- 서브쿼리에서 적어도 1개의 행을 리턴하면 논리식은 참이고 그렇지 않으면 거짓
 예_) SELECT empno, ename, sal
        FROM emp e
        WHERE EXISTS (SELECT empno FROM emp WHERE e.empno = mgr)

select DATA_01, DATA_02, DATA_03
from TESTTABLE02 a
where exists (select DATA_01
                    from TESTTABLE02
       where a.DATA_01 = 'test01');

[ 다중 열(Multiple-Column) 서브쿼리 ]
- 서브쿼리의 결과값이 두개 이상의 컬럼을 반환하는 서브쿼리

- Pairwise(쌍비교) Subquery : 서브쿼리가 한번 실행되면서 모든 조건을 검색해서 주 쿼리로 넘겨 줌

select DATA_01, DATA_03, DATA_04
from TESTTABLE02
where (DATA_03, DATA_04) in (select DATA_03, DATA_04
                         from TESTTABLE02
                         where DATA_04 = 19 and 0 is not null );

- Nonpairwise(비쌍비교) Subquery : 서브쿼리가 여러 조건별로 사용 되어서 결과값을 주 쿼리로 넘겨 줌

select DATA_01, DATA_03, DATA_04
from TESTTABLE02
where DATA_03 in ( select DATA_03
                   from TESTTABLE02
                   where DATA_04 = 23 );
                   AND DATA_04 in ( select DATA_04
                                    from TESTTABLE02
                                    where DATA_04 = 14 in not null);

- Null Balues in a Subquery : 서브쿼리에서 null값이 변환되면 주 쿼리 에서는 어떠한 행도 반환되지 않음

[ RROM절상의 서브쿼리(INLINE VIEW) ]
- 서브쿼리는 FROM절에서도 사용이 가능
- from절상에 오는 서브 쿼리로 view처럼 작용

예_) SELECT b.empno,b.ename,b.job,b.sal, b.deptno
       FROM  (SELECT empno
                  FROM emp 
                  WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno = 20)) a, emp b
       WHERE a.empno = b.empno
                    AND b.mgr is NOT NULL
                    AND b.deptno != 20

[ 상관관계 서브쿼리 ]
- 바깥쪽 쿼리의 컬럼 중의 하나라 안쪽 서브쿼리의 조건에 이용되는 처리 방식
- 이는 주 쿼리에서 서브 쿼리를 참조하고 이 값을 다시 주 쿼리로 반환한다는 것

예_) SELECT empno, ename, sal
       FROM emp e
       WHERE sal > (SELECT AVG(sal) sal FROM emp WHERE e.empno = mgr)

[ 집합쿼리(UNION, INTERSECT,MINUS) ]
- 집합 연산자를 사용시 집합을 구성할 컬러의 데이터 타입이 동일 해야 함
 UNION : 합집합
 UNION ALL : 공통원소 두번씩 다 포함한 합집합
 INTERSECT : 교집합
 MINUS : 차집합

- UNION : 두 테이블의 겹합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환

예_) SELECT deptno FROM emp
       UNION
       SELECT deptno FROM dept;

- UNION ALL : UNION과 같으나 두 테이블의 중복되는 값까지 반환

예_) SELECT deptno FROM emp
       UNION ALL
       SELECT deptno FROM dept;

- INTERSECT : 두행의 집합중 공통된 행을 반환
예_) SELECT deptno FROM emp
       INTERSECT
       SELECT deptno FROM dept;

- MINUS : 첫번째 select문에 의해 반환되는 행중에서 두번째 select문의 의해 반환되는 행에 존재하는 않는 핸들을 반환
예_) SELECT deptno FROM dept
       MINUS
       SELECT deptno FROM emp;