creative_KIM

select * from A
 union (all)

select * from B

=> A와B의 테이블의 해당하는 컬럼들을 연결하여 보여줍니다.

OR과 유사하다고 생각하면 되고 실제로 OR을 사용하는 쿼리를 union all 으로 대체시 수행속도를

향상할 수 있습니다.

union은 중복된 데이터를 제거하며 union all 은 중복된 데이터를 모두 보여줍니다.

(distinct를 사용하는 것보다는 union만 쓰는것이 더 효율적이라 생각됩니다. )

오라클에서만 제공하는 기능.

ex) 생산 공정에 소모되는 부품들을 조립 단계에 맞춰 각 단계별로 '독립적인 테이블들'로 구성하는 것이 아니라,

          '한개의 테이블'에 모든 부품 관련 정보들을 입력한 후, 조회시 '내부적으로 정의한 순서' 에 의해 읽어올 수 있는

           방법을 말한다. 즉, 순환 관계란 부모와 자식의 관계처럼 계층적 구조의 자료를 하나의ENTITY 내에 구조화

           하여 기술한 것이다.

            일반적으로 계층적 구조의 자료 형태의 단점은 (독립적인 테이블) 하나의 레벨에 대한 데이터 변경 시, 부모의

            식별자가 바뀜으로서 자식의 식별자도 모두 바뀌어야 한다는 것이다. 물론 이것은 해당 테이블의 컬럼에

             REVERSE 값을 주어 보다 효과적으로 사용할 수 있지만 모든 레벨에 해당하는 값들을 단일 테이블에 저장하는

             방식(한개의 테이블)을 사용하였을 경우 DB BUFFER 및 디스크 용량 그리고 DIST I/O와 MEMORY I/O등

             시스템의 전반적인 모든 튜닝 관련 사항들에 대해 보다 효과적으로 대처 할 수 있다.

            하지만 이런 순환관계 모델이 현업에서 사용되는 것은 드물다. 이유는 해당 모델에 대해 이해가 부족하여

             사용하기 어렵고, 그리하여 처리 속도가 느려지기 때문이다.

             이러한 순환관계로 테이블을 설계한다면 부모 및 자식 관계에 있는 모든 로우들이 단일 테이블내에 모두

            저장이 된다. 그리고 이러한 순환관계 테이블을 처리하기 위해서는 CONNECT BY ... START WITH ...

             라는 구문을 사용하여 처리할 수 있어야 한다. 이러한 기능을 제공하지 않는 데이터베이스의 경우 직접 순환 구조로

             데이터를 추출 할 수 있게 매우 복잡한 단계의 프로그래밍을 구현하여야 한다. 그리고 이런 문장은 일반 SQL 과는

             다르게 구성되는 만큼 처리 구조도 독특한 특성을 가지고 있다.

            where sal>500                                                          - CHECK 조건

            connect by prior empno = 'mgr'             -  JOIN 조건

            start with mgr is null                                          - DRIVING 조건 

            순환 구조 SQL을 작성하고 실행 할 때 중요한 부분은 일반SQL과는 달리 where 절ㅇㅣ 처리 범위를 줄여주는

            선행 조건이 아니라는 것이다.  순환 전개 방식의 선행 처리 조건은 START WITH 절로 순환 구조 처리의

            가장 중요한 부분이다. where 절은 단지 체크 조건으로 결과값에 대해 단순한 필터로만 작용을 한다.

             이리하여 순환 구조의 SQL의 튜닝을 위해서는  START WITH절에 사용되는 컬럼에 대해 인덱스를 생성해야

             한다. 만약 STRAT WITH의 처리 범위가 넓다면 이를 해당 쿼리 내의 where 절 혹은 START WITH 의

             해당 컬럼의 비교값에서 INLINE VIEW 를 사용해서 범위를 줄여줘야 한다.

              그 다음으로 중요한 부분은 PRIOR 절이다. 오라클 매뉴얼을 보거나 모든 책을 보더라도 CONNECT BY PRIOR a = b

              라고 기술되어 있다. 마치 항상 저렇게 사용해야만 하는 것 처럼 느낄 수도 있다. 하지만 PRIOR절은 반대편에

             기술해도 되고 select 절에 기술해도 된다. PRIOR의 의미는 실제로는 최초 시작한 노드의 PRIOR절에 기술되는

              컬럼(a) 값을 읽어 PIROR절 내 = 의 반대편 컬럼(b)에 상수를 제공하겠다는 의미이다. 그렇다면 PRIOR로

               읽은 컬럼 값을 제공받는 반대편 컬럼에도 튜닝을 위해서는 인덱스가 존재해야만 빠른 성능을 보장받을 수 있다

              는 것이다.

               - START WITH

                   select 구문의 strat with 절은 계층 구조가 어떤 행으로부터 시작하는지 지정하는 절이다.

                   일종의 where절이라고 생각.

                    START WITH <조건>

                   ex) START WITH c.cate_name = '컴퓨터'

                          => 카테고리 이름이  '컴퓨터'인 레코드부터 계층적 쿼리를 수행하라는 의미이다.

                              ※ 가능하면 명료하고 구체적인 결과 레코드가 적은 조건을 사용하여야 쿼리 퍼포먼스가 향상된다.

               - CONNECT BY

                     각각의 행들이 어떻게 연결되어야 하는지 정보를 작성한다.(계층적 구조에거 각 행의 연결관계

                                                                                                                                                                     를 설정하는 것)

               - PRIOR

                      컬럼의 상/하 위 의 계층정보를 설정하는 키워드

         ex)     CONNECT BY c.no = c.base_cate_no;

                       => no 컬럼과 base_cate_no이 계층적 연결 관계에 있다라고 설정하는것

                              (self join이라 생각해도 좋음)

                        CONNECT BY PRIOR c.no = c.base_cate_no;

                       => outer join을 생각해보라.

                             c.no(+) = c.base_cate_no; 와 같이 prior 키워드의 위치에 집중하자.

                             c.no 쪽에 위치하고 있는데 이것은 이렇게 설명 할 수 있다.

                            " no 컬럼을 참조하는 base_cate_no컬럼이 속한 레코드를 모두 찾아라."

                        이렇게 쿼리를 작성하였기 때문에 상위 ' 컴퓨터' 카테고리에 해당하는 하위 카테고리를 찾았고,

                        또 그 하위 카테고리의 하위 카테고리를 찾을 수 있게 된다.

