반응형

<출처: http://rayfeel.co.kr/63 >

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

자동 인덱스 : 프라이머리 키 또는 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 제약 조건시 생성되는 인덱스는 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를 할 수 없습니다.
SQL>DROP INDEX emp_empno_ename_indx ;

※  인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색할 수
      있습니다.


반응형

Oracle 에서 테이블의 컬룸의 값들이 자동으로 증가하도록 테이블을 구성하는 방법

우선 원하는 테이블을 생성한다

CREATE TABLE ajaxchat (
  id int NOT NULL ,
  user varchar(255) NOT NULL,
  msg varchar(255) NOT NULL,
  time int NOT NULL,
  PRIMARY KEY  (id)
);

그후 Sequence 테이블을 작성 한다 .

SQL>CREATE SEQUENCE id
        START WITH 1
        INCREMENT BY 1
        MAXVALUE 100000 ;

 

 시작 값이 1일고 1씩 증가하고, 최대값이 100000만이 되는 시퀀스를 생성했습니다.

그리고 값을 저장 할 때 이런식으로 insert 시킨다


 SQL>INSERT INTO ajaxchat(id, user, msg,time ) VALUES(id.NEXTVAL, ’julia’ , sysdate,1);

    empno는 컬럼값을 입력할 때 일일이 다음 값을 기억하지 않아도 NEXTVAL을 사용하여
    자동으로 입력할 수 있습니다.




 시퀀스란?


