레이블이 오라클실무교육인 게시물을 표시합니다. 모든 게시물 표시
레이블이 오라클실무교육인 게시물을 표시합니다. 모든 게시물 표시

2013년 10월 28일 월요일

Column Name & Constraints 이름 변경 예 테이블이나 인덱스의 이름을 변경 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr) [주간] [11/4]Spring3.X, MyBatis, Hibernate실무과정 [11/6]SQL초보에서실전전문가까지 [평일야간] [11/1]C#,ASP.NET마스터 [11/5]iPhone 하이브리드 앱 개발 실무과정 [11/7]JAVA&WEB프레임워크실무과정 [11/8]Spring3.X, MyBatis, Hibernate실무과정 [주말] [11/2]C#,ASP.NET마스터 [11/2]Spring3.X, MyBatis, Hibernate실무과정 [11/2]JAVA&WEB프레임워크실무과정 [11/9]안드로이드개발자과정 JAVA ORACLE iPhone/Android .NET 표준웹/HTML5 채용/취업무료교육 초보자(재학생)코스 Spring3.X, MyBatis, Hibernate실무과정 총 5일 35시간 11-04 JAVA&WEB프레임워크실무과정 총 33일 99시간 11-07 Spring3.X, MyBatis, Hibernate실무과정 총 12일 36시간 11-08 자바초보에서안드로이드까지 총 18일 54시간 11-15 Spring3.X, MyBatis, Hibernate실무과정 총 5일 35시간 11-02 JAVA&WEB프레임워크실무과정 총 14일 98시간 11-02 SQL초보에서실전전문가까지 총 8일 56시간 11-06 고급개발자를위한 오라클힌트&SQL튜닝 총 10일 30시간 11-08 SQL초보에서실전전문가까지 총 18일 54시간 11-13 고급개발자를위한 오라클힌트&SQL튜닝 총 4일 32시간 11-09 SQL초보에서실전전문가까지 총 8일 56시간 11-10 Column Name & Constraints 이름 변경 예 테이블이나 인덱스의 이름을 변경하는 것은 오라클 9iR2 이전에도 가능했지만 9iR2에서는 테이블의 컬럼 명 또는 제약조건의 이름을 변경하는 것이 가능해 졌습니다. 실제 예제를 통해 확인해 보자구요~~ SQL> create table test ( 2 c1 varchar2(4) not null, 3 c2 number(10) not null 4 ); 테이블이 생성되었습니다. 프라이머리 키를 추가 합니다. 이때 C1컬럼에 대해 인덱스가 생성 됩니다. SQL> alter table test add (constraint pk_test 2 primary key (c1)); 테이블이 변경되었습니다. SQL> desc test; 이름 널? 유형 ----------------------------------------- -------- -------------- C1 NOT NULL VARCHAR2(4) C2 NOT NULL NUMBER(10) 사용자의 제약 조건을 확인 할 수 있는 USER_CONSTRAINTS VIEW를 통해 TEST 테이블에 제약조건의 타입이 ‘P’ 인것 즉 Primary Key인 제약조건을 검색 합니다. 제약조건에는 NOT NULL, UNIQUE, CHECK, PRIMARY KEY등 테이블의 컬럼에 제약을 가하는 조건을 말합니다. SQL> select constraint_name 2 from user_constraints 3 where table_name = 'TEST' 4 and constraint_type = 'P'; CONSTRAINT_NAME ------------------------------ PK_TEST 이번에는 TEST 테이블에 생성되어 있는 인덱스를 확인 합니다. 위에서 C1 컬럼을 Primary Key로 설정하여 저절로 이 컬럼에 대한 인덱스가 생성되어 있습니다. SQL> select index_name, 2 column_name 3 from user_ind_columns 4 where table_name = 'TEST'; INDEX_NAME COLUMN_NAME ------------------------------------ PK_TEST C1 우선 테이블의 이름을 바꾸어 봅니다. 이 기능은 오라클의 이전 버전에서도 되는 기능 입니다… SQL> alter table test rename to test1; 테이블이 변경되었습니다. 이번에는 컬럼명을 바꾸어 보죠^^ SQL> alter table test1 rename column c1 to code; 테이블이 변경되었습니다. Primary Ket 제약 조건의 이름을 변경 합니다. SQL> alter table test1 rename constraint pk_test to pk_test1; 테이블이 변경되었습니다. 이번에는 Primary Key에 걸린 인덱스의 이름을 바꿉니다. SQL> alter index pk_test rename to pk_test1; 인덱스가 변경되었습니다. 위에서 변경한 내역에 대해 확인해 보겠습니다… SQL> select constraint_name 2 from user_constraints 3 where table_name = 'TEST1' 4 and constraint_type = 'P'; CONSTRAINT_NAME ------------------------------ PK_TEST1 SQL> select index_name, 2 column_name 3 from user_ind_columns 4 where table_name = 'TEST1'; INDEX_NAME COLUMN_NAME --------------------------------------------- PK_TEST1 CODE [출처] 오라클자바커뮤니티 - http://www.oraclejavanew.kr/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=107 [개강확정강좌]오라클자바커뮤니티에서 운영하는 개발자 전문교육 ,개인80%환급(www.onjprogramming.co.kr) [주간] [11/4]Spring3.X, MyBatis, Hibernate실무과정 [11/6]SQL초보에서실전전문가까지 [평일야간] [11/1]C#,ASP.NET마스터 [11/5]iPhone 하이브리드 앱 개발 실무과정 [11/7]JAVA&WEB프레임워크실무과정 [11/8]Spring3.X, MyBatis, Hibernate실무과정 [주말] [11/2]C#,ASP.NET마스터 [11/2]Spring3.X, MyBatis, Hibernate실무과정 [11/2]JAVA&WEB프레임워크실무과정 [11/9]안드로이드개발자과정 JAVA ORACLE iPhone/Android .NET 표준웹/HTML5 채용/취업무료교육 초보자(재학생)코스 Spring3.X, MyBatis, Hibernate실무과정 총 5일 35시간 11-04 JAVA&WEB프레임워크실무과정 총 33일 99시간 11-07 Spring3.X, MyBatis, Hibernate실무과정 총 12일 36시간 11-08 자바초보에서안드로이드까지 총 18일 54시간 11-15 Spring3.X, MyBatis, Hibernate실무과정 총 5일 35시간 11-02 JAVA&WEB프레임워크실무과정 총 14일 98시간 11-02 SQL초보에서실전전문가까지 총 8일 56시간 11-06 고급개발자를위한 오라클힌트&SQL튜닝 총 10일 30시간 11-08 SQL초보에서실전전문가까지 총 18일 54시간 11-13 고급개발자를위한 오라클힌트&SQL튜닝 총 4일 32시간 11-09 SQL초보에서실전전문가까지 총 8일 56시간 11-10

