나의 향기에 젖다...

이런 환경에서는 SID와 Service Name을 구분할 필요가 없었던것이다.

단순히 구분짓자면 이렇게 말할수 있다.

SID = DB 하나의 인스턴스

Service Name = 여러개의 인스턴스를 모아 하나의 서버 혹은 시스템을 구성한것

쉽게 예를 들어보자.

서버 한대에 인스턴스를 여러개 생성하여 orcl1, orcl2 로 각각 생성했다고 하자.

각각의 인스턴스는 orcl1, orcl2 라는 SID를 갖게 된다.

해당 서버에서 두개의 인스턴스를 묶어 사용할경우, orcl 이라는 Service Name을 갖을수 있다.

이외에도 서버 두대에 설치하여 각각 미러링 처리하여 동일한 서버인것 처럼 활용할경우

각각의 서버는 서로다른 SID를 갖게 되지만 Service Name을 동일하게 하여 같은 서버 처럼 활용할수 있다.

1. 오라클 데이타베이스명 확인하는 방법

SELECT NAME, DB_UNIQUE_NAME FROM v$database;

2. 오라클 SID 확인하는 방법

SELECT INSTANCE FROM v$thread;

* DB에 존재하는 모든 ROW가 갖는 유일한 식별자

* ROWID는 SELECT문을 이용하여 조회 할수 있지만 UPDATE문을 이용한 UPDATE는 불가능

* INSERT문을 이용해서 강제로 부여할수 없음

ROWID 구성 정보

000000 FFF BBBBBB RRR ( 6 + 3 + 6 + 3)

Display : 18자리

저장 : 10자리

000000 : Data Object Number :DB Segment 식별 정보

(해당 로우가 속해 있는 오브젝트 번호)

FFF : Relative File : Tablespace에 상대적 Datafile 번호

(해당 로우가 속해 있는 데이터 파일 번호)

BBBBBB : Block Number : Row를 포람하는 Data Block 번호

(해당 로우가 속해있는 데이터 파일의 데이터 블록 주소값)

RRR : Row Number : Block에서의 ROW의 SLOT

(데이터 블록 내에서 해당 로우의 주소 값)


출처 : http://blog.naver.com/speedsky98/120045820683

[인덱스] - 색인

책에서 원하는 내용을 빨리 찾으려면 인덱스를 이용(책의 인덱스와 비슷한 개념)
DB도 사용자가 원하는 내용을 빨리 찾으려면 색인이란 정보를 미리 만들어서 원하는 데이터를 빨리 찾을 수 있게 할 수 있다.

데이터베이스내의 테이블에서 원하는 정보를 좀더 빨리 찾아줄수 있게  데이터의 위치 정보를 모아놓은 데이터베이스내의 객체 object이다.

 
* 인덱스는 정렬되어 있다.
  예1) 사진에서 원하는 데이터를 찾을때
  예2) 책에서 책뒷부분의 인덱스 페이지
 

table scan  -- 전부다 검색
index seek  -- 원하는 페이지만 검색
===========================================
* 포인트쿼리   point query
  조회되는 데이터가 한두개
  select * from tb_member where uid = 'kim'    -- 값이 하나
 
* 범위쿼리   range query
  조회되는 데이터가 다수
  select * from tb_member where regdt = '2008/06/19'    -- 값이 다수
 
* 커버드쿼리  covered query
  조회의 대상과 조회의 결과가 컬럼이 일치하는 상태
  인덱스 측면에서 제일 빠른 성능을 냄
 
select * from tb_member

 where uid = 'kim' and upw = '1234'    -- 커버드 쿼리 아님 포인트쿼리
 
select uid from tb_member
 where uid = 'kim' and upw = '1234'    -- 커버드 쿼리


1. clustered index 클러스터 인덱스
형식) create clustered index 인덱스명
        on 테이블명(칼럼명 오름/내림)
해당 컬럼을 기준으로 정렬, 테이블당 1개씩만 허용.
데이터를 여러개 조회하는 범위 쿼리이건, 하나만 조회하는 포인트 쿼리건 둘다 성능 발휘.
primary key  설정시 그 컬럼에 자동으로 클러스터드 인덱스가 만들어진다.
이 컬럼은 데이터 입력, 수정, 삭제시 항상 정렬을 유지한다.
기본적으로 인덱스는 정렬되어 있다. <<-- 이자체가 인덱스
즉 그 인덱스가 사전식으로 정렬 된다.