유일(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를 지정할 경우 최소값은 무한대가 됩니다

 


 SQL>CREATE SEQUENCE emp_seq
        START WITH 1
        INCREMENT BY 1
        MAXVALUE 100000 ;

      sequence created.

     시작 값이 1일고 1씩 증가하고, 최대값이 100000만이 되는 시퀀스를 생성했습니다.


 SQL>INSERT INTO emp(empno, ename, hiredate ) VALUES(emp_seq.NEXTVAL, 'julia' , sysdate);

    empno는 컬럼값을 입력할 때 일일이 다음 값을 기억하지 않아도 NEXTVAL을 사용하여
    자동으로 입력할 수 있습니다.

  CURRVAL : 현재 값을 반환 합니다. .
   NEXTVAL : 현재 시퀀스값의 다음 값을 반환 합니다.


 SQL>SELECT emp_seq.CURRVAL FROM DUAL ;

        CURRVAL
        ---------
                  1

 SQL>SELECT emp_seq.NEXTVAL FROM DUAL ;

       NEXTVAL
     ---------
                 2
 



사용규칙 


  ◈ 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와 같습니다.
 


 SQL>ALTER SEQUENCE emp_seq
         INCREMENT BY 2
         CYCLE;

      sequence altered.

      2씩 증가하고, 최대값을 넘으면 다시 처음부터 순환하도록 수정하였습니다.


      DROP 문을로 필요하지 않은 시퀀스는 삭제 할수 있습니다.

 SQL>DROP SEQUENCE PRD_SEQ;
        sequence dropped.
 

  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 운영자 : 김정식 (oramaster _at_ naver.com)
  ================================================
※ oracleclub 강좌를 다른 홈페이지에 퍼가실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

 

반응형


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


☞ UNION

 ◈ UNION은 두 테이블의 결합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환 합니다.
 
 
SQL>SELECT deptno FROM emp
        UNION
        SELECT deptno FROM dept;
 
    DEPTNO
----------
        10
        20
        30
        40



☞ UNION ALL

◈ UNION과 같으나 두 테이블의 중복되는 값까지 반환 합니다.
 
SQL>SELECT deptno FROM emp
        UNION ALL
       SELECT deptno FROM dept;
 
   DEPTNO
---------
       20
       30
       30
       20
       30
       30
       10
       20
       10
       30
....



☞ INTERSECT

INTERSECT는 두 행의 집합중 공통된 행을 반환 합니다.
 
SQL>SELECT deptno FROM emp
        INTERSECT
        SELECT deptno FROM dept;
   
    DEPTNO
----------
        10
        20
        30



☞ MINUS

MINUS는 첫번째 SELECT문에 의해 반환되는 행중에서 두번째 SELECT문에 의해 반환되는 행에
    존재하지 않는 행들을 반환 합니다.
 
SQL>SELECT deptno FROM dept
        MINUS
        SELECT deptno FROM emp;
 
    DEPTNO
----------
        40

  ================================================
    * 오라클 정보공유 커뮤니티 oracleclub.com
    * http://www.oracleclub.com
    * http://www.oramaster.net
    * 강좌 작성자 : 김정식 (oramaster _at_ naver.com)
  ================================================
반응형

oracle DECODE 함수 사용법

 

* DECODE란 오라클에서만 지원하는 함수로서SELECT문장 내에서 비교연산을 수행해 주는 아주 편리한 함수입니다.

형식은 다음과 같이 사용하시면 됩니다.


DECODE(deptname, 'A',1,0)
-> deptname
'A'와 같으면 1을 갖고 아니면 0을 갖게 됩니다.

 

또는 SUM함수로 합계를 내줄 수 있습니다.
SUM(DECODE(deptname, 'A',1,0)
-> deptname
'A'와 같으면, 1을 누적하고,아니면 0을 갖게 됩니다.

 

2 DECODE문도 허용됩니다. 다음을 보도록 하겠습니다.

DECODE(deptname, 'A', DECODE(name,'KIM',1),0)
-> deptname
'A'와 같고, name 'KIM'이면, 1을 같고, 아니면, 0을 갖는다.

 

2 DECODE문을 쓸 때 두 개의 DECODE문은 AND연산으로 수행됨을
명심하길 바랍니다.

 

예제) 사원테이블(PA06MR0)에서 영문 성이 'KIM'으로 시작하는 사람과 'LEE'로 시작하는 사람의 합을 구하라. 단 입사년도가 올해(1998)인 사람의 합을 구하라.

 

SELECT EMPNAME, SUM(DECODE(SUBSTR(EMPNAME,1,3),'KIM',1,0)
                             + DECODE(SUBSTR(EMPNAME,1,3),'LEE',1,0)),
FROM tableName
WHERE SUBSTR(entondate,1,4) = '1998';
GROUP BY EMPNAME;

반응형

WITH TEST AS
(
    select 'A' grade, 10 cnt from dual union all
    select 'A' grade, 20 cnt from dual union all
    select 'A' grade, 15 cnt from dual union all 
    select 'B' grade, 20  from dual union all
    select 'B' grade, 30  from dual union all
    select 'D' grade, 15  from dual union all
    select 'F' grade, 10  from dual
)
SELECT
    grade 등급,
    cnt 학생수,
    sum(cnt) over () 갯수,
    row_number() over(partition by grade order by cnt) 로넘,
    rank() over(partition by grade order by cnt) 랭크,
    round((cnt / sum(cnt) over ())*100,2) 구성비,
    sum(cnt) over (partition by grade  order by grade) 누적학생수,
    sum(cnt) over (partition by grade,cnt  order by grade) 그레카운,
    sum(cnt) over (order by grade) 누적,
    round((sum(cnt) over (order by grade)/sum(cnt) over ())*100,2) 누적구성비
FROM TEST;

 

/*

  rank() over(partition by grade order by cnt) 랭크,

 

  표현식 over (partition by 컬럼1 order by 컬럼2)

 

  이 의미는

  "(컬럼1값이 같은놈들로 묶은것을 컬럼2로 정렬한) 각 파티션별로 표현식을 수행해준다."

*/

 

복잡하군... :(

[출처] Oracle over() 구문|작성자 18061975

반응형

CREATE or REPLACE FUNCTION sum_emp //sum_emp 이름으로 함수 생성
(v_deptno IN emp.deptno%TYPE) //입력받는값 변수에 empTB에 deptno랑 같은 타입으로받겟다
//v_deptno 란 변수에 emp.deptno 와 같은 타입으로 선언
return number 변수를 넘버타입으로 리턴
IS v_sum_sal emp.sal;%TYPE; //변수와 데이타 타입

BEGIN
 SELECT SUM(sal) //SUM(sal)은 살레코드의의 값을 다 더하겟다
 INTO v_sum_sal
 FROM emp
 WHERE dept=v_deptno; //입력받은 부서번호와 일치한다면
 RETURN (v_sum_sal); //리턴하라
END;
/
excute example)SELECT sum_emp(20) FROM dual;


CREATE or REPLACE PROCEDURE 이름
(v_deptno IN emp.deptno%TYPE,
 v_sum_sal OUT emp.sal%TYPE) //IN:mode is IN in function, OUT:argument result is return
IS
BEGIN
 SELECT SUM(sal)
 INTO v_sum_sal   //
 FROM emp
 WHERE deptno = v_deptno;//입력받은 값과 같은때 그걸 가주와서 섬에서 다 더하고 v_sum_sal 이란 변수에 너케따
END;
/
프로시져 실행
VARIABLE x number //프로시저를 실행시켜서 값이 나오면 저장시킬 변수를 일단 선언
execute  프로시져네임(10, :x)
//10번이 파라미터값으로 들어가고 x 에 v_sum_sal 에 결과값이 들어간다
PRINT x // v_sum_sal 의 값이 x 에 저장되 있으니 x  를 출력하라


IF  조건  THEN
    문장;
ELSE
    문장;]
END IF;


LOOP
    문장1;
    문장2;
    ···
    EXIT[WHEN 조건];   --EXIT 문
END LOOP;

CREATE or REPLACE PROCEDURE loop_basic1
IS
 v_num number(2) := 1;
 v_sum varchar(10) := NULL;
BEGIN
 LOOP
  v_sum := v_sum||'*';
  DBMS_OUTPUT.PUT_LINE(v_sum);
  v_num := v_num+1;
  EXIT WHEN v_num > 10;
 END LOOP;
END loop_basic1;
/

FOR 인덱스 IN [REVERSE] 하한..상한 LOOP
    문장1;
    문장2;
    ···
END LOOP;

CREATE or REPLACE PROCEDURE for_basic1
IS
 v_char varchar2(10) := NULL;
