'제5 외국어 영역'에 해당되는 글 23건

  1. 2007.12.12 CONNECT BY
  2. 2007.12.05 JDBC 액세스의 가속화
  3. 2007.11.21 Statement vs. PreparedStatement
제5 외국어 영역2007.12.12 11:03

제목

오라클 고급쿼리(1) - CONNECT BY

작성자

DEV.WON ( http://blog.naver.com/jadin1 )

작성일

2007-05-31

배포

PUBLIC (, 출처는 밝혀 주세요)



편의상  짧은 어투 양해바라며...



후... 강좌 올만에 올리는군요...그동안 블로그 관리에 소홀히 했는데 이제부턴 열심히 해야겠습니다.





오라클에서 제공하는 쿼리중 좀더 고급스러운 쿼리를 사용해 보도록하자.


처음으로 살펴볼 쿼리는 이른바 계층적 쿼리이다.


CONNECT BY 절을 사용하는데 이 계층적 쿼리는 오라클만이 가능 기능으로써,


데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.


scott의 emp 테이블을 기억하는가?


그 사원테이블에는 사원번호 와 함께 사원의 메니저의 번호(fk) 를 참조하는 외부키 컬럼이 있다.


바로 self join을 사용하는 가장 흔한 예라 하겠다.


필자는 emp테이블로 설명을 하려 했으나, 역시나 지겨운 관계로 새로 테이블을 만들었다.


쇼핑몰이나 기타 솔루션에서 흔히 보게 되는 카테고리 정보 테이블을 예로 들면서 강좌를 진행하도록 하겠다.




우선 DB 데이블의 schema부터 보도록하자.

1. 카테고리 정보 테이블(PRT_CATE)


DDL

-- Create table
create table PRT_CATE
(
  NO            NUMBER not null,
  CATE_NAME     VARCHAR2(100) not null,
  BASE_CATE_NO  NUMBER,
  CATE_MEMO     VARCHAR2(500),
  CATE_VIEW_CNT NUMBER default 0
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns
comment on column PRT_CATE.NO
  is 'pk';
comment on column PRT_CATE.BASE_CATE_NO
  is '상위 카테고리 의 FK';
comment on column PRT_CATE.CATE_MEMO
  is '카테고리의 설명.';
comment on column PRT_CATE.CATE_VIEW_CNT
  is '카테고리 열람 횟수';
-- Create/Recreate primary, unique and foreign key constraints
alter table PRT_CATE
  add constraint PRT_CATE_PK primary key (NO)
  using index
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );


 








2. 상품 테이블(PRT)

-- Create table
create table PRT
(
  NO          NUMBER not null,
  PRT_NAME    VARCHAR2(100) not null,
  PRT_PRICE   NUMBER not null,
  PRT_CATE_NO NUMBER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the columns
comment on column PRT.NO
  is 'PK';
comment on column PRT.PRT_NAME
  is 'NAME';
comment on column PRT.PRT_PRICE
  is '가격';
comment on column PRT.PRT_CATE_NO
  is '상품이 속한 카테고리 FK';
-- Create/Recreate primary, unique and foreign key constraints
alter table PRT
  add constraint PRT_PK primary key (NO)
  using index
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );









3. R-R





역시나 필자의 귀차니즘에 의한 엄청나게 허술한 그림이 돋보인다..(예전의 열정은 사라졌다. ㅎ)




위의 schema를 쭉 본 독자들이라면 거의 모두다 파악이 될것이다.


주목할 점은 바로 저, self join을 해야할 PRT_CATE테이블이다.


자 독자라면 여기서 카테고리의 계층 구조를 쿼리로 나타내어 보라.

(8번PK의 DB카테고리의 계층구조는 컴퓨터 > 소프트웨어 > DB 이다.)


우선 필자는 이렇게 처리하였다.


---------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE CATE_LISTING
       (p_cateName VARCHAR2, p_level NUMBER DEFAULT
0)
AS
BEGIN
    
--PRINT
     DBMS_OUTPUT.put_line(lpad(
' ',p_level * 3,' ') || p_cateName);

     FOR CATE_ROW IN         
             (
             SELECT * FROM PRT_CATE C
             WHERE C.BASE_CATE_NO IN (
                                       SELECT C2.NO FROM PRT_CATE C2
                                       WHERE C2.CATE_NAME = p_cateName        
                                     ) 
             ORDER BY C.NO
             )
         LOOP
      
      
-- PROCEDURE CALL   
       CATE_LISTING(CATE_ROW.CATE_NAME,p_level +
1);
    
     END LOOP;   

END ;      


-- EXECUTE
CALL CATE_LISTING(
'컴퓨터',0);

---------------------------------------------------------------------------------------------


결과>

--------------

컴퓨터
   소프트웨어
      DB
      Tools
   주변제품
---------------


재귀 프로시져 기법으로 처리하였다.


커서등을 사용하는것도 하나의 방법이 될수 있겠다.








하지만, 너무 복잡하지않은가?


자 이쯤에서 오라클이 자랑하는(?) 기능중 계층적 쿼리를 사용해 보도록하자.


필자는 똑같은 결과를 이렇게 바꾸었다.


---------------------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ')  || C.CATE_NAME CATE_HIER
FROM PRT_CATE C
START WITH C.CATE_NAME =
'컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

---------------------------------------------------------------------------------------------





결과>




오, 놀랍지않은가? 엄청나게 코드가 줄었다.(그렇다면 퍼포먼스는?? 오라클 도큐먼트를 살펴보길 바란다.)


우선 START WITH 절부터 보도록 하자.


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


일종의 WHERE절이라 생각해도 좋다.


      START WITH <조건>




필자는 예제에서

START WITH C.CATE_NAME = '컴퓨터'

라고 작성하였다.


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


START WITH절은  기본적으로 WHERE절에서 사용가능한 sql을 모두 사용할수 있다.


이를 테면

---------------------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ')  || C.CATE_NAME CATE_HIER
FROM PRT_CATE C
--START WITH C.CATE_NAME = '컴퓨터'
START WITH C.CATE_NAME IN
      (SELECT CATE_NAME FROM PRT_CATE WHERE CATE_NAME =
'컴퓨터' OR CATE_NAME = '가전제품')     
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

--------------------------------------------------------------------------------

와 같이 바꿀수도 있다.

 

이때 주의할점은 아래의 결과값과 같이 OR 연산 에 의해 두개의 레코드로 부터 계층쿼리가 시작이 되므로

 

결과값도 아래와 같이 두세트로 나온다는것에 주의하기 바란다.

(사실 이러한 경우때문에 가능하면 명료하고 구체적인 , 결과 레코드가 적은 조건을 사용하여야 쿼리 퍼포먼스가 향상된다. )

 

 

결과값>



다음은


CONNECT BY PRIOR 절을 살펴보도록하자.




CONNECT BY 절은 각각의 행들이 어떻게 연결되어야하는지 (여기서는 JOIN을 뜻한다.) 정보를 작성한다.


무슨말이냐하면, 계층적 구조 에서 각 행의 연결 관계를 설정하는것인데...


사원테이블의 사원번호와 , 사원테이블의 사수번호가 여기에 해당된다 하겠다.




그렇다면 어떤 컬럼과 어떤 컬럼이 상위이고 하위인지 이러한 계층정보는 어떻게 설정하는 것일까?


바로 이때 사용하는 키워드가 PRIOR 이다.



필자가 작성한 예제를 보면

CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

와 같이 작성하였는데..

 

우선 PRIOR 키워드를 빼고 생각해보자.

 

CONNECT BY

     C.NO = C.BASE_CATE_NO;

 

이와 같이 되는데 이때 NO 컬럼과 BASE_CATE_NO이 계층적 연결 관계에 있다라고 설정하는것이다

(SELF JOIN이라 생각해도 좋다.)

 

이제 PRIOR를 생각해보자.

CONNECT BY

     PRIOR C.NO = C.BASE_CATE_NO;

 

 

 

 

OUTER JOIN을 생각해보라.

C.NO (+) = C.BASE_CATE_NO; 와 같이, PRIOR 키워드의 위치에 집중하자.

 

 

바로 C.NO 쪽에 위치하고있다.!

 

 

 

 

그렇다면 어느쪽 컬럼에 PRIOR가 위치한다는것은 무엇을 의미할까?

 

이것을 말로 설명하기가 매우 어렵다. 말주변의 바닥... ㅜㅜ;;

 

"NO컬럼을 참조하는 BASE_CATE_NO 컬럼이 속한 레코드를 모두 찾아라" 정도로 이해하면 될듯싶다.

 

 

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

 

하위 카테고리를 찾을 수 있게 된것이다.

(PRIOR 키워드의 위치를 바꾸어서 실습해보면 이해에 도움이 된다.)

 

 

 

 

자 이쯤에서 다음으로 넘어가자.

 

 

 

 

 

 

 

 

알면 유용한 키워드 LEVEL !

 

필자는 위의 예제에서 LEVEL 키워드 사용예를 위해 LEVEL키워드를 추가하였다.

 

--------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ')  || C.CATE_NAME CATE_HIER , LEVEL CATE_LEVEL
FROM PRT_CATE C
START WITH C.CATE_NAME =
'컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;

--------------------------------------------------------------------------------

 

 

결과값>





눈치빠른 독자라면 LEVEL키워드의 쓰임을 알것이다.


LEVEL은 오라클에서 실행되는 모든 쿼리내에서 ROWNUM과 더불어 가상-컬럼 이라 할수 있는데,


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



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




자, LEVEL키워드를 어디에 사용할까??


예를들면 2단계까지의 카테고리 정보만을 출력하라 라는 식의 요구사항일때 유용하다.


예상되는 쿼리는


WHERE LEVEL < 3 일것같지만 사실 LEVEL키워드는 CONNECT BY절에 위치하여야한다.



--------------------------------------------------------------------------------

SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ')  || C.CATE_NAME CATE_HIER , LEVEL CATE_LEVEL
FROM PRT_CATE C
START WITH C.CATE_NAME =
'컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO AND LEVEL <
3;

--------------------------------------------------------------------------------

 

 


결과값>


이로써 오라클이 제공하는 고급쿼리중 CONNECT BY 절에 대해 마칠까 한다.



부족한 강좌 끝까지 읽어주셔서 감사합니다.

Posted by is윤군
TAG Oracle

댓글을 달아 주세요

제5 외국어 영역2007.12.05 12:35



JDBC 액세스의 가속화
Step by Step: Speeding Up JDBC Access

저자: Jason Price

자동 커밋을 비활성화하고 업데이트 일괄 처리(batch)를 사용하십시오.

Java API(application-programming interface)가 데이타베이스에 대한 연결을 제공하는 첫 번째 API로서 Oracle 및 기타 데이타베이스를 위한 애플리케이션을 생성할 때 많은 Java 개발자들이 JDBC(Java Database Connectivity)를 사용하고 있습니다. 이 기사에서 애플리케이션 성능을 현저히 향상시킬 수 있는 두 가지 기본 JDBC 기술에 대해 살펴볼 것입니다. 또한 첨부한 예제 코드를 다운로드한 후 컴파일 및 실행하면 이러한 기술이 어떻게 성능을 향상시키는 지도 살펴보겠습니다.

예제를 실행하기 전에 테스트 데이타를 위한 performance 테이블을 생성해야 합니다. 여기서는 이 테이블을 익숙한 scott 스키마로 생성했습니다. 따라서 여러분이 다른 스키마를 사용하는 경우에는 예제 애플리케이션에서 스키마 이름을 반드시 변경해야 합니다. 또한 Oracle 데이타베이스 서비스 이름도 변경해야 합니다. 여기서는 기본 ORCL 서비스 이름을 사용하고 로컬로 실행합니다. (비록 이것이 데이타베이스 연결 문자열 정보를 하드 코딩하는 데 있어 좋은 실습은 아닐지라도, 이 예제를 사용하여 실습해 보았더니 테스트 목적으로는 괜찮았습니다.)

이 SQL 문을 사용하여 성능 테이블을 생성합니다.

CREATE TABLE performance ( int_value INTEGER, varchar2_value VARCHAR2(10) ); 

여기에서 볼 수 있듯이 performance 테이블은 INTEGER 열(int_value)과 VARCHAR2 열(varchar2_value)을 포함하고 있습니다. 이 열들은 예제 프로그램이 생성하는 테스트 데이타로 채워지게 되며, 시작 및 종료 시간을 캡처하고 각 실행에서 둘의 차이를 계산합니다.

자동 커밋 모드를 비활성화하여 시작해 봅시다.

자동 커밋의 기능 및 자동 커밋을 비활성화해야 하는 이유

기본적으로 JDBC의 자동 커밋 모드는 활성화되어 있습니다. 즉, 데이타베이스로 전송되는 모든 SQL 문은 자동으로 데이타베이스에 커밋됩니다. 데이타베이스에 커밋된다는 것은 INSERT, UPDATE, DELETE SQL 문이 실행된 후 이러한 변경 사항이 데이타베이스에 영구히 기록된다는 것을 의미합니다. 애플리케이션 디자인 관점에서 봤을 때 자동 커밋 모드를 활성화하는 것은 이치에 맞지 않습니다. 왜냐하면 애플리케이션의 비즈니스 로직 수준에서 트랜잭션은 빈번히 하나의 INSERT, UPDATE, DELETE 문이 아닌 여러 개의 SQL 문으로 구성되기 때문입니다. 따라서 여러분은 개발자로서 트랜잭션이 구별되는 방법과 트랜잭션이 어떤 SQL 문을 포함해야 하는지에 대해 생각해 보아야 합니다.

예를 들어 트랜잭션이 한 계좌에서 다른 계좌로의 자금 이체에 영향을 주는 두 개의 UPDATE 문으로 구성되어 있는 경우, 이 자금 이체는 한 UPDATE 문을 사용하여 당좌 예금 계좌에서 예금을 빼내고 다른 UPDATE 문을 사용하여 보통 예금 계좌에 예금을 더합니다. 예금이 손실될 수도 있기 때문에 UPDATE 문 중 하나에서 오류가 발생하면, 커밋을 사용하여 둘 다 변경되지 않도록 하거나 롤백을 사용하여 둘 다 실행이 취소되도록 합니다.

만약 자동 커밋을 활성화한다면, 각 문은 실행되는 대로 커밋되어 데이타베이스가 일관성 없는 상태가 될 것입니다. 따라서 앞의 예에서 예금은 손실됩니다. (트랜잭션과 일관성 요구 사항에 대한 자세한 정보는 Oracle Magazine의 Oracle Developer 섹션의 "JDBC 트랜잭션 최적화" 기사의 "ACID 트랜잭션 속성" 사이드바와 "Java에서의 데이타베이스 트랜잭션 사용 방법"을 참조하십시오.)

여러분은 아마도 애플리케이션에 정의되어 있는 일반적인 트랜잭션이 하나 이상의 SQL 문으로 구성된 경우, 자동 커밋을 활성화하는 것이 이치에 맞지 않다고 말할 것입니다. 또한 자동 커밋을 비활성화하면 성능적 이점도 얻을 수 있습니다.

java.sql.Connection(또는 oracle.jdbc.OracleConnection) 객체에 대해 자동 커밋을 비활성화하기 전에 적절한 Oracle JDBC 드라이버를 등록합니다. 예를 들어,

DriverManager.registerDriver( new oracle.jdbc.OracleDriver() ); 

Next, you create a java.sql.Connection object:

Connection myConnection = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:ORCL", "scott", "tiger" ); 

그 다음, 자동 커밋 모드를 비활성화하려면 Connection 객체의 setAutoCommit() 메소드를 호출합니다. setAutoCommit() 메소드는 부울(true 또는 false) 매개변수를 받아들입니다. false는 우리가 바라는 대로 자동 커밋 모드를 비활성화합니다. 다음은 myConnection Connection 객체에 대해 자동 커밋 모드를 비활성화하는 문입니다.

myConnection.setAutoCommit(false); 

자동 커밋 모드를 활성화한 경우와 비활성화한 경우가 성능에 미치는 영향을 비교하도록 디자인된 애플리케이션을 살펴봅시다.

AutoCommit.java와의 시간 제한 게임

AutoCommit.java 예제 프로그램(목록 1 참조)을 사용하면, 자동 커밋 모드를 비활성화 할 경우에 성능이 향상된다는 주장을 증명할 수 있습니다. 이 예제를 컴파일 및 실행하려면 데이타베이스가 요구하는 대로 호스트 이름, 포트 번호, 데이타베이스 서비스 이름, 스키마 이름 및 암호를 변경해야 합니다. 데이타베이스 연결을 위해 JDBC API를 사용하는 Java 애플리케이션을 사용하면, AutoCommit.java 애플리케이션은 적절한 JDBC 드라이버(이 경우 Oracle Thin 드라이버)를 등록함으로써 시작하고 그런 다음, Connection 객체를 생성하여 데이타베이스에 연결합니다. (여러분은 java.sql.Connection 또는 oracle.jdbc.OracleConnection 객체를 사용할 수 있습니다. java.sql.Connection은 표준 JDBC인데 반해, oracle.jdbc.OracleConnection은 Oracle JDBC 확장이고 더 많은 기능과 더 높은 성능을 제공합니다.)

데이타베이스에 연결한 후에는 SQL 문(목록 1의 3단계)을 위한 문 객체를 생성합니다. 이 지점에서부터 프로그램은 데이타를 캡처하여 두 작업 모드의 성능 차이를 보여 줍니다. performance 테이블이 절단되어(단계 4 참조) 테이블에 존재하는 모든 행이 지워지고 나면, 자동 커밋이 활성화됩니다(Connection 객체(myConnection)에서 autoCommit() 메소드를 호출함). 프로그램은 1,000개의 행을 performance 테이블에 삽입하는 insertRows() 메소드를 호출합니다(SQL INSERT 문으로 구성되는 PreparedStatement 객체를 사용함). 프로그램은 삽입을 수행하는 데 걸린 시간을 1000분의 1초로 표시합니다. 그 다음 자동 커밋은 비활성화되고, performance 테이블이 절단되어 기존 데이타가 지워진 후 또 다른 1,000개의 행이 테이블에 삽입됩니다.

다음은 이 프로그램의 예제 실행입니다.

Enabling auto-commit mode Total time for inserting 1000 rows was 5688 milliseconds Disabling auto-commit mode Total time for inserting 1000 rows was 3515 milliseconds 

위에서 볼 수 있듯이 자동 커밋을 비활성화하면 성능이 현저히 향상됩니다. 또한 자동 커밋을 비활성화하는 것은 일반적으로 바람직한 정책이라고 할 수 있는데, 그 이유는 개발자인 여러분들이 애플리케이션의 비즈니스 로직에 대해 생각해야 하고 트랜잭션이 구별되는 방법을 결정해야 하기 때문입니다. 반면 자동 커밋을 활성화하는 것은 별로 좋지 않은 정책입니다. 왜냐하면 업데이트 일괄 처리(다음 단원에서 배우게 될 내용)를 비롯한 기타 많은 JDBC 성능 조정 기술은 자동 커밋이 비활성화되도록 요구하기 때문입니다.

성능 향상을 위해 업데이트 일괄 처리를 활용

기본적으로 여러분의 애플리케이션은 한 번에 하나씩 SQL 문을 데이타베이스로 보낼 것입니다. 생산 환경에서 애플리케이션은 보통 데이타베이스가 실행되는 컴퓨터와 다른 컴퓨터에서 실행되고, 여러분의 애플리케이션은 각 SQL 문을 네트워크를 통해 데이타베이스로 보냅니다. 데이타베이스는 이 SQL 문을 받아 실행하고 네트워크를 통해 다시 여러분의 애플리케이션으로 결과를 보냅니다. 이러한 전체 프로세스가 바로 라운드 트립(round trip)입니다.

상상할 수 있듯이 라운드 트립에는 상당히 오랜 시간이 걸리며, 한 번에 하나씩 SQL 문을 데이타베이스로 보내는 것은 일반적으로 비효율적입니다. 다행히도 여러분은 많은 SQL INSERT, UPDATE, DELETE 문을 일괄 처리로 묶을 수 있어 여러분의 애플리케이션은 이를 한 번에 데이타베이스로 보내서 실행할 수 있습니다. 따라서 라운드 트립의 횟수와 전체 처리 시간이 줄어 듭니다.

업데이트 일괄 처리는 동일한 기본 SQL INSERT, UPDATE, DELETE 문을 데이타베이스로 보낼 경우 효과가 좋습니다. 상이한 SQL 문을 일괄 처리에 추가할 경우 일괄 처리는 실행을 위해 즉시 데이타베이스로 전송되고, 상이한 SQL 문을 위한 새 일괄 처리가 생성됩니다. 이러한 이유로 인해 여러 가지 많은 SQL 문을 데이타베이스로 보내는 경우에는 업데이트 일괄 처리를 사용하는 것이 불편할 것입니다. 예를 들어 많은 INSERT 문을 데이타베이스로 보내는 경우 일괄 처리는 효과가 상당히 좋습니다. 하지만 INSERT, 뒤이어 UPDATE, 뒤이어 DELETE를 보내는 경우에는 업데이트 일괄 처리로 인한 이점을 얻을 수 없습니다.

Oracle JDBC는 서로 다른 두 가지 업데이트 일괄 처리 모델을 지원합니다.

  • · Java 표준 모델(Oracle 설명서에는 표준 업데이트 일괄 처리로 되어 있고, Sun 설명서에는 일괄 처리 업데이트로 되어 있음)
  • · JDBC에 대한 Oracle 확장을 지원하는 Oracle 특정 모델(Oracle 업데이트 일괄 처리)

대체적으로 Oracle 업데이트 일괄 처리가 표준 업데이트 일괄 처리보다 성능이 우수하다는 것을 알고 있을 것입니다. 그러나 Oracle 및 비 Oracle 데이타베이스 간의 코드 이식성이 중요하다면 표준 업데이트 일괄 처리를 사용해야 합니다. 표준 및 Oracle 업데이트 일괄 처리는 모두 SQL INSERT, UPDATE, DELETE 문과 비 LOB(large object) 데이타 유형에 대해서만 작용하므로 CLOB, BLOB, BFILE, LONG, LONG RAW 데이타는 일괄 처리 모드에 삽입할 수 없습니다. 일괄 처리는 SQL 문이 이러한 데이타 유형 중 한 가지를 포함하고 있으면 자동으로 비활성화됩니다.

여러분의 코드에서 사용하고 있는 객체의 유형은 또한 여러분이 사용해야 하는 업데이트 일괄 처리 모델을 지시합니다. 예를 들어 Oracle 업데이트 일괄 처리는 OraclePreparedStatement 객체와만 작용합니다. OraclePreparedStatement 객체는 더 많은 기능을 가지고 있다는 점을 제외하고는 PreparedStatement 객체와 비슷하여 Oracle 데이타베이스에서 사용할 수 있는 추가 기능을 처리합니다. 일반적으로 OraclePreparedStatement 객체는 또한 성능이 더 뛰어납니다.

여러분은 두 가지 일괄 처리 모델을 혼용할 수 없습니다. 즉, Oracle 업데이트 일괄 처리나 표준 업데이트 일괄 처리를 사용할 수 있지만 이 두 가지를 동시에 사용할 수는 없습니다. 다음 단원에서 각 일괄 처리 모델의 사용 방법에 대해 알아보도록 하겠습니다.

JDBC 액세스의 가속화
Step by Step: Speeding Up JDBC Access

저자: Jason Price

표준 JDBC의 업데이트 일괄 처리 사용 방법

앞에서 말한 것처럼 표준 업데이트 일괄 처리는 표준 JDBC의 일부로서, 비 Oracle 데이타베이스와의 코드 이식성이 중요한 경우에는 이 기술을 사용해야 합니다. 표준 업데이트 일괄 처리를 사용하면 다음과 같은 애플리케이션의 객체 유형이 수행하는 SQL 문을 일괄 처리할 수 있습니다.

Statement
OracleStatement
PreparedStatement
OraclePreparedStatement
CallableStatement
OracleCallableStatement

PreparedStatement 객체는 ?로 표시된 다른 값을 가진 동일한 SQL 문을 실행하는 데 재사용할 수 있는 선행 컴파일된 문입니다. CallableStatements 객체는 데이타베이스의 내장 프로시저입니다.

표준 JDBC에 대한 자세한 내용은 Statement, PreparedStatementCallableStatement 인터페이스에 대한 온라인 Javadocs를 참조하십시오.

Oracle* 객체들(OracleStatement 등)은 JDBC에 대한 Oracle 확장으로서, 여러분은 여전히 이러한 객체들로 표준 업데이트 일괄 처리를 사용할 수 있습니다. (JDBC에 대한 Oracle 확장, OracleStatement, OraclePreparedStatement 및 OracleCallableStatement에 관한 추가 정보는 Oracle9i JDBC Developer's Guide and Reference Release 2 (9.2)를 참조하십시오.

애플리케이션에서 표준 업데이트 일괄 처리를 사용하려면 다음의 다섯 가지 단계를 수행하십시오

1. 자동 커밋 모드를 비활성화합니다.
2. 문 객체를 생성합니다.
3. SQL 문을 일괄 처리에 추가합니다.
4. SQL 문 일괄 처리를 실행합니다.
5. 실행된 SQL 문 일괄 처리 전체를 커밋(또는 롤백)합니다.

1단계: 자동 커밋 모드를 비활성화합니다.

첫 번째 단계는 SQL 문 일괄 처리에 포함되어 있는 각 SQL 문이 일괄 처리가 전송된 후에 즉시 커밋되는 것을 방지하기 위해 자동 커밋을 비활성화 하는 것입니다. 앞에서 언급한 것처럼 여러분은 성능 때문에 자동 커밋을 비활성화하고 싶을 것입니다. 또한 자동 커밋을 비활성화하면 여러분이 일괄 처리를 실행하는 동안 오류가 발생하는 경우에 더 많은 제어를 가지게 되는데, 그 이유는 이미 실행된 일괄 처리에 포함된 문을 커밋할 것인지 롤백할 것인지 선택할 수 있기 때문입니다. 자동 커밋 모드가 활성화되어 있는 경우에는 문이 자동으로 커밋되므로 이러한 수준의 제어를 갖지 못 합니다. (오류 처리에 대한 자세한 내용은 이 기사의 뒷부분에서 설명할 것입니다.)

자동 커밋 모드를 비활성화 하려면 다음과 같이 Connection 객체의 setAutoCommit() 메소드의 부울을 false로 설정하면 됩니다.

myConnection.setAutoCommit(false);

2단계: 문 객체를 생성합니다.

두 번째 단계는 앞에서 열거한 문 유형(PreparedStatement, CallableStatement) 중 하나를 사용하여 적절한 문 객체를 생성하는 것입니다. 이 예는 한 행을 performance 테이블에 추가하기 위해 INSERT 문을 포함하는 PreparedStatement 객체(myPrepStatement)를 생성합니다.

PreparedStatement myPrepStatement =
  myConnection.prepareStatement(
    "INSERT INTO performance " +
    "(int_value, varchar2_value) VALUES (?, ?)"

  );

물음표(?)는 INSERT 문의 VALUES 절에서 값이 어디에 설정되는 지를 지시하는 위치 표시자입니다. 이 예에서는 performance 테이블의 int_value 및 varchar2_value 열에 값을 설정할 것입니다. 이 부분에 대해서는 다음 단계에서 살펴보겠습니다.

3단계: SQL 문을 일괄 처리에 추가합니다.

세 번째 단계는 SQL 문을 일괄 처리에 추가하는 것입니다. 트랜잭션에 있는 SQL 문의 개수에 따라 일괄 처리에 포함시킬 문의 개수를 정할 수 있습니다. 원하는 만큼의 문을 일괄 처리할 수는 있지만, 다수의 문을 일괄 처리하여 데이타베이스를 채워서는 안 됩니다. 5 ~ 30개의 SQL 문을 포함하는 일괄 처리가 가장 적당합니다. 이 예에서는 일괄 처리에 10개의 문이 포함되어 있습니다.

문 객체의 addBatch() 메소드를 사용하여 SQL 문을 일괄 처리에 추가합니다. 먼저 addBatch() 메소드를 호출하면 새 일괄 처리가 생성되고 첫 번째 SQL 문이 그 일괄 처리에 추가됩니다. 또 다른 SQL 문을 일괄 처리에 추가하려면 addBatch()를 다시 호출합니다. 필요한 만큼의 SQL 문을 추가한 다음 일괄 처리를 실행합니다. 일괄 처리를 실행하는 방법은 4단계에서 알 수 있습니다. 일괄 처리를 실행한 후 addBatch()를 다시 호출하면 새로운 일괄 처리가 생성되고, 모든 SQL 문을 실행할 때까지 이 과정이 반복됩니다.

이 아이디어를 구체화하려면 for 루프를 사용하여 문을 일괄 처리에 추가하는 코드를 살펴보아야 합니다. 이 루프는 myPrepStatement의 addBatch() 메소드를 사용하여 10개의 INSERT 문을 일괄 처리에 추가하고, INSERT 문의 두 개의 위치 표시자에 값을 설정합니다.

for (int count = 0; count < 10; count++) {
  myPrepStatement.setInt(1, count);
  myPrepStatement.setString(2, "Test" + count);
  myPrepStatement.executeUpdate();
}

setInt() 메소드는 정수를 위치 표시자에 할당하고, setString() 메소드는 문자열을 위치 표시자에 할당합니다. 이 메소드들은 각각 두 개의 매개변수(myPrepStatement의 위치 표시자의 수치 위치 및 이 위치 표시자의 값)를 받아들입니다. 예에서 setInt()의 첫 번째 매개변수는 1이고 INSERT 문의 int_value 행의 값을 지정하는 첫 번째 위치 표시자에 대응됩니다. 두 번째 매개변수는 count 변수로서, for 루프에서 위치 표시자에 0 ~ 9 값을 설정합니다. 비슷한 방식으로 setString() 메소드는 두 번째 위치 표시자에 "Test0" ~ "Test9" 문자열을 설정합니다. 이 루프의 마지막에서 일괄 처리는 각각 하나의 새로운 행을 performance 테이블에 추가(전체 10개의 행)할 10개의 INSERT 문을 포함하게 됩니다.

4단계: 문 일괄 처리를 실행합니다.

문 객체의 executeBatch() 메소드를 사용하여 문 일괄 처리를 실행합니다. 이 메소드는 문 일괄 처리를 데이타베이스에 보냅니다. 그런 다음 데이타베이스는 일괄 처리의 각 문을 실행하여 int 요소의 배열을 반환합니다.

다음 예에서는 myPrepStatement의 executeBatch() 메소드를 호출하는데, 이는 이전 단계에서 추가된 INSERT 문 일괄 처리를 실행하기 위해 데이타베이스에 보내는 것입니다.

int [] rowsInserted = myPrepStatement.executeBatch();

executeBatch()가 반환한 배열의 각 int 요소는 여러분이 사용하는 문 객체의 유형에 따라 서로 다른 것을 저장합니다. 다양한 문 객체에 대해 이러한 int 요소가 나타내는 것에 대해서는 "일괄 처리 오류 처리하기" 뒷부분의 "" 단원에서 알아볼 것입니다.

5단계: 실행된 문 일괄 처리를 커밋(또는 롤백)합니다.

일괄 처리에 포함된 모든 문을 실행한 후 연결 객체에서 commit() 또는 rollback() 메소드를 호출하여 마지막에는 일괄 처리를 커밋 또는 롤백해야 합니다. 예를 들어 myConnection이라는 이름의 연결 객체를 생성했다고 가정하면 다음과 같이 일괄 처리 전체를 커밋합니다.

myConnection.commit();

이는 4단계에서 실행됐던 10개의 INSERT 문을 커밋하게 됩니다. 일반적으로 Java 코드의 try{} 블록에서 커밋을 작성하고 catch{} 블록에서 롤백을 호출하는데, 이 catch 블록은 예외와 오류를 처리하는 곳입니다. 커밋은 일괄 처리된 문이 모두 오류 없이 실행된다는 것을 가정합니다. 다음 단원에서 배우게 될 내용을 참고하여 애플리케이션에서 오류를 적절하게 처리하십시오.

일괄 처리 오류 처리하기

일괄 처리에 포함된 SQL 문에 대한 오류가 발생하면 java.sql.BatchUpdateException 객체가 발생합니다. 여러분은 코드에서 이 예외 객체를 처리해야 하고 그 다음 BatchUpdateException 객체의 getUpdateCounts() 메소드를 호출해야 합니다.

getUpdateCounts() 메소드는 이전에 설명한 executeBatch() 메소드와 같은 값을 포함하고 있는 int 배열을 반환합니다. 또한 일반적으로 catch 블록에서 롤백을 수행하여 성공적으로 실행된 SQL 문을 실행 취소합니다.

다음의 코드 부분은 BatchUpdateException 객체를 처리하고 이 객체를 위해 getUpdateCounts() 메소드를 호출하는 방법을 보여 줍니다. commit() 메소드는 try 블록 내에서 호출되고 rollback() 메소드는 catch 블록 내에서 호출되어 성공적으로 실행된 SQL 문을 롤백합니다.

try {
  /*
    ... code that executes a batch of SQL statements
    using a statement object ...
  */

  myConnection.commit();
} catch (BatchUpdateException e) {
  int [] updateCounts = e.getUpdateCounts();
  myConnection.rollback();
}

getUpdateCounts() 메소드가 반환한 updateCounts int 배열은 SQL 문 일괄 처리를 수행하기 위해 try 블록에서 사용한 문 객체 유형(prepared 또는 callable)에 따라 여러 가지 값을 가지게 됩니다. 이러한 여러 가지 값들에 대해서는 다음 두 단원에서 설명할 것입니다. 여러분은 일괄 처리에 성공적으로 실행된 문을 롤백할 것인지 결정하기 전에 이러한 값들을 추가 확인으로 사용할 수 있습니다.

PreparedStatement 및 OraclePreparedStatement 객체의 배열 값

try 블록에서 PreparedStatement 또는 OraclePreparedStatement 객체를 사용하는 경우 updateCounts 배열의 int 요소는 일괄 처리의 모든 SQL 문이 성공적으로 실행되었는지를 나타냅니다. JDBC 드라이버가 일괄 처리의 어떤 SQL 문이 실패했는지를 정확하게 확인할 수 없기 때문에 int 요소 값은 일괄 처리의 모든 SQL 문에 적용됩니다.

int 요소가 -2 값을 가지고 있으면 일괄 처리된 모든 SQL 문이 성공적으로 실행된 것이며, int 요소가 -3 값을 가지고 있으면 일괄 처리된 문 중 하나 이상에 문제가 있다는 것을 뜻합니다.

예를 들어 10개의 INSERT 문 일괄 처리가 있는데 9번째 문까지는 성공했으나 10번째 문이 실패했다고 가정해 봅시다. 이 예가 그러한 경우라면 배열의 int 요소 10개 모두가 -3 값을 가지게 되어 하나 이상의 SQL 문에 문제가 있다는 것을 나타냅니다. 일부 SQL 문은 성공적으로 실행되었을 수도 있으므로 성공적으로 실행된 이 SQL 문을 롤백할 것인지 결정하기 전에 -3 값을 보고 추가로 확인할 수 있습니다.

다른 문 객체의 배열 값

try 블록에서 Statement, OracleStatement, CallableStatement, 또는 OracleCallableStatement 객체를 사용하는 경우, updateCounts 배열의 int 요소는 일괄 처리의 각 SQL 문으로부터 영향을 받은 행의 개수를 지정합니다.

예를 들어 2개의 DELETE 문 일괄 처리가 있는데 첫 번째 DELETE 문이 테이블에서 4개의 행을 제거했고 두 번째 DELETE 문이 테이블에서 2개의 행을 제거한 경우가 있다고 가정해 봅시다. 이 경우 updateCounts 배열은 2개의 int 요소를 가지는데 첫 번째 int은 4 값을 가지고 두 번째 int은 2 값을 가집니다. 이는 일괄 처리의 각 DELETE 문으로부터 영향을 받은 행의 개수를 나타내는 것입니다. 그러나 만약 일괄 처리의 두 번째 DELETE 문이 실패했다면 updateCounts 배열은 하나의 int 요소만 가지게 됩니다. 보다시피 예외가 있는 경우에는 updateCounts 배열의 요소의 개수는 1만큼 부족하게 됩니다. 여러분은 성공적으로 실행된 SQL 문을 롤백할 것인지 결정하기 전에 이 사실을 통해 추가로 확인할 수 있습니다.

예제 애플리케이션: StandardUpdateBatching.java

이름에서 짐작할 수 있듯이 StandardUpdateBatching.java 예제 프로그램(목록 2 참조)은 표준 업데이트 일괄 처리 사용 방법을 보여 줍니다. 이 클래스는 AutoCommit.java 예제에서처럼 Oracle JDBC Thin 드라이버를 등록하고, Connection 객체(scott 스키마를 통해)를 생성하며, 자동 커밋 모드를 비활성화합니다. 그런 다음 Statement 객체를 생성하고 performance 테이블을 절단하여 기존 행을 모두 지웁니다. 그리고 나서 애플리케이션은 일괄 처리 없이 1,000개의 행을 performance 테이블에 삽입하고, 삽입에 걸린 시간을 1000분의 1초로 표시합니다. (애플리케이션에 정의되어 있는 insertRows() 메소드가 삽입을 수행합니다.

다음으로 애플리케이션은 performance 테이블을 지우고 이번에는 표준 업데이트 일괄 처리를 사용하여 또 다른 1,000개의 행을 삽입합니다. 표준 업데이트 일괄 처리는 개발자에게 프로그래밍 로직을 기반으로 하여 명시적으로 일괄 처리를 실행할 것을 요구하기 때문에 이 예제에서는 10개의 SQL 문 크기의 일괄 처리를 사용하는 간단한 카운터 메커니즘을 사용하고 있습니다. "실제" 애플리케이션에서는 비즈니스 수준의 트랜잭션에 대응하는 다양한 문으로 구성된 일괄 처리를 생성하는 것과 같은 다른 로직을 사용하게 됩니다. Statement 및 Connection 객체가 닫힌 후에 삽입에 걸린 시간이 다시 표시됩니다. 애플리케이션 실행 출력 결과는 다음과 같습니다.

Inserting 1000 rows without batching
Total time for inserting 1000 rows was 4286 milliseconds
Inserting 1000 rows with standard update batching
Total time for inserting 1000 rows was 521 milliseconds

이 엄청난 결과에 주목하십시오. 일괄 처리를 사용하지 않은 경우보다 표준 업데이트 일괄 처리를 사용한 경우에 성능이 약 8배나 빠른 것으로 나타났습니다.

JDBC 액세스의 가속화
Step by Step: Speeding Up JDBC Access

저자: Jason Price

Oracle 확장의 업데이트 일괄 처리 사용 방법

앞에서 언급했듯이 Oracle 업데이트 일괄 처리는 일반적으로 표준 JDBC 업데이트 일괄 처리보다 빠르고 기능도 더 많습니다. 그렇지만 Oracle 업데이트 일괄 처리는 OraclePreparedStatement 객체와만 사용할 수 있다는 제한 사항이 있습니다. 따라서 JDBC PreparedStatement 객체를 포함한 코드를 사용하여 작업하고 있는 경우 Oracle 업데이트 일괄 처리를 사용하기 전에 JDBC PreparedStatement 객체를 OraclePreparedStatement 객체로 변환해야 합니다.

Oracle 업데이트 일괄 처리는 먼저 일괄 처리 값(일괄 처리에서 허용된 SQL 문의 최대 개수)을 설정한다는 점에서 표준 업데이트 일괄 처리와 다릅니다. addBatch() 또는 비슷한 메소드를 사용하여 SQL 문을 일괄 처리에 추가하는 대신, SQL 문을 자동으로 일괄 처리에 추가하는 executeUpdate() 메소드를 OraclePreparedStatement 객체에서 호출할 수 있습니다. 일괄 처리의 SQL 문의 개수가 정해진 일괄 처리 값에 도달하면 일괄 처리는 자동으로 실행을 위해 데이타베이스로 보내집니다. 예제 애플리케이션에서 일괄 처리 값은 10인데, 이는 10번째 executeUpdate()에서 10개의 INSERT 문 일괄 처리가 데이타베이스로 보내진다는 것을 뜻합니다.

최적의 일괄 처리 크기는 여러 요소에 따라 달라지기는 하지만 최적의 결과를 위한 좋은 법칙은 일괄 처리 값을 5 ~ 30(SQL 문의 개수)으로 두는 것입니다.

Oracle 업데이트 일괄 처리가 대개 표준 업데이트 일괄 처리보다 빠른 이유 중 하나는 일괄 처리 값 설정 때문입니다. JDBC 드라이버는 한 번에 몇 개의 SQL 문이 일괄 처리되는지 알기 때문에 일괄 처리 값을 근거로 하여 최적화할 수 있습니다.

Oracle 업데이트 일괄 처리의 경우 다음과 같이 코딩해야 합니다.

1. 자동 커밋 모드를 비활성화합니다.
2. 일괄 처리 값을 설정합니다.
3. SQL 문을 일괄 처리합니다.
4. 실행된 문을 커밋합니다.

1단계: 자동 커밋 모드를 비활성화합니다.

먼저 다음과 같이 Connection 객체의 setAutoCommit() 메소드의 부울을 false로 설정하여 자동 커밋을 비활성화합니다.

myConnection.setAutoCommit(false);

2단계: 일괄 처리 값을 설정합니다.

OracleConnection 또는 OraclePreparedStatement 객체에 대한 일괄 처리 값을 설정할 수 있습니다. OracleConnection 객체에 대한 일괄 처리 값을 설정하면 OracleConnection과 함께 생성되는 OraclePreparedStatement 객체는 자동으로 같은 일괄 처리 값을 사용하게 됩니다. 만약 OraclePreparedStatement 객체에 대한 일괄 처리 값을 설정할 경우, 이 일괄 처리 값은 이전에 OracleConnection 객체에 대해 설정했던 일괄 처리 값을 무효화합니다. OracleConnection 또는 OraclePreparedStatement 객체에 대한 기본 일괄 처리 값은 1이며, 이는 SQL 문이 일괄 처리되지는 않지만 한 번에 하나씩 데이타베이스로 보내진다는 것을 의미합니다.

다음 두 단원에서는 OracleConnection 객체와 OraclePreparedStatement 객체에 대한 각각의 일괄 처리 값 설정 방법을 살펴보도록 하겠습니다.

OracleConnection 객체에 대한 일괄 처리 값 설정

OracleConnection 객체에 대한 일괄 처리 값을 설정하려면 setDefaultExecuteBatch() 메소드를 사용합니다. 이 메소드는 다음과 같이 일괄 처리 값에 대한 int 매개변수를 받아들입니다.

myOracleConnection.setDefaultExecuteBatch(10);

여기서 myOracleConnection(OracleConnection 객체)는 일괄 처리를 위해 최대 10개의 SQL 문까지 설정됩니다.

또한 다음 예와 같이 기존의 연결 객체를 OracleConnection 객체로 변환한 다음 setDefaultExecuteBatch() 메소드를 호출할 수 있습니다.

((OracleConnection) myConnection).setDefaultExecuteBatch(10);

일반 Java 연결 객체인 myConnection 객체는 OracleConnection로 변환되고, 그 다음 setDefaultExecuteBatch() 메소드를 호출합니다.

OracleConnection 객체의 현재 일괄 처리 값 설정을 확인하려면 getDefaultExecuteBatch() 메소드를 사용합니다.

int batchSize = myOracleConnection.getDefaultExecuteBatch();

getDefaultExecuteBatch() 메소드는 int 값을 반환하는데 이 경우에는 그 값이 10이고, 이는 myOracleConnection 객체에 대한 일괄 처리 값을 10으로 설정했기 때문입니다.

OraclePreparedStatement 객체에 대한 일괄 처리 값 설정

setDefaultExecuteBatch() 메소드를 사용하면 OraclePreparedStatement 객체에 대한 일괄 처리 값을 설정할 수 있습니다. 이 메소드는 일괄 처리 값을 포함하고 있는 int 매개변수를 받아들이며, 이 값은 OracleConnection 객체에 대한 일괄 처리 값을 무효화합니다.

예를 들어 INSERT 문을 포함하고 있는 myOraclePrepStatement라는 이름의 OraclePreparedStatement 객체를 가지고 있다고 가정합시다.

OraclePreparedStatement myOraclePrepStatement =
  (OraclePreparedStatement) myConnection.prepareStatement(
    "INSERT INTO performance " +
    "(int_value, varchar2_value) VALUES (?, ?)"
  );

여기에서 INSERT 문은 표준 업데이트 일괄 처리 예에 있던 INSERT 문과 동일합니다. 첫 번째 위치 표시자는 int_value 열의 값을 지정하고, 두 번째 위치 표시자는 varchar2_value 열의 값을 지정합니다. 그 다음 myOraclePrepStatement의 setDefaultExecuteBatch() 메소드를 사용하여 일괄 처리 값을 20으로 설정합니다.

myOraclePrepStatement.setExecuteBatch(20);

이 일괄 처리 값은 이전에 myConnection에 대해 설정했던 일괄 처리 값 10을 무효화하므로, 데이타베이스로 전송되기 전에 myOraclePrepStatement를 사용하여 20개의 SQL 문을 일괄 처리할 수 있습니다.

getDefaultExecuteBatch() 메소드를 통해 OraclePreparedStatement 객체에 대한 현재 일괄 처리 값을 확인할 수 있습니다. 예를 들면 다음과 같습니다.

System.out.println("Batch value = " +
  myOraclePrepStatement.getExecuteBatch());

다음 단계는 SQL 문을 일괄 처리하는 것입니다.

3단계: SQL 문을 일괄 처리합니다.

일단 일괄 처리 값을 설정하면, SQL 문을 일괄 처리에 추가하기 위해 해야 하는 일은 OraclePreparedStatement 객체의 executeUpdate() 메소드를 호출하는 것이 전부입니다. 일괄 처리의 SQL 문의 개수가 일괄 처리 값에 도달하면, 일괄 처리는 자동으로 실행을 위해 데이타베이스로 보내집니다.

이 예는 20개의 INSERT 문을 일괄 처리에 추가하는 for 루프를 보여 주는데, 이는 각 INSERT 문에 대해 int_value 열의 값을 0 ~ 19로, varchar2_value 열의 값을 Test0 ~ Test19로 설정합니다.

for (int count = 0; count < 20; count++) {
  myOraclePrepStatement.setInt(1, count);
  myOraclePrepStatement.setString(2, "Test" + count);
  int rowsInserted = myOraclePrepStatement.executeUpdate();
}

2단계에서 myOraclePrepStatement 객체에 대한 일괄 처리 값을 20으로 설정했기 때문에 20번째 INSERT 문이 일괄 처리에 추가되면 일괄 처리는 자동으로 실행을 위해 데이타베이스로 보내집니다.

executeUpdate() 메소드가 반환하는 int 값은 SQL 문으로부터 영향을 받은 행의 개수입니다. SQL 문이 일괄 처리된 다음 하나의 단위로서 데이타베이스로 전송되었기 때문에, 반환된 이 int 값은 executeUpdate() 메소드가 단지 하나의 SQL 문을 일괄 처리에 추가하는 경우 0이 됩니다(SQL 문은 아직 실행되지 않았기 때문에 어떠한 행에도 영향을 주지 않았습니다). 그러나 executeUpdate() 메소드가 실행을 위해 실제로 일괄 처리 전체를 데이타베이스로 보내면, 반환된 int 값은 함께 추가된 모든 일괄 처리된 SQL 문으로부터 영향을 받은 행의 총 개수가 됩니다.

그러므로 for 루프의 예에서 executeUpdate()가 반환하는 int 값은 루프의 19번째 반복까지는 0이 됩니다(왜냐하면 INSERT 문은 일괄 처리에 추가만 되었고 실제로 실행되지는 않았기 때문입니다). 그러나 20번째 반복에서 일괄 처리는 데이타베이스로 보내지고 모든 문이 실행되므로 반환된 int 값은 20(20개의 일괄 처리된 INSERT 문에 의해 데이타베이스에 추가된 행의 개수)이 됩니다.

4단계: 실행된 문을 커밋합니다.

네 번째 단계는 Connection 객체에서 commit() 또는 rollback() 메소드를 호출하는 것입니다. 예를 들어 myConnection이라는 연결 객체를 생성했다면 다음과 같이 일괄 처리 전체를 커밋합니다.

myConnection.commit();

일괄 처리 강제 실행

또한 일괄 처리가 채워지기 전에 일괄 처리를 강제로 실행할 수 있습니다. 즉, sendBatch() 메소드를 호출하면 일괄 처리를 강제로 실행할 수 있습니다. 보통은 일괄 처리가 가득차지 않더라도 여러분이 커밋을 수행할 때마다 일괄 처리를 데이타베이스로 보낼 수 있기 때문에 이 메소드를 사용할 필요가 없습니다. 그러나 일괄 처리를 강제로 실행하고 싶은 경우가 생길 수도 있습니다.

sendBatch() 사용의 예를 살펴봅시다. 3개의 INSERT 문을 일괄 처리에 추가하는 경우가 있다고 가정합시다. 이 경우 다음과 같이 sendBatch()를 호출하고 OraclePreparedStatement 객체를 사용하여 이 3개의 문을 강제로 실행할 수 있습니다.

int rowsInserted = myOraclePrepStatement.sendBatch();

sendBatch() 메소드는 일괄 처리의 SQL 문으로부터 영향을 받은 행의 개수인 int 값을 반환합니다. 여기에서는 각 INSERT 문이 하나의 행만 삽입한다는 가정 하에서 sendBatch()가 일괄 처리의 문이 삽입한 행의 개수인 3을 반환하게 됩니다..

예제 애플리케이션: OracleUpdateBatching.java

OracleUpdateBatching.java 애플리케이션(목록 3 참조)은 Oracle 업데이트 일괄 처리의 사용 방법 및 성능에 미친 영향을 나타냅니다. 이 프로그램은 다음 사항을 수행합니다.

1. Oracle JDBC 드라이버를 등록합니다.
2. Connection 객체를 생성하고 사용자 scott으로서 데이타베이스에 연결합니다.
3. 자동 커밋 모드를 비활성화합니다.
4. 문 객체를 생성합니다.
5. performance 테이블을 절단합니다.
6. 일괄 처리를 사용하지 않으면서 1,000개의 행을 performance 테이블에 삽입하고, 삽입에 걸린 전체 시간을 1000분의 1초로 표시합니다. 애플리케이션에 정의되어 있는 insertRows() 메소드가 삽입을 수행합니다.
7. performance 테이블을 다시 절단하여 데이타를 지웁니다.
8. 이번에는 Oracle 업데이트 일괄 처리를 사용하여 다시 1,000개의 행을 performance 테이블에 삽입합니다. OraclePreparedStatement 객체에 대한 일괄 처리 값을 10으로 설정합니다.
9. 문과 Connection 객체를 닫습니다.

이 프로그램 실행의 예제 출력 결과는 다음과 같습니다.

Inserting 1000 rows without batching
Batch value = 1
Total time for inserting 1000 rows was 3785 milliseconds
Inserting 1000 rows with Oracle update batching
Setting the batch value to 10
Batch value = 10
Total time for inserting 1000 rows was 461 milliseconds

이 결과를 표준 업데이트 일괄 처리 결과와 비교해 보면 Oracle 업데이트 일괄 처리가 더 빠르다는 것을 알 수 있습니다. 삽입될 행의 값과 일괄 처리 값을 수정하면 결과는 더욱 분명해집니다. 예를 들어 일괄 처리 값을 50, 삽입될 행의 값을 10,000으로 하여 실행하면 업데이트 일괄 처리를 사용하지 않고 실행했을 때보다 25배나 더 빨라집니다

개선의 여지는 항상 있습니다.

Java 애플리케이션의 성능을 향상시키기 위해 자동 커밋 모드를 비활성화하고 표준 또는 Oracle 업데이트 일괄 처리를 사용하는 것은 애플리케이션의 성능을 높이기 위한 두 가지 성능 조정 기술입니다. 이러한 성능 조정 기술에 관한 자세한 내용이나 다중 행 사전 인출(prefetching), 결과 집합 열 사전 정의, 재사용을 위한 문 캐싱 및 SQL 문 조정 등 기타 다른 기술에 대해서는 본인이 집필한 Oracle9i JDBC Programming(McGraw-Hill/Osborne, 2002).을 참조하시기 바랍니다.

Jason Price (jmprice_314159@yahoo.com)는 소프트웨어 업계에서 10년 이상 몸담고 있는 Oracle 공인 데이타베이스 관리자 및 애플리케이션 개발자입니다. 기술 컨설턴트이자 테크니컬 라이터인 Price는 Oracle9i JDBC Programming(McGraw-Hill/Osborne, 2002)과 Java Programming with Oracle SQLJ(O'Reilly, 2001)의 저자이기도 합니다.

펌 : http://blog.naver.com/honeyc?Redirect=Log&logNo=80017058804

Posted by is윤군

댓글을 달아 주세요

제5 외국어 영역2007.11.21 17:42

펌 : http://cafe.naver.com/jspworld.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=31
Statement vs. PreparedStatement


PreparedStatement를 사용하는 경우 분포도
이 부분은 preparedStatement를 사용하는 경우 값의 분포도에 따라 오히려 낮은 성능을 보이는 경우로 자바서비스넷(www.javaservice.net)에서 김주현씨가 답변한 내용을 일부 수정한 것으로 상당히 이해가 쉽게 정리가 되어 있습니다.


 BIND 변수를 쓸 경우 실행계획이 틀어져서 느려지는 그런 경우를 unsafe literal 이라고 합니다.  예를 들어 다음과 같은 쿼리 문이 있다고 하면  select * from emp where empno = :v1  위의 SQL에서 empno 는 emp의 PK입니다. 따라서 preparedStatement를 써서 BIND 변수를 사용하든지 그렇지 않든지 실행계획은 똑같이 나옵니다. 


이런 경우를 safe literal 이라고 합니다.  하지만 쿼리 문이 다음과 같다고 하면  select * from emp where deptno > :v1  위의 경우 :v1이 1 일 경우와 1000일 경우는 분포도가 전혀 다릅니다. 1일 경우에는 대부분의 deptno가 1보다는 크기 때문에 분포도가 넓습니다. 즉, 인덱스를 타지 않는게 더 좋습니다. 하지만 deptno가 1000보다 큰 경우는 거의 없습니다.


즉, 분포도가 좁고, 이 경우 인덱스를 타야 합니다.  그러니까 히스토그램이라는 것은 이처럼 실제 :v1 값이 무엇이냐에 따라서 분포도가 달라지는 분포도 정보입니다. 어느 value가 어느 정도의 분포도를 가지고 있는지 하는 정보를 히스토그램이라고 합니다.  문제는 BIND 변수를 쓰면 :v1 이 1이 들어올지 1000이 들어올지에 무관하게 무조건 단 하나의 실행계획만을 만들고 일괄적으로 적용하다가 보니 1000이 들어오는데도 불구하고 인덱스를 타지 않고 Full Scan하는 상황이 나올 수 있습니다.  즉, BIND 변수를 활용하면 히스토그램 정보를 활용할 수 없습니다. 


그렇다고 prepatedStatement를 쓰지 말아야 할까요? 아닙니다. 써야 합니다. 위와 같이 불충분한 통계정보(히스토그램)이 없어서 실행계획을 잘못 수립해서 느려지는 경우는 어쩔 수 없는 현재의 옵티마이져의 한계로 보아야 합니다. 이 경우 똑똑한 사람이 힌트 등을 통해서 SQL에게 올바른 실행계획을 가르쳐줘야 합니다.  아주 특수한 경우 위와 같은 이유로 튜닝을 위해 Statement를 쓰는 경우도 있습니다.


예를 들어 성별이 남, 여 두 가지일 경우, 그리고 남자가 전체의 2%이며 아주 극소수라고 가정하면 남자일 경우에는 인덱스를 타야 하고, 여자일 경우 인덱스를 타지 말아야 합니다. 이 경우 어차피 distinct 값의 종류가 딱 2가지 이므로 BIND 변수를 쓰지 않아서 하드 파싱을 해봤자 2개의 SQL 종류 밖에는 나오지 않으므로 문제가 없습니다.  select * from user where sex = 'm' select * from user where sex = 'f'  오라클의 Shared pool의 Library cache에는 위와 같이 딱 두 종류의 SQL이 저장되어있어서 재사용되겠지요.  



Statement vs. PreparedStatement의 성능 차이 


이 부분은 자바서비스넷(www.javaservice.net) 에서 statement와 preparedstatement 중 어떤 것이 나은지 논쟁 중에 박영록(poci)씨가 “static sql과 dynamic sql의 성능 차이”라는 제목으로 preparedstatement를 사용하는 경우 발생할 수 있는 문제에 대해 정리한 내용을 일부 수정한 것으로 소모적인 논쟁 중에서 빛을 발하는 내용입니다.


 prepared statement에서는 보통 변수를 설정하고 바인딩하는 static sql이 사용되고 statement에서는 쿼리 자체에 조건이 들어가는 dynamic sql이 사용됩니다. (주 - 여기서 언급하고 있는 static/dynamic은 일반적으로 얘기하는 동적 sql과는 다른 의미로, 실행계획이 매번 새로 생성되는지 아닌지에 관한 것입니다.)  preparedStatement가 파싱 타임을 줄여주는 것은 분명히 중요한 장점이지만, static sql을 사용하는데 따르는 퍼포먼스 저하를 생각하지 않을 수가 없습니다.  이를테면 이런 예가 있을 수 있습니다.  dynamic sql을 사용하는 경우 자바에서 다음과 같은 SQL을 생성했다고 가정해봅시다. 


SELECT ... FROM   TAB1 WHERE  COL1 LIKE '%' AND    COL2 LIKE '%' AND    COL3 LIKE 'ABC%'  그리고 인덱스가 COL1 + COL2 + COL3 로 걸려 있습니다.  그렇다면 실행계획은 당연히 인덱스에서 COL3 컬럼만을 액세스하게 설정될 것입니다.  그런데 다음과 같은 static sql을 봅시다.  SELECT ... FROM   TAB1 WHERE  COL1 LIKE :A || '%' AND    COL2 LIKE :B || '%' AND    COL3 LIKE :C || '%'  이런 식으로 SQL을 작성했다고 합시다.


A, B, C에 어떤 값이 들어올지 모르니까 인덱스의 COL1, COL2, COL3 컬럼을 차례대로 스캔하도록 처리 경로가 잡힙니다. 그런데 조건으로 A, B는 NULL, C에는 'ABC'라는 값이 들어왔습니다. COL3 컬럼만 랜덤으로 액세스해서 테이블로 가면 될 것을 인덱스 풀스캔을 해버립니다.  이와 같이 static sql은 변수에 어떤 값이 바인딩 되는지를 알 수 없기 때문에 가장 일반적인 형태로 실행 계획을 작성합니다. 때문에 통계 자료를 제대로 활용할 수 없고 인덱스 활용 등에서 비효율이 발생할 확률이 높아집니다.  이런 예는 정말로 많습니다. 이를테면 TABLE1의 COL1의 분포도가 값이 'A'인 게 100건, 'B'인 게 100만 건 있다고 할 경우, COL1 = :VAR 와 같이 바인딩을 시켜놓으면 늘 풀스캔을 하게 실행계획을 잡습니다.


그런데 :VAR에 'A'가 들어가는 경우 인덱스를 타면 금방 결과가 나올 것을 풀스캔 후에 결과를 리턴하게 되죠.  static sql의 활용으로 파싱 타임을 절약해서 매번 실행 계획을 작성하는 것을 피하는 대가로 좀더 비효율적인 실행 계획이 수립된다는 것이죠. 일반적으로 SQL의 실행과정에서 파싱과 실행 계획 수립 과정은 전체 SQL 수행 시간에서 큰 비중을 차지하지 않습니다. 가장 큰 비중으로 차지하는 것은 테이블에서 로우를 가져오는 과정이고 파싱 시간은 이의 10분의 1에 불과합니다. 그런데, 이 10%의 시간을 줄이자고 90%의 시간에서 비효율이 발생하게 만드는 일이 생길 수 있습니다. 


물론 쿼리 생성 단계에서부터 어느 정도는 해결할 수 있는 문제입니다만, 그로 인해 자바 코드와 SQL이 복잡하게 얽히는 문제도 결코 작은 문제가 아니죠.  얼마전 대용량 데이터베이스 I, II를 모두 수강했습니다. 강사분께서 계속 강조하는 말씀이 두 가지가 있었습니다. 첫째는 원리를 이해하고 이론적으로 따져보는 것이 중요하다는 것, 그리고 둘째는 이론적으로 따져서 작성한 SQL에 대해 반드시 실행 계획을 떠보라는 것이었습니다. 상반되는 이야기인 것처럼도 보이고 일반적인 이론과 경험의 조화를 말하는 것처럼도 보이지만 저에게는 이론적인 체계를 먼저 잡아나가고 원리를 따져본 다음에 그 이론을 검증해보라는 말로 들렸습니다.  


Statement vs. PreparedStatement의 JDBC 드라이버별 속도 차이
이 부분은 O’Reilly 출판사에서 나온 “Java Programming with Oracle JDBC” 란 책의 19장 Performance 에서 statement와 preparedStatement를 JDBC 드라이버별로 속도를 비교한 자료이다. 결론적으로 실 프로젝트에서 여기 결과를 따라 프로그래밍을 할 수는 없겠지만 개념적으로라도 알고 있어야 할 내용이다.
 There's a popular belief that using a PreparedStatement object is faster than using a Statement object. After all, a prepared statement has to verify its metadata against the database only once, while a statement has to do it every time. So how could it be any other way? Well, the truth of the matter is that it takes about 65 iterations of a prepared statement before its total time for execution catches up with a statement. This has performance implications for your application, and exploring these issues is what this section is all about.   When it comes to which SQL statement object performs better under typical use, a Statement or a PreparedStatement, the truth is that the Statement object yields the best performance. When you consider how SQL statements are typically used in an application--1 or 2 here, maybe 10-20 (rarely more) per transaction--you realize that a Statement object will perform them in less time than a PreparedStatement object. In the next two sections, we'll look at this performance issue with respect to both the OCI driver and the Thin driver.  The OCI Driver Table 19-3 shows the timings in milliseconds for 1 insert and 1,000 inserts in the TESTXXXPERF table. The inserts are done first using a Statement object and then a PreparedStatement object. If you look at the results for 1,000 inserts, you may think that a prepared statement performs better. After all, at 1,000 inserts, the PreparedStatement object is almost twice as fast as the Statement object, but if you examine Figure 19-1, you'll see a different story.  Table 19-3: OCI driver timings (in milliseconds) Inserts Statement PreparedStatement  1 10 113  1,000 2,804 1,412   Figure 19-1 is a graph of the timings needed to insert varying numbers of rows using both a Statement object and a PreparedStatement object. The number of inserts begins at 1 and climbs in intervals of 10 up to a maximum of 150 inserts. For this graph and for those that follow, the lines themselves are polynomial trend lines with a factor of 2. I chose polynomial lines instead of straight trend lines so you can better see a change in the performance as the number of inserts increases. I chose a factor of 2 so the lines have only one curve in them. The important thing to notice about the graph is that it's not until about 65 inserts that the PreparedStatement object outperforms the Statement object. 65 inserts! Clearly, the Statement object is more efficient under typical use when using the OCI driver.   Figure 19-1. OCI driver timings      The Thin Driver If you examine Table 19-4 (which shows the same timings as for Table 19-3, but for the Thin driver) and Figure 19-2 (which shows the data incrementally), you'll see that the Thin driver follows the same behavior as the OCI driver. However, since the Statement object starts out performing better than the PreparedStatement object, it takes about 125 inserts for the PreparedStatement to outperform Statement.  Table 19-4: Thin driver timings (in milliseconds) Inserts Statement PreparedStatement  1 10 113  1,000 2,583 1,739    Figure 19-2. Thin driver timings      When you consider typical SQL statement usage, even with the Thin driver, you'll get better performance if you execute your SQL statements using a Statement object instead of a PreparedStatement object. Given that, you may ask: why use a PreparedStatement at all? It turns out that there are some reasons why you might use a PreparedStatement object to execute SQL statements. First, there are several types of operations that you simply can't perform without a PreparedStatement object. For example, you must use a PreparedStatement object if you want to use large objects like BLOBs or CLOBs or if you wish to use object SQL. Essentially, you trade some loss of performance for the added functionality of using these object technologies. A second reason to use a PreparedStatement is its support for batching.  Batching As you saw in the previous section, PreparedStatement objects eventually become more efficient than their Statement counterparts after 65-125 executions of the same statement. If you're going to execute a given SQL statement a large number of times, it makes sense from a performance standpoint to use a PreparedStatement object. But if you're really going to do that many executions of a statement, or perhaps more than 50, you should consider batching. Batching is more efficient because it sends multiple SQL statements to the server at one time. Although JDBC defines batching capability for Statement objects, Oracle supports batching only when Prepared-Statement objects are used. This makes some sense. A SQL statement in a PreparedStatement object is parsed once and can be reused many times. This naturally lends itself to batching.   The OCI Driver Table 19-5 lists Statement and batched PreparedStatement timings, in milliseconds, for 1 insert and for 1,000 inserts. At the low end, one insert, you take a small performance hit for supporting batching. At the high end, 1,000 inserts, you've gained 75% throughput.  Table 19-5: OCI driver timings (in milliseconds) Inserts Statement Batched  1 10 117  1,000 2,804 691   If you examine Figure 19-3, a trend line analysis of the Statement object versus the batched PreparedStatement object, you'll see that this time, the batched Prepared-Statement object becomes more efficient than the Statement object at about 50 inserts. This is an improvement over the prepared statement without batching.   Figure 19-3. OCI driver timings for batched SQL      WARNING:  There's a catch here. The 8.1.6 OCI driver has a defect by which it does not support standard Java batching, so the numbers reported here were derived using Oracle's proprietary batching.  Now, let's take a look at batching in conjunction with the Thin driver.  The Thin Driver The Thin driver is even more efficient than the OCI driver when it comes to using batched prepared statements. Table 19-6 shows the timings for the Thin driver using a Statement object versus a batched PreparedStatement object in milliseconds for the specified number of inserts.  Table 19-6: Thin driver timings (in milliseconds) Inserts Statement Batched  1 10 117  1,000 2,583 367   The Thin driver takes the same performance hit on the low end, one insert, but gains a whopping 86% improvement on the high end. Yes, 1,000 inserts in less than a second! If you examine Figure 19-4, you'll see that with the Thin driver, the use of a batched PreparedStatement object becomes more efficient than a Statement object more quickly than with the OCI driver--at about 40 inserts.  Figure 19-4. Thin driver timings for batched SQL      If you intend to perform many iterations of the same SQL statement against a database, you should consider batching with a PreparedStatement object.   

Posted by is윤군
TAG Oracle

댓글을 달아 주세요