2. nonclustered index 넌클러스터 인덱스 

인덱스 페이지 따로 만든다. 용량이 더 차지 한다.(로그파일에 저장)
기존의 테이블 + 넌클러스트 인덱스테이블
형식) create nonclustered index 인덱스명
        on 테이블명(칼럼명 정렬)
인덱스 페이지 따로 만든다.
레코드 원본은 정렬 안된다.
테이블당 240개 정도 만들수 있다.
포인트 쿼리는 성능발휘/ 범위 쿼리는 장담 할 수 없다.
 

예) 클러스터 인덱스는 책을 비유하자면 페이지를 알기 때문에 바로 그 페이지를 펴는 것과 비교되는것이고
넌클러스터 인덱스는 뒤에 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동 하는것과 같다.
테이블스캔은 처음부터 한장씩 넘기면서 내용을 찾는것과 같다.
 
-- 포인트 쿼리일때 비교 : 포인트 쿼리에서는 별다른 속도 차이가 없다.

-- 인덱스 추가 범위 쿼리일때 비교

select * from w_zipcode where dong like '당산%'
select * from c_myzip     where dong like '당산%'
select * from n_myzip     where dong like '당산%'    -- %당산% 일때 처음에 %가 있으면 table scan 으로 된다. 따라서 속도시간이 늘어난다
 
create clustered index dong인덱스
on c_myzip(dong asc)

create nonclustered index dong인덱스2
on n_myzip(dong asc)

* 어떤 컬럼에 인덱스를 걸어야 하는지??
1. where절에서 자주 사용되는 컬럼 (예 : dong 컬럼 -> 자주 검색하기 때문에)
2. like '%~~~' 조심. %는 뒤에만 오게 해야 속도가 빨라진다.
3. between A and B (클러스터인덱스가 유리)

범위 쿼리문에서는 클러스터드인덱스가 유리하지만 클러스터드인덱스는 그 테이블에서 한번만 사용되는 단점을 가지고 있다.
4. order by가 항상 사용되는 컬럼
5. join으로 자주 사용되는 컬럼
FK( 1:1 대응이 많을 때 -- >  둘다 상관 없음(상황에 따라 넌클러스터드 인덱스를 사용)
1:N 대응이 많을 때 -- > 클러스터드 인덱스 유리
6. 100만건 중에 10개 조회/1000개 조회. 찾는 것이 적은 수에 주로 인덱스를 걸어주는 것이 상책이다.
주의)  중복 데이터가 많은 컬럼 (성별) --> 인덱스를 거는게 아님
조회되는것이 많으면 그냥 처음부터 찾는것이 나은편.
7. not 연산자 -> 긍정문을 바꿔서...
8. insert, delete가 빈번한 컬럼은 인덱스에 좋은 영향은 아님

* 인덱스로 인해 얻는 손해
1. 만드는데 시간과 많은 공간이 필요하고, 만들고 난 후에도 추가적인 공간이 필요한다.
2. 데이타를 수정(insert, delete, update)하는 시간, 특히 insert작업은 오히려 더 많이 걸린다.

SQL> L

1 DECLARE

2 t_personnel PERSONNEL%ROWTYPE;

3 BEGIN

4 SELECT EMP_NAME, ADDRESS

5 INTO t_personnel.emp_name, t_personnel.address

6 FROM PERSONNEL

7 WHERE EMPNO='98002';

8

9 dbms_output.put_line('<<<조회결과>>>');

10 dbms_output.put_line(' 이름 : ' || t_personnel.emp_name);

11 dbms_output.put_line(' 주소 : ' || t_personnel.address);

12 EXCEPTION

13 WHEN NO_DATA_FOUND THEN

14 dbms_output.put_line('<<< ERROR >>>');

15 dbms_output.put_line(' 에러내용 : 조회된 데이타가 없습니다.!!');

16 WHEN TOO_MANY_ROWS THEN