Column Name & Constraints 이름 변경 예

테이블이나 인덱스의 이름을 변경하는 것은 오라클 9iR2 이전에도 가능했지만 9iR2에서는 테이블의 컬럼 명 또는 제약조건의 이름을 변경하는 것이 가능해 졌습니다.

실제 예제를 통해 확인해 보자구요~~


SQL> create table test (
  2  c1 varchar2(4) not null,
  3  c2 number(10)  not null
  4  );

테이블이 생성되었습니다.

프라이머리 키를 추가 합니다. 이때 C1컬럼에 대해 인덱스가 생성 됩니다.

SQL> alter table test add (constraint pk_test
  2                        primary key (c1));

테이블이 변경되었습니다.

SQL> desc test;
 이름                                      널?      유형
 ----------------------------------------- -------- --------------

 C1                                        NOT NULL VARCHAR2(4)
 C2                                        NOT NULL NUMBER(10)

사용자의 제약 조건을 확인 할  수 있는 USER_CONSTRAINTS VIEW를 통해 TEST 테이블에 제약조건의 타입이 ‘P’ 인것 즉 Primary Key인 제약조건을 검색 합니다. 제약조건에는 NOT NULL, UNIQUE, CHECK, PRIMARY KEY등 테이블의 컬럼에 제약을 가하는 조건을 말합니다.