BEGIN
 FOR I IN 1..10 LOOP
  v_char := v_char||'*';
  DBMS_OUTPUT.PUT_LINE(v_char);
 END LOOP;
END for_basic1;
/


WHILE  조건  LOOP
    문장1;
    문장2;
    ···
END LOOP;

CREATE or REPLACE PROCEDURE while_basic1
IS
 v_num number(2) := 1;
 v_char varchar2(10) := NULL;
BEGIN
 WHILE v_num <= 10 LOOP
  v_char := v_char||'*';
  DBMS_OUTPUT.PUT_LINE(v_char);
  v_num := v_num+1;
 END LOOP;
END while_basic1;
/

반응형

- 논리적인 테이블


- 물리적으로도 연결되어 있기 때문에 원본과 VIEW가

  서로 상호적으로 데이터 변경 시 각자에 반영된다.


- SELECT를 단순하게 하기 위해 WIEW씀

  (내가 정의해서 저장해놓고 쓰고 싶을 때마다 호출하여 쓸 수 있다.)


- 보안상으로 VIEW씀


// 뷰 생성

CREATE VIEW EMP_VIEW(EMPNO, ENAME, JOB) AS

SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB='SALESMAN';


// 뷰 생성 OR 수정

  -> 없는 뷰 이름을 써주면 뷰가 생성되고 있는 뷰 이름을 써주면 뷰가 수정된다.

CREATE OR REPLACE VIEW EMP_VIEW(EMPNO, ENAME, JOB) AS

SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB='SALESMAN';


컬럼명은 달라도 되지만 나중에 알아봐야 하기 때문에 같은 이름으로

주는것을 지향한다.


CREATE 해야 나중에 종료 후에도 쓸 수 있다.


// 그룹 뷰 생성 시 꼭 별명을 써야한다. 아래 에러

CREATE VIEW V1(DEPTNO, AVG(SAL), SUM(SAL)) AS
SELECT DEPTNO, AVG(SAL), SUM(SAL) FROM EMP GROUP BY DEPTNO;


// 그룹 뷰 생성 시  생성 방법

CREATE VIEW V1(DEPTNO, 평균, 합) AS
SELECT DEPTNO, AVG(SAL) AS 평균, SUM(SAL) 합 FROM EMP GROUP BY DEPTNO;


// 내가 만든 VIEW의 내용 조회

SELECT VIEW_NAME, TEXT FROM USER_VIEWS;


VIEW_NAME                                                    TEXT
-------------------          --------------------------------------------------------
EMP_VIEW                       SELECT EMPNO, ENAME, JOB FROM EMP

                                       WHERE JOB='SALESMAN'

V1                                   SELECT DEPTNO, AVG(SAL) AS 평균, SUM(SAL) 합

                                       FROM EMP GROUP BY DEPTNO


// 복합 VIEW 생성

CREATE VIEW EMPVI(EMPNO, ENAME, JOB, DEPTNO, DNAME) AS
SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO AND JOB='SALESMAN';

[출처] View|작성자 bkh4536

반응형
출처 탁월함을 찾아서 | 핑크펜더
원문 http://blog.naver.com/cccnam5158/80045146598

oracle 10g 를 삭제하는 방법에 대해 알아보겠습니다

일반적으로 오라클을 삭제하고 나서도 레지스트리에 정보가 남아있다거나

깨끗하게 삭제하는 것은 어렵다고 생각하여

OS 를 재설치하는 등의 여러가지 수고가 들게 됩니다


최대한 깨끗하게 삭제하는 절차를 정리해보겠습니다


1. 먼저 OUI(Oracle Universal Installer) 를 이용하여 설치된 오라클 관련 제품을 삭제합니다

2. services.msc 를 실행하신후 오라클 관련 서비스 모두를 멈춥니다

3. 다음의 경로에 포함되어 있는 레지스트리 정보를 삭제합니다

    - 사용자에 따라 경로가 다를수 있습니다

    HKEY_CURRENT_USER\SOFTWARE\ORACLE
    HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Application

    \Oracle.oracle
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OracleDBConsole
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Oracle10g_home
    HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\OraclService

4. 파일 시스템에서 오라클 관련 폴더를 삭제합니다 - 사용자의 환경에 따라 다릅니다

    Oracle Home Folder

    Oracle 이 설치된 Program Files 하단의 Oracle 관련 폴더 삭제

    Oracle Start Group 삭제 -

     예) C:\Documents and Settings\All Users\Start Menu\Programs\Oracle*

    - 위의 폴더를 지웠다고 해서 서비스 목록에서 오라클 관련 서비스 목록이 삭제되지는 않습니다

      재부팅이 필요합니다
5. 시스템 환경 변수에서 오라클 관련 설정 내용들을 모두 삭제합니다

    - System Path 와 user defined Path 모두 해당됩니다

6. 그리고 다음의 정보를 삭제합니다

        C:\Windows\assembly\ 의 Oracle.DataAccess 와 xxx Polic.Oracle 을 삭제합니다


이상으로 삭제가 완료되었습니다

새로운 오라클을 시작하기전에 반드시 위의 과정을 거치시는 것을 추천합니다

+ Recent posts