17 dbms_output.put_line('<<< ERROR >>>');

18 dbms_output.put_line('에러내용 : 조회 데이타가 너무 많습니다.!!');

19 WHEN OTHERS THEN

20 dbms_output.put_line('<<< ERROR >>>');

21 dbms_output.put_line('에러코드 : ' || SQLCODE );

22 dbms_output.put_line('에러내용 : ' || SUBSTR(SQLERRM, 1, 100));

23* END;

SQL> /

<<<조회결과>>>

이름 : 이대명

주소 : 경기도 군포시 궁내동

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> L

1 declare

2 t_personnel PERSONNEL%ROWTYPE;

3 BEGIN

4 SELECT EMP_NAME, ADDRESS

5 INTO t_personnel.emp_name, t_personnel.address

6 FROM PERSONNEL;

7

8 dbms_output.put_line('<<<조회결과>>');

9 dbms_output.put_line('이름 : ' || t_personnel.emp_name );

10 dbms_output.put_line('주소 : ' || t_personnel.address);

11 EXCEPTION

12 WHEN NO_DATA_FOUND THEN

13 dbms_output.put_line('<<<ERROR>>>');

14 dbms_output.put_line('에러내용 : 조회된 데이타가 없습니다.');

15 WHEN TOO_MANY_ROWS THEN

16 dbms_output.put_line('<<<ERROR>>>');

17 dbms_output.put_line('에러내용 : 조회 데이타가 너무 많습니다.');

18 WHEN OTHERS THEN

19 dbms_output.put_line('<<<ERROR>>>');

20 dbms_output.put_line('에러코드 : ' || SQLCODE );

21 dbms_output.put_line('에러내용 : ' || SUBSTR(SQLERRM, 1, 100));

22* END;

SQL> /

<<<ERROR>>>

에러내용 : 조회 데이타가 너무 많습니다.

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> L

1 DECLARE

2 t_personnel PERSONNEL%ROWTYPE;

3 BEGIN

4 SELECT EMP_NAME, ADDRESS

5 INTO t_personnel.emp_name, t_personnel.address

6 FROM PERSONNEL

7 WHERE EMPNO='98002';

8

9 IF SQL%ROWCOUNT = 1 THEN

10 dbms_output.put_line('<<<조회결과>>>');

11 dbms_output.put_line('이름 : ' || t_personnel.emp_name);

12 dbms_output.put_line('주소 : ' || t_personnel.address);

13 ELSIF SQL%ROWCOUNT = 0 THEN

14 dbms_output.put_line('<<<ERROR>>>');

15 dbms_output.put_line('에러내용 : 조회된 데이타가 없습니다.');

16 ELSIF SQL%ROWCOUNT > 1 THEN

17 dbms_output.put_line('<<<ERROR>>>');

18 dbms_output.put_line('에러내용 : 조회 데이타가 너무 많습니다.');

19 END IF;

20

21 EXCEPTION

22 WHEN OTHERS THEN

23 dbms_output.put_line('<<<ERROR>>>');

24 dbms_output.put_line('에러코드 : ' || SQLCODE );

25 dbms_output.put_line('에러내용 : ' || SUBSTR(SQLERRM, 1, 100));

26 END;

27 /

<<<조회결과>>>

이름 : 이대명

주소 : 경기도 군포시 궁내동

PL/SQL 처리가 정상적으로 완료되었습니다.

 기존에는 테이블을 새로 작성(reorg)  작업을 통해 커진 테이블 스페이스 크기를 복구 가능하였으나 10g 부터는 Shrink 기능을 이용하여 손쉽게 테이블을 줄일수 있게 되었다.

Shrink 기능은 HWM(High Water Mark)를 줄일수 있는 기능이다.

- 사전 확인 사항

init.ora 파일내에 compatible = 10.2 이상인것을 확인

ASSM (Automatic Segment Space Management) 테이블 스페이스여만 가능

각 테이블 조회 방법은