SQL> select constraint_name
  2  from  user_constraints
  3  where  table_name = 'TEST'
  4  and    constraint_type = 'P';

CONSTRAINT_NAME
------------------------------
PK_TEST

이번에는 TEST 테이블에 생성되어 있는 인덱스를 확인 합니다. 위에서 C1 컬럼을 Primary Key로 설정하여 저절로 이 컬럼에 대한 인덱스가 생성되어 있습니다.

SQL> select index_name,
  2        column_name
  3  from  user_ind_columns
  4  where  table_name = 'TEST';

INDEX_NAME          COLUMN_NAME
------------------------------------

PK_TEST                    C1

우선 테이블의 이름을 바꾸어 봅니다. 이 기능은 오라클의 이전 버전에서도 되는 기능 입니다…

SQL> alter table test rename to test1;

테이블이 변경되었습니다.

이번에는 컬럼명을 바꾸어 보죠^^

SQL> alter table test1 rename column c1 to code;

테이블이 변경되었습니다.

Primary Ket 제약 조건의 이름을 변경 합니다.

SQL> alter table test1 rename constraint pk_test to pk_test1;

테이블이 변경되었습니다.

이번에는 Primary Key에 걸린 인덱스의 이름을 바꿉니다.

SQL> alter index pk_test rename to pk_test1;

인덱스가 변경되었습니다.

위에서 변경한 내역에 대해 확인해 보겠습니다…

SQL> select constraint_name
  2  from  user_constraints
  3  where  table_name = 'TEST1'
  4  and    constraint_type = 'P';

CONSTRAINT_NAME
------------------------------
PK_TEST1

SQL> select index_name,
  2        column_name
  3  from  user_ind_columns
  4  where  table_name = 'TEST1';

INDEX_NAME            COLUMN_NAME
---------------------------------------------

PK_TEST1                    CODE
SQL초보에서실전전문가까지 총 8일 56시간   11-06
고급개발자를위한 오라클힌트&SQL튜닝 총 10일 30시간   11-08
SQL초보에서실전전문가까지 총 18일 54시간   11-13
고급개발자를위한 오라클힌트&SQL튜닝 총 4일 32시간   11-09
SQL초보에서실전전문가까지 총 8일 56시간   11-10


작성자: 시간: 댓글 없음:
라벨: , , , , , , ,

2013년 10월 17일 목요일

오라클 물리적 구조

Oracle 물리적 구조 oracle physical structure

------------------- 
물리적 DataBase구조 
-------------------- 
oracle 설치된 폴다에 가보면 oradata 폴더에 대부분파일이 위치한다,
확인해 보자.

A. DataFile 
- 모든 Oracle DataBAse는 하나이상의 DataFile을 가지며, DB의 영역이 부족할 때 자동으로 
확장할 수 있는 기능이 있다. 
- 하나이상의 DataFile이 TableSpace를 형성한다. 
- 수정된 Data나 새로운 Data는 파일에 즉시 Write할 필요가 없다.즉 디스크 Access량을 줄이고 
성능을 향상시키려면 Data를 메모리에 저장했다가 DBWR BackGround Process가 한번에 디스크에 
저장한다. 
B. Redo Log File 
- Oracle DB는 2개 이상의 Redo Log File을 가진다. 
- Redo Log의 주기능은 변경사항을 저장,이미 수정된 Data가 장애 때문에 DataFile에 기록되지 
못했다면 수정된 부분이 Redo Log에 있으므로 수행한 작업을 손실하지는 않는다. 
C.  Control File 
- Control File에는 DB이름, DataFile과 Redo Log File의 위치,DB생성시간등이 기록되어 있다. 
- Oracle은 Instance가 시작될때마다 DataBase와 Redo Log File을 지정한다. 새 DataFile이나 
Redo Log File이 생성되는 경우에는 Oracle은 Control File을 자동으로 수정한다. 