◈ START WITH
 
 - 계층 질의의 루트(부모행)로 사용될 행을 지정 합니다..
 - 서브쿼리를 사용할 수도 있습니다.
 
 
◈ CONNECT BY
 
 - 이 절을 이용하여 계층 질의에서 상위계층(부모행)과 하위계층(자식행)의 관계를 규정 합니다.
 - 보통 PRIOR 연산자를 많이 사용 합니다..
 - 서브쿼리를 사용할 수 없습니다..
 - PRIOR
   PRIOR 이 붙는 column 이 가져온 row 의 column을 의미한다. 즉 상위에 존재할 데이타가 되

   게 된다. 어느쪽에 붙느냐 잘 따져 본다.
  
 
◈ CONNECT BY의 실행순서는 다음과 같습니다.
 
 - 첫째 START WITH절
 - 둘째 CONNECT BY 절
 - 세째 WHERE 절 순서로 풀리게 되어있습니다.

◈ SYNTEX
  
   SELECT
   FROM
   START WITH
   CONNECT BY PRIOR
   AND
   ORDER SIBLINGS BY

   or

   SELECT
   FROM
   WHERE
   START WITH
   CONNECT BY PRIOR
   ORDER SIBLINGS BY
  
◈ 이용
   1) 쇼핑목 카테고리 관계 - 대분류, 중분류, 소분류 등을 트리 구조로
   2) 게시판 에서 일반글 과 답글과의 관계 등을 트리 구조로    
  
◈ 데이터가 많아질 경우....
 
 - 첫째로 풀리는 START WITH job='PRESIDENT' job 컬럼에 index가 생성되어 있지 않는다면
    속도를 보장할 수 없습니다.
 
 - 그리고 둘째로 풀리는 CONNECT BY PRIOR empno = mgr 역시 PRIOR 쪽의 컬럼값이 상수가
   되기 때문에 MGR컬럼에 index를 생성하여야 CONNECT BY의 속도를 보장할 수 있습니다.
  
 - 계층구조를 CONNECT BY, START WITH로 풀면 부분범위 처리가 불가능하고 Desc으로
   표현하기가 어렵 습니다.
  

--------------- 설명
-- 아래 예제 1
1) job 이 president 인 row 을 가져온다.
2) 가져온 row 에서 prior 이 붙은 comumn 의 데이타를 가져온다. 여긴선 empno 다.
3) PRIOR empno = mgr  empno 을 mgr 로 사용하는 row 을 가져온다. 기존의 row 상위, 비교해서 가져온 row 하위에 있게 된다.
4) 이제 두번째로 가져온 row 에서 PRIOR empno = mgr 을 실행시킨다.
5) 이런 과정이 연속으로 반복되면서 최종적으로 가져온 data 는 트리 구조를 이루게 된다.(계층구조)
6) LEVEL 은 depth 을 의미한다.

7) empno = PRIOR mgr 한다면 가져온 row 의 mgr을 기준으로 비교하여 data을 가져오게 된다.
   예제 2 참조

 
---- 예제 1
SELECT                  LEVEL,empno,ename, mgr, job  --  LEVEL 은 depth 을 의미한다.
FROM                     emp
START WITH             job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층

-- level 을 공백으로 찍어 본다.

SELECT                  LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job 
FROM                     emp
START WITH             job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr    -- 사원(empno)과 관리자(mgr)의  관계를 계층