SELECT OBJ.OWNER
           , OBJ.TABLE_NAME, OBJ.TABLESPACE_NAME
           , (CASE WHEN NVL(IDX.CNT, 0) < 1 THEN 'YES' ELSE 'NO' END) SHRINKABLE
   FROM DBA_TABLES OBJ,
             (SELECT TABLE_NAME, COUNT(ROWNUM) CNT
                 FROM DBA_INDEXES
               WHERE INDEX_TYPE LIKE 'FUN%'
               GROUP BY TABLE_NAME) IDX
 WHERE OBJ.TABLE_NAME = IDX.TABLE_NAME(+)
     AND OBJ.TABLESPACE_NAME = :TABLESPACE_NAME;

쿼리를 통해 (테이블 스페이스 명을 인자값으로) 확인 가능하다.

1. 테이블 검색 방법

SELECT * FROM TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS());

아래와 같이 테이블의 DBA_SEGMENTS에서 볼수 있는 크기와 축소 가능 크기가 조회된다.

SEGMENT_OWNER | SEGMENT_NAME | SEGMENT_TYPE | PARTITION_NAME |

ALLOCATED_SPACE | USED_SPACE | RECLAIMABLE_SPACE
MAX_TI | SMR_IF_LOG       | TABLE  | | 6761793074 | 5910149080 | 851643994
MAX_TI | SMR_IF_LOG_I01 | INDEX  | |   478455081 |   420139142 | 58315939
MAX_TI | SMR_IF_LOG_I02 | INDEX  | |   411921243 |   398204079 | 13717164

2. 축소하기

가장 먼저 행들을 이동 가능하도록 변경한다.

위의 SMR_IF_LOG 테이블을 기준으로 설명하겠다.

ALTER TABLE SMR_IF_LOG ENABLE ROW MOVEMENT;

--> 반대로는 ALTER TABLE SMR_IF_LOG DISABLE ROW MOVEMENT; 이다..

이제 SHRINK 기능을 수행할수 있으며, 다음 3가지 형태로 가능하다

① ALTER TABLE SMR_IF_LOG SHRINK SPACE; 
    -- SMR_IF_LOG 테이블의 공간만 TABLESPACE로 환원

② ALTER TABLE SMR_IF_LOG SHRINK SPACE CASCADE;
    -- 모든 관련 OBJECT까지 TABLESPACE으로 공간 환원

③ ALTER TABLE SMR_IF_LOG SHRINK SPACE COMPACT;
   -- 행들만 이동시킴.

※ SHRINK 를 적용 시킬  없는 테이블
UNDO segments
temporary segments
clustered tables
tables with a column of datatype LONG
LOB indexes
IOT mapping tables and IOT overflow segmnets
tables with MVIEWS with ON COMMIT
tables with MVIEWS which are based on ROWIDs.