D, 파라미터파일
     -데이터베이스 이름
   - SGA메모리 구조와 할당크기
   - 컨트롤 파일명과 위치
   - 아카이브 파일정보
   - 언두세그먼트 정보

오라클자바커뮤니티에서 설립한 개발자교육6년차 오엔제이프로그래밍 실무교육센터(오라클SQL,튜닝,힌트,자바프레임워크,안드로이드,아이폰,닷넷 실무개발강의)  
SQL초보에서실전전문가까지 총8일56시간 10-15
SQL초보에서실전전문가까지 총18일54시간 10-17
PL/SQL,오라클힌트,SQL튜닝,사례연구 총10일30시간 10-25
PL/SQL,오라클힌트,SQL튜닝,사례연구 총4일32시간 10-19
SQL초보에서실전전문가까지 총8일56시간 11-10



작성자: 시간: 댓글 없음:
라벨: , , , , , ,

2013년 8월 3일 토요일

[Oracle SQL Explain Plan]ORACLE HINT 강좌 실행계획 SQL 연산(HASH ANTI-JOIN)

실행계획 SQL 연산(HASH ANTI-JOIN

구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr

ANTI 조인은 조인의 대상이 되는 테이블과 일치하지 않는 데이터를 추출하는 연산 입니다. SQL연산에서 NOT IN, NOT EXISTS, MINUS등이 해당되며 이러한 안티 조인은 MERGE ANTI-JOIN or HASH ANTI_JOIN으로 풀리도록 할 수 있습니다.

아래의 Query는 동일한 의미를 가지는 질의 입니다. 확인해 보세요~

create table myemp1
(empno number not null primary key,
 ename varchar2(100),
 deptno number,
 addr   varchar2(100),
 sal    number
 )

-- 실습을 위해 myemp1을 1000만건 만들자.
DECLARE
          v_c NUMBER := 1;
BEGIN

          WHILE (v_c <= 10000000) LOOP
                insert into myemp1 values ( v_c, '홍길동'||v_c, mod(v_c, 5), '서울'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
                insert into myemp1 values ( v_c, '다길동'||v_c, mod(v_c, 5), '부산'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
                insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '대구'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
                insert into myemp1 values ( v_c, '나길동'||v_c, mod(v_c, 5), '광주'||v_c, mod(v_c, 1000000));
                v_c := v_c + 1;
          END LOOP;
          commit;
END;


create table myemp1_old
as select * from myemp1 where rownum < 1000000


  

 -- 통계정보 셍성
    exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'MYDEPT1_OLD')
   
    exec DBMS_STATS.GATHER_TABLE_STATS(USER, 'MYDEPT1')




별다른 인덱스가 없는 상태에서 실행해 보자. 37초 걸린다.
오라클 11g에서 기본적으로 merge anti joinm으로 실행계획을 잡는다.


SQL> select count(e1.ename) 
  2   from myemp1 e1
  3   where (ename, sal) not in (select ename, sal
  4                                from myemp1_old e2);

COUNT(E1.ENAME)--28
-------------------
            9000001

경   과: 00:00:37.87

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT     |            |     1 |    82 |       | 90492  
|   1 |  SORT AGGREGATE      |            |     1 |    82 |       |           
|   2 |   MERGE JOIN ANTI NA |            |    10M|   782M|       | 90492  
|   3 |    SORT JOIN         |            |    10M|   162M|   536M| 72263  
|   4 |     TABLE ACCESS FULL| MYEMP1     |    10M|   162M|       | 16961  
|*  5 |    SORT UNIQUE       |            |  1070K|    66M|   156M| 18229  
|   6 |     TABLE ACCESS FULL| MYEMP1_OLD |  1070K|    66M|       | 




이번에는 HASH ANTI JOIN으로 힌트를 주고 실행하자. ANTI JOIN 일때는 where절의 not in 출현 컬럼에 대해 is not null 조건을 주도록 하자.

SQL> select
           count(e1.ename)
  from myemp1 e1
  where (ename, sal) not in (select
                                    ename, sal
                               from myemp1_old e2
                               where ename is not null
                                 and sal is not null)
 and ename is not null
  and    sal is not null
 /

실행해 보면 수행시간이 절반 이상으로 준다. MERGE ANTI JOIN 보다 HASH ANTI JOIN 성능이 낫다.

COUNT(E1.ENAME)
---------------
        9000001

경   과: 00:00:14.92

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    82 |       | 36297  
|   1 |  SORT AGGREGATE       |            |     1 |    82 |       |
|*  2 |   HASH JOIN RIGHT ANTI|            |    10M|   782M|    78M| 36297  
|*  3 |    TABLE ACCESS FULL  | MYEMP1_OLD |  1070K|    66M|       | 
|*  4 |    TABLE ACCESS FULL  | MYEMP1     |    10M|   162M|       |



SQL> SELECT count(ename)
  2                FROM   MYEMP1 E
  3  WHERE  NOT EXISTS (SELECT  1
  4                     FROM MYEMP1_OLD EO
  5                     WHERE  EO.ENAME = E.ENAME
  6                     AND     EO.SAL    = E.SAL)
  7  and ename is not null
  8  and   sal is not null;

COUNT(ENAME)
------------
     9000001

경   과: 00:00:11.56

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    82 |       | 36295  
|   1 |  SORT AGGREGATE       |            |     1 |    82 |       |
|*  2 |   HASH JOIN RIGHT ANTI|            |    10M|   782M|    78M| 36295  
|   3 |    TABLE ACCESS FULL  | MYEMP1_OLD |  1070K|    66M|       | 
|*  4 |    TABLE ACCESS FULL  | MYEMP1     |    10M|   162M|       |



이번에는 HASH_AJ 힌트를 사용해 보자.
수행 시간은 대략 비슷하다.
SQL> SELECT count(ename)
  2                FROM   MYEMP1 E
  3  WHERE  NOT EXISTS (SELECT  /*+ hash_aj */1
  4                     FROM MYEMP1_OLD EO
  5                     WHERE  EO.ENAME = E.ENAME
  6                     AND     EO.SAL    = E.SAL)
  7  and ename is not null
  8  and   sal is not null;

COUNT(ENAME)
------------
     9000001

경   과: 00:00:11.00

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    82 |       | 36295  
|   1 |  SORT AGGREGATE       |            |     1 |    82 |       |
|*  2 |   HASH JOIN RIGHT ANTI|            |    10M|   782M|    78M| 36295  
|   3 |    TABLE ACCESS FULL  | MYEMP1_OLD |  1070K|    66M|       | 
|*  4 |    TABLE ACCESS FULL  | MYEMP1     |    10M|   162M|       |



이번에는 MINUS로 풀어 보자.

SQL> with a as (
  2      select  ename, sal
  3      from myemp1
  4      minus
  5      select  ename, sal
  6      from myemp1_old
  7   )
  8  select count(ename) from a  ;

COUNT(ENAME)
------------
     9000001

경   과: 00:00:30.48

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT      |            |     1 |    52 |       | 90492  
|   1 |  SORT AGGREGATE       |            |     1 |    52 |       |
|   2 |   VIEW                |            |    10M|   495M|       | 90492  
|   3 |    MINUS              |            |       |       |       |
|   4 |     SORT UNIQUE       |            |    10M|   162M|   268M| 72263  
|   5 |      TABLE ACCESS FULL| MYEMP1     |    10M|   162M|       |
|   6 |     SORT UNIQUE       |            |  1070K|    66M|    78M| 18229  
|   7 |      TABLE ACCESS FULL| MYEMP1_OLD |  1070K|    66M|       | 


HASH ANTI JOIN으로 풀 수 있는 것은 NOT IN을 포함하고 있는 첫 번째 질의에서 가장 좋은 성능을 보이며 NOT IN의 비교 대상이 되는 컬럼은 NOT NULL로 서브쿼리까지 명시해 주어야 합니다. 물론 HASH_AJ 라는 힌트 구문도 사용해야 하구요~

HSH ANTI JOIN으로 풀 경우 성능이 향상되므로 위 문장과 같이 한 테이블에 존재하지 않는 로우만 추출하는 경우엔 HASH ANTI JOIN이  되도록 힌트를 사용하는 것이 유리합니다.


이번에는 조금 더 개선을 해서 EMPTEST TABLE의  EMPNO, ENAME, SAL 컬럼으로 비트맵 인덱스를 구성해서 쿼리를 해 보자.

create bitmap index idx_bitmap_empno_ename_sal on emptiest (empno, ename, sal)

select   /*+ index(idx_bitmap_empno_ename_sal e1) */
          count(e1.ename)  
 from emptest e1
 where (ename, sal) not in (select
                                   ename, sal
                              from emptest_old e2
                              where ename is not null
                                and sal is not null)
 and    ename is not null
 and    sal is not null


작성자: 시간: 댓글 없음:
라벨: , , , , , , ,

Explain Plan - 오라클힌트, Oracle Hint-실행계획 SQL연산(INLIST ITERATOR)

실행계획 SQL연산(INLIST ITERATOR)

구로디지털 오엔제이프로그래밍실무교육센터
www.onjprogramming.co.kr


인덱스 컬럼이 IN-LIST구에 나타나는 경우의 ROW 연산 입니다. INLIST ITERATOR는 IN-LIST의 인수 만큼 반복연산을 수행 합니다.

SQL> desc emptest;
 이름                                      널?      유형
 ------------------ -------- --------------------------
 EMPNO                                              NUMBER
 DEPTNO                                             NUMBER
 ENAME                                              VARCHAR2(46)
 ADDR                                               VARCHAR2(44)
 SAL                                                NUMBER

SQL> select count(*)  from emptest;

  COUNT(*)
----------
   2500000

SQL> select index_name, table_name from user_indexes 
where table_name like 'EMPTEST';

INDEX_NAME                     TABLE_NAME                     
------------------------------ ------------------------------
IDX_EMPTEST_ADDR               EMPTEST                      
IDX_EMPTEST_DEPTNO             EMPTEST

ADDR 컬럼으로 인덱스가 있다. Addr 컬럼을 이용해 보자.

SQL> select empno, ename
  2  from emptest
  3  where addr in ('서울1','서울10001');

     EMPNO ENAME
---------- ----------------------------------------------
         1 홍길동1
     10001 홍길동10001

경   과: 00:00:00.00

Execution Plan
----------------------------------------------------------
|   0 | SELECT STATEMENT
|   1 |  INLIST ITERATOR            
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPTEST
|*  3 |    INDEX RANGE SCAN          | IDX_EMPTEST_ADDR

위의 는 INLIST ITERATOR를 나타내게 하기 위해 넣은 힌트 구문이며 힌트 구문을 사용하지 않는 다면 아래와 같은 실행 계획이 수립됩니다.


SQL> select  empno, ename
  2  from emptest
  3  where addr in ('서울1','서울10003');

     EMPNO ENAME
---------- ----------------------------------------------
         1 홍길동1
     10003 홍길동10003

경   과: 00:00:00.00


--------------------------------------------------------------------
|   0 | SELECT STATEMENT
|   1 |  INLIST ITERATOR            
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPTEST
|*  3 |    INDEX RANGE SCAN          | IDX_EMPTEST_ADDR |  
작성자: 시간: 댓글 없음:
라벨: , , , , , , , , ,
피드 구독하기: 글 (Atom)