-- 결과치
ENAME         LEVEL EMPNO MGR     JOB
KING             1 7839      PRESIDENT
    JONES         2 7566 7839 MANAGER
        SCOTT     3 7788 7566 ANALYST
            ADAMS 4 7876 7788 CLERK
        FORD     3 7902 7566 ANALYST
            SMITH 4 7369 7902 CLERK
        JJS         3 9000 7566 ANALIST
    BLAKE         2 7698 7839 MANAGER
        ALLEN     3 7499 7698 SALESMAN
        WARD     3 7521 7698 SALESMAN
        MARTIN     3 7654 7698 SALESMAN
        TURNER     3 7844 7698 SALESMAN
        JAMES     3 7900 7698 CLERK
    CLARK         2 7782 7839 MANAGER
        MILLER     3 7934 7782 CLERK

----예제 2 - PRIOR 위치 변경
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job  -- level 을 공백으로 찍어 본다.
FROM             emp
START WITH       job = 'CLERK'   -- 직업이 CLERK를 기준으로
CONNECT BY empno = PRIOR mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층   

-- 결과치
ENAME             LEVEL EMPNO MGR     JOB
SMITH             1     7369 7902 CLERK
    FORD         2     7902 7566 ANALYST
        JONES     3     7566 7839 MANAGER
            KING 4     7839      PRESIDENT
ADAMS             1     7876 7788 CLERK
    SCOTT         2     7788 7566 ANALYST
        JONES     3     7566 7839 MANAGER
            KING 4     7839      PRESIDENT
JAMES             1     7900 7698 CLERK
    BLAKE         2     7698 7839 MANAGER
        KING     3     7839      PRESIDENT
MILLER             1     7934 7782 CLERK
    CLARK         2     7782 7839 MANAGER
        KING     3     7839      PRESIDENT

---- 예제 3: 조건 절 사용
-- 1) WHERE  절 사용    
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job  -- level 을 공백으로 찍어 본다.
FROM             emp
WHERE     ename LIKE '%K%'
START WITH       job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층

-- 2) CONNECT BY PRIOR 아래에 AND 사용
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename,LEVEL, empno, mgr, job  -- level 을 공백으로 찍어 본다.
FROM             emp
START WITH       job = 'PRESIDENT'   -- 직업이 PRESIDENT를 기준으로
CONNECT BY PRIOR empno = mgr -- 사원(empno)과 관리자(mgr)의  관계를 계층
AND      ename LIKE '%K%'

-- 3) LEVEL 조건 사용
SELECT           LPAD(' ', 4*(LEVEL-1)) || ename ename, empno, mgr, job
FROM             emp
START WITH       job='PRESIDENT'
CONNECT BY PRIOR empno =mgr
AND              LEVEL <= 2

---- 예제4 :  각 label별로 급여의 합과 인원수를 구하는 예제
 
SELECT           LEVEL, SUM(sal) salTotal,COUNT(empno) empnCnt
FROM             emp
START WITH       job='PRESIDENT'
CONNECT BY PRIOR empno=mgr     
GROUP BY         LEVEL
ORDER BY         LEVEL

-- 결과치 
     LEVEL   salTotal    empnCnt
---------- ---------- ----------
         1       5000          1
         2       8275          3
         3      13850          8
         4       1900          2

★ LEVEL 키워드 (알아두면 유용함)

오라클에서 실행되는 모든 쿼리내에서 ROWNUM과 더불어 가상 컬럼이라 할 수 있다.

계층적 쿼리 트리내에서 어느한 위치, 또는 단계(LEVEL)에 위치하는가를 나타내는 정수 값 컬럼이다.

그렇다면 당연히 계층적 쿼리가 아닌 일반 쿼리에서 LEVEL컬럼의 값이 모두 0으로 나올 것이다.


[원문] http://blog.naver.com/mocheri/80080188893

-- 이름과 입사일과 입사한 요일을 출력하는데
월 화 수 목 금 토 일 순으로 출력하시오

select ename, hiredate, decode(to_char(hiredate,'D'),
       2, 'mon',
       3, 'tue',
       4, 'wed',
       5, 'thu',
       6, 'fri',
       7, 'sat',
       1, 'sun')
from emp
order by mod(nvl(substr(to_char(hiredate,'D')*9, 2, 1),0), 9) desc;

전체 사용자에 대해 설정할거면 /etc/environment   ,
개별 사용자는 각자의 홈디렉토리의 .bashrc 에....

$sudo gedit /etc/environment
 or
$sudo gedit .bashrc

열고 아래 부분 추가하고 ,
(두 군데에 해도 되고, .bashrc에만 해도 되고 ..

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME
export ORACLE_SID=XE
export NLS_LANG='KOREAN_KOREA.AL32UTF8'

저장하고 나서 아래 명령문 두 개 쳐주고 나면 되던데,,

$ source .bashrc
$ sudo ln -s /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/sqlplus /usr/bin/sqlplus