[출처] 웹 개발과 관련된 모든 것들 (http://web-dev.tistory.com/116)

CREATE OR REPLACE PROCEDURE SAMPLE_PROCE AS

-- SELETE 문의 결과를 TEMP_CUR 라는 CURSOR 에 넣는다.
CURSOR TEMP_CUR IS
SELECT BWCODE, BWNAME, BWADDS
  FROM TEMP_TAB;

-- TEMP_CUR의 타입을 그대로 사용할 수 있는 NOW_CUR 변수선언
NOW_CUR TEMP_CUR%ROWTYPE;

BEGIN

-- 커서 사용을 위해서 연다.
OPEN TEMP_CUR;

-- 반복문 시작
LOOP

-- 커서의 ROW를 NOW_CUR로 보낸다.
FETCH TEMP_CUR INTO NOW_CUR;

-- DBMS_OUTPUT.put_line('LOOP 시작');

-- 커서가 끝나면 반복문을 종료한다.
EXIT WHEN TEMP_CUR%NOTFOUND;

--조건문 사용
IF (NOW_CUR.BWCODE  <> 'A') THEN

ELSIF -- 또는 ELSE IF

ELSE

  
END IF;

INSERT INTO NEW_TAB ( BWCODE, BWNAME, BWADDS )
VALUES ( NOW_CUR.BWCODE, NOW_CUR.BWNAME, NOW_CUR.BWADDS );

COMMIT;

END LOOP;

-- 커서를 닫는다.
CLOSE TEMP_CUR;

EXCEPTION 

WHEN OTHERS THEN

-- 에러 발생시 에러 메시지 출력

-- 토드의 DBMS_OUTPUT 에서 확인 할 수 있다.

DBMS_OUTPUT.put_line('ERROR');

ROLLBACK; 

END;

실행은 ~~

EXEC SAMPLE_PROCE;

디버깅은 ~~

CREATE OR REPLACE PROCEDURE SAMPLE_PROCE 부분을 DECLARE 로 바꾸고
중간중간마다 DBMS_OUTPUT.put_line(...) 를 이용해서 처리한다.

* 참고로 SET SERVEROUTPUT ON; 작성 후 실행해야 DBMS_OUTPUT 나옴.

여러개의 쿼리가 있을경우엔 블록지정을 해줘야만 실행계획이 나온다.
Explain plan 을 실행하려면 ctrl + E 를 눌러주면 아래의 상태창에 실행 계획이 나온다.

1. 트리를 보면 왼쪽에 숫자가 나온다. 1부터가 실행되는 순서!!

 - 플랜은 트리 형태로 되어 있으며, 자신보다 하위 레벨이 있으면 하위 레벨부터, 같은 레벨이라면 상위 로우 소스부터 실행된다.

2.  TABLE ACCESS FULL 는 쿼리상 나오면 안됨(겁나 느림)!!

 - 옵티마이저가 통계 정보를 이용해 추정한 값이라고 보면 된다.

Cost - 옵티마이저가 해당 오퍼레이션을 수행하는데 필요하다고 판단한 비용. 측정을 위한 단위가 있는 것은 아니고, 그냥 weight 값 정도로 생각하면 됨. CBO는 여러 가지 실행 계획을 세우고 이에 대한 비용(cost)를 비교한 다음 비용이 가장 적은 실행 계획으로 SQL을 실행함.

Cardinality - 오퍼레이션을 수행할 때 접근하게 될 row 수. 역시 옵티마이저가 추정한 값
 
Bytes - 해당 오퍼레이션을 수행할 때 접근하게 될 byte 수. 옵티마이저가 추정한 값

3. 실행계획에서는 옵티마이져가 판단한 값!!

 - 실제 oracle의 실행과 다를수도 있으며 정확한것은 Trace를 통해서 확인해야 한다. 단지 이것을 어떻게 쿼리를 풀어나가는지를 확인하는 단계다.

[예제] JOIN UPDATE

UPDATE TABLE_MAIN
      SET TABLE_MAIN.REGUSR = '쫑'
   FROM TABLE_JOIN
 WHERE TABLE_JOIN.SEQNUM = TABLE_MAIN.SEQNUM
     AND TABLE_JOIN.PROGBN = 'Y'

[예제] JOIN DELETE

DELETE TABLE_MAIN
   FROM TABLE_JOIN
 WHERE TABLE_JOIN.SEQNUM = TABLE_MAIN.SEQNUM
     AND TABLE_JOIN.PROGBN = 'Y'

조인을 이용한 UPDATE/DELETE 구문의 FROM 절 이후는 똑같다.

DATEDIFF 함수

입력받은 두 날짜 또는 시간이 얼마나 차이가 나는지 계산

OPTION : DD(일), HH(시), MI(분), SS(초), MS(밀리초)

[예제]

SELECT DATEDIFF( DD, '2012-11-06 11:34', GETDATE())
→ 2

SELECT DATEDIFF( HH , '2012-11-07 11:30', '2012-11-08 14:44')
→ 27

SELECT DATEDIFF( MI , '2012-11-07 11:30', '2012-11-08 14:44')
→ 1634

SELECT DATEDIFF( HH, '11:30', '14:44')
→ 3

1. Toad for Oracle 기능 및 사용법 (첨부)

▶ 목차
   * Toad for Oracle 제품 개요
   * 설치 방법
   * Toad 제공 기능 및 사용법   
      - Server Login
      - Schema Browser ( Data Grid )
      - SQL Builder
      - SQL Editor
      - Procedure Editor
      - SQL Optimizer
      - DBA 기능

Toad_for_Oracle_기능_및_사용법.ppt

2. Toad Advanced 교육 자료 (첨부)

TOAD_Advanced_교육_자료.pdf