오라클은 다중 테이블을 조인하여 데이터를 갱신하는 기능을 지원합니다.


T1 테이블 A1, A2, A3 컬럼, T2 테이블 B1, B2, B3 컬럼에서 A2 와 B2 의 데이터가 같고 A1 의 데이터가 1 인경우 A3 을 B3 으로 업데이트 한다고 합니다.

CREATE TABLE A ( A1 INT, A2 INT, A3 INT );
CREATE TABLE B ( B1 INT, B2 INT, B3 INT );
INSERT INTO A VALUES (1, 3, 3);
INSERT INTO B VALUES (1, 3, 5);
COMMIT;

UPDATE 문은 다음과 같이 작성할 수 있습니다.

UPDATE A SET A3 = ( SELECT B3
                    FROM B
                    WHERE A.A2 = B.B2 AND
		    A.A1 = 1 );

이후 A 테이블의 데이터를 조회하면 A3 컬럼이 B3 의 데이터가 되는 것을 확인할 수 있습니다.

SQL> SELECT * FROM A;

        A1         A2         A3
---------- ---------- ----------
         1          3          5


그런데 만약 일치하는 데이터가 없다고 하면 어떻게 될까요? 기존 데이터가 없기 때문에 NULL 값으로 업데이트 됩니다.

A.A1 을 2 로 설정하고 업데이트 한 경우

SQL> SELECT * FROM A;

        A1         A2         A3
---------- ---------- ----------
         1          3

만약 NULL 인경우 업데이트를 원치 않는 경우에는 NVL 함수를 써야 합니다.


이 구문을 UPDATE JOIN 을 사용하여 업데이트를 해보겠습니다.

먼저, JOIN 대상이 되는 테이블의 부모/자식 관계를 오라클이 알고있어야 합니다. 이를 알지 못하는 경우 다음과 같은 에러가 발생합니다.

ORA-01779: cannot modify a column which maps to a non key-preserved table


위 테이블에서 부모와 자식관계를 알려주기 위해 B 테이블에 제약조건을 추가합니다.

SQL> ALTER TABLE B ADD CONSTRAINT B_PK PRIMARY KEY (B2);

이후 UPDATE JOIN 을 사용하여 데이터를 갱신합니다.

SQL> UPDATE ( SELECT A.A3 A_A3, B.B3 B_B3
              FROM A, B
              WHERE A.A2 = B.B2 AND
                    A.A1 = 1 ) SET A_A3 = B_B3;

1 row updated.

SQL> SELECT * FROM A;

        A1         A2         A3
---------- ---------- ----------
         1          3          5


오라클 11g 이전버전에는 /*+ BYPASS_UVJC */ 힌트를 사용하여 위의 에러를 무시했지만, 이는 적절치 못하는 방법이며 부모/자식의 관계를 오라클이 알도록 하는게 가장 좋은 방법입니다.


블로그 이미지

사용자 꽁담

SQL 의 성능은 시스템 운영에 매우 중요합니다.

예를들어 사용자가 평소 1초내로 응답받던 결과가, 갑자기 느려지면 사용자는 그 서비스를 더이상 사용하지 않을수도 있습니다.


이번 포스팅은 SPM(SQL Plan Management 실행계획 관리) 을 통해 SQL 성능이 갑자기 느려졌을 때, 대처하는 방법입니다.


SPM 을 관리함으로써 아래와 같은 효과를 얻을 수 있습니다.

 - 성능 안정화

 - 성능 개선



먼저 SPM 아키텍처에 대해 알아봅니다.

SPM 은 SQL 성능이 저하될 경우를 대비해 미리 실행계획을 저장합니다.

이렇게 저장된 실행계획은 'SQL 계획 베이스라인' 이라고 불립니다.

실행계획은 SQL 단위로 다수개가 저장될 수 있습니다.


SPM 은 SQL 관리 베이스라는 딕셔너리 뷰에서 관리되며, SYSAUX 테이블 스페이스에 저장됩니다.

SQL 관리 베이스는 SQL 별로 실행계획이 저장되며 SQL 계획 이력과 베이스라인이 있습니다.


말로는 어려우니 그림으로 좀 더 쉽게 풀어보겠습니다.


 SQL 관리 베이스

 설명

 SQL 계획 이력

 특정 SQL 과 관련된 모든 실행계획

 미승인된 이력들은 SQL 실행계획에 사용되지 않음

 SQL 계획 베이스라인

 승인되어 사용될 수 있는 SQL 실행계획

 하나 이상의 실행 계획이 존재하며, 이 때는 적합한 실행계획을 옵티마이저가 채택하여 사용


SPM 은 베이스라인에 저장된 실행계획으로만 SQL 구문을 실행해 성능에 영향을 미치는 것을 방지합니다.

만약 특정 SQL 의 베이스라인에 하나의 실행계획만 저장한다면, 갑자기 실행계획이 변하는 일을 방지할 수 있습니다.



그렇다면, 이 베이스라인은 어디에 저장될까요?

SQL 계획 베이스라인은 DBA_SQL_PLAN_BASELINES 딕셔너리 뷰에서 확인할 수 있습니다.


SQL 계획 베이스라인을 자동캡쳐로 사용하기 위해서는 아래 프로퍼티처럼 설정되어 있어야 합니다.

OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES 는 실행계획을 자동으로 캡쳐하는,

OPTIMIZER_USE_SQL_PLAN_BASELINES 가 TRUE 인 경우 SPM 을 사용해 SQL 구문을 파싱, FALSE 라면 일반적인 하드파싱으로 실행계획을 결정합니다.

SQL> SHOW PARAMETER SQL_PLAN_BASELINES NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean TRUE optimizer_use_sql_plan_baselines boolean FALSE

DBA_SQL_PLAN_BASELINES 뷰에서는 생성된 실행계획을 위해 다음과 같은 컬럼을 확인합니다.

 컬럼

 설명

 SQL_HANDLE

 SPM 으로 SQL 을 식별하기 위한 판별자

 PLAN_NAME

 SPM 에서 실행 계획을 식별하기 위한 식별자

 CREATED

 SQL 이력의 생성 시간

 ACCEPTED

 승인 여부

 ENABLED

 실행 계획을 사용할 수 있는지 여부

 SQL_TEXT

 SQL 텍스트


SQL> SELECT SQL_HANDLE, PLAN_NAME,    
  2         TO_CHAR(CREATED, 'YYYY/MM/DD HH24:MI:SS') CREATED,
  3         ACCEPTED, ENABLED, SQL_TEXT
  4  FROM DBA_SQL_PLAN_BASELINES;

SQL_HANDLE           PLAN_NAME                      CREATED             ACC ENA SQL_TEXT
-------------------- ------------------------------ ------------------- --- --- -----------------------------------------------------------------
SQL_115024ccba5e158c SQL_PLAN_12n14tkx5w5cc52d2775d 2018/08/27 16:16:48 YES YES DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
SQL_294c437e331fa51f SQL_PLAN_2km23gstjz98zdf463620 2018/08/27 16:16:48 YES YES SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABL
                                                                                E', :1))

SQL_85372e07e425b213 SQL_PLAN_8adtf0zk2bchm3e1c8782 2018/08/27 16:18:58 YES YES SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'s
                                                                                tring',3,'integ

SQL_9c0d7998b1d28680 SQL_PLAN_9s3btm2sx51n0c51fbe40 2018/08/27 16:14:19 YES YES SELECT * FROM DBA_SQL_PLAN_BASELINES
SQL_dc84ded2dcd1bea1 SQL_PLAN_dt16yubfd3gp14ae4c79a 2018/08/27 16:16:48 YES YES SELECT * FROM EMP, DEPT ORDER BY 1

ACCEPTED 컬럼이 YES 라면 승인된 SQL 계획 베이스라인 입니다.

ACCEPTED 와 ENABLED 컬럼 모두 YES 일 때 실행계획으로 사용할 수 있습니다.

SQL_HANDLE 컬럼은 SQL 을 구분하기 위한 값이며, 같은 SQL 이라면 같은 SQL_HANDLE 을 가집니다.

PLAN_NAME 컬럼은 실행 계획을 식별하는 식별자이므로 각각 다른 값을 가집니다.


만약 실행 계획을 변화 없이 유지하고자 하는 SQL 구문은, 성능이 좋은 실행 계획을 SQL 계획 베이스라인으로 저장한 뒤

초기화 매개변수 OPTIMIZER_USE_SQL_PLAN_BASELINES 를 TRUE 로 설정하여 진행합니다.


등록한 실행 계획을 사용할 수 없는 경우도 있습니다.

만약 인덱스를 타는 실행계획을 베이스라인에 저장했는데 인덱스가 삭제되는 경우, 더이상 그 실행계획은 사용될 수 없습니다.



그렇다면 베이스라인에 등록된 실행계획이 실제로 사용되는지 확인은 어떻게하는지 알아봅니다.

DBMS_XPLAN.DISPLAY_CURSOR 의 NOTE, V$SQL 의 SQL_PLAN_BASELINE 컬럼에서 확인이 가능하지만, 현재 커서가 열려있는 경우에만 확인이 가능합니다.


커서가 닫힌 경우에도 확인을 원하는 경우에는

DBA_SQL_PLAN_BASELINES 의 LAST_EXECUTED 컬럼을 보면 베이스라인을 사용한 마지막 시간을 출력합니다.


하지만 이 컬럼은 한번 지정되면 6.5 일 동안 사용되더라도 변경되지 않으므로 항상 최신은 아닙니다.

만약 어떤 이유로 실행계획이 사용되지 못할때는 REPRODUCED 컬럼 값이 NO 로 변경되므로 이 컬럼으로 현재 사용되는지 확인이 가능합니다.

SQL> SELECT SQL_HANDLE, PLAN_NAME, LAST_EXECUTED, SQL_TEXT FROM DBA_SQL_PLAN_BASELINES;

SQL_HANDLE           PLAN_NAME                      LAST_EXECUTED     SQL_TEXT
-------------------- ------------------------------ ----------------- -----------------------------------------------------------------
SQL_115024ccba5e158c SQL_PLAN_12n14tkx5w5cc52d2775d 27-AUG-18 04.16.4 DELETE FROM PLAN_TABLE WHERE STATEMENT_ID=:1
                                                    8.000000 PM

SQL_294c437e331fa51f SQL_PLAN_2km23gstjz98zdf463620 27-AUG-18 04.16.4 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABL
                                                    8.000000 PM       E', :1))

SQL_435692ce8c04cbda SQL_PLAN_46pnktu609kyub73cade2 27-AUG-18 04.57.2 SELECT SQL_ID, SQL_PLAN_BASELINE, SQL_TEXT FROM V$SQL
                                                    6.000000 PM

SQL_85372e07e425b213 SQL_PLAN_8adtf0zk2bchm3e1c8782 27-AUG-18 04.18.5 SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'s
                                                    8.000000 PM       tring',3,'integ

SQL_9c0d7998b1d28680 SQL_PLAN_9s3btm2sx51n0c51fbe40 27-AUG-18 04.14.1 SELECT * FROM DBA_SQL_PLAN_BASELINES
                                                    9.000000 PM

SQL_dc84ded2dcd1bea1 SQL_PLAN_dt16yubfd3gp14ae4c79a 27-AUG-18 04.16.4 SELECT * FROM EMP, DEPT ORDER BY 1
                                                    8.000000 PM


블로그 이미지

사용자 꽁담

ORACLE 은 대량의 레코드 적재를 FORALL 키워드 를 이용하여, 벌크로 적재하는 기능을 제공합니다.

또한 이런 기능을 BULK INSERT 라고 칭합니다.


벌크 INSERT 는 레코드를 배열의 값에 저장하고 한번에 INSERT 하는 작업을 수행합니다.


예를들어서 테이블이 아래와 같은 경우,

CREATE TABLE MOZI ( C1 NUMBER, C2 NUMBER );


레코드를 3건 넣기 위해서는 INSERT 구문을 3번 수행해야 합니다.

SQL> INSERT INTO MOZI VALUES (1, 1);
SQL> INSERT INTO MOZI VALUES (2, 2);
SQL> INSERT INTO MOZI VALUES (3, 3);
SQL> COMMIT;


반면, FORALL 을 사용하여 데이터를 배열에 담은 뒤 한번의 INSERT 구문으로 진해할 수 있습니다.

FORALL 은 프로시저 함수에서 사용가능하므로, 프로시저를 아시는 분에 한해서 사용할 수 있습니다.

SQL> DECLARE
  2    TYPE mozi_ins IS TABLE OF MOZI%ROWTYPE INDEX BY BINARY_INTEGER;
  3    tistory mozi_ins;
  4  BEGIN
  5    FOR i IN 1 .. 3 LOOP
  6      tistory(i).C1 := i;
  7      tistory(i).C2 := i;
  8    END LOOP;
  9  
 10    FORALL i in 1 .. 3 INSERT INTO MOZI VALUES tistory(i);
 11    COMMIT;
 12  END;
 13  /

PL/SQL procedure successfully completed.


만약 FORALL 을 사용하지 않고 일반 INSERT 프로시져를 작성하는 경우 다음과 같이 됩니다.

SQL> BEGIN
  2    FOR i IN 1 .. 3 LOOP
  3      INSERT INTO MOZI VALUES (i, i);
  4    END LOOP;
  5    COMMIT;
  6  END;
  7  /

PL/SQL procedure successfully completed.



예를 들어보면, Java 의 Statement 와 PrepareStatement 의 비슷한 차이라고 생각하시면 됩니다.

http://mozi.tistory.com/29

블로그 이미지

사용자 꽁담

ORACLE 은 SQL 을 병렬 힌트를 부여하여 실행하는 기능을 제공합니다.

이 기능을 사용하면 어플리케이션 쪽에서 소스를 수정하지 않아도 되므로, 많은 이점이 있습니다.


병렬 힌트란, 데이터를 가져오는 작업을 단일이 아닌 멀티로 진행하여 빠른 응답을 제공받습니다.



병렬 쿼리 사용 방법

먼저, 병렬처리가 가능하도록 세션에 권한을 부여합니다.

SQL> ALTER SESSION ENABLE PARALLEL DML;

Session altered.

병렬 쿼리는 PARALLEL 힌트를 사용하여 활성화 합니다. 괄호안의 수는 멀티 작업 스레드를 의미합니다.

SQL> SELECT /*+ PARALLEL (4) */
  2    C1, C2
  3  FROM 
  4    MOZI;

        C1         C2
---------- ----------
         1          1
         2          2


병렬 실행이 되었는지 확인 방법

SQL 이 병렬 쿼리로 실행이 된 경우, V$PQ_SESSTAT 에 정보가 기록됩니다.

이 내용은 병렬 쿼리가 실행될 때마다 갱신되며, 세션이 로그아웃 하는 경우 데이터는 삭제됩니다.


Allocation Height 는 1개의 인스턴스의 병렬도를 나타내며,

Allocation Weight 는 RAC 구성때 병렬 쿼리를 실행하는 인스턴스 수를 의미합니다.


따라서 병렬도는 Allocation Height * Allocation Weight 로 계산할 수 있습니다.

SQL> SELECT * FROM V$PQ_SESSTAT;

STATISTIC                      LAST_QUERY SESSION_TOTAL     CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized                    1             2          0
DML Parallelized                        0             0          0
DDL Parallelized                        0             0          0
DFO Trees                               1             2          0
Server Threads                          4             0          0
Allocation Height                       4             0          0
Allocation Width                        1             0          0
Local Msgs Sent                        14            28          0
Distr Msgs Sent                         0             0          0
Local Msgs Recv'd                      14            28          0
Distr Msgs Recv'd                       0             0          0

STATISTIC                      LAST_QUERY SESSION_TOTAL     CON_ID
------------------------------ ---------- ------------- ----------
DOP                                     4             0          0
Slave Sets                              1             0          0

13 rows selected.


병렬 쿼리 사용은 항상 올바른가?

대량의 데이터를 병렬로 빠르게 처리할 수 있는 강력한 기능입니다.

하지만 병렬 쿼리를 사용하지 않더라도 빠르게 처리되는 쿼리, 시스템 자원의 부족 등의 경우에는 적합하지 않습니다.

블로그 이미지

사용자 꽁담

오라클 설치시 SID 가 이미 존재한다는 오류와 함께 설치가 진행되지 않은 경우가 있습니다.




이때에는 /etc/ 경로의 oratab 파일을 삭제한 뒤 다시 설치를 진행하면 됩니다.

# cd /etc
# rm oratab
rm: remove 일반 파일 `oratab'? y


블로그 이미지

사용자 꽁담

TEST 명으로 된 계정 혹은 롤이 없는 상황에서

ORACLE 에 TEST 계정을 생성하려고 하는데 아래와 같은 오류가 나왔습니다.


ORA-65096: invalid common user or role name


SQL> drop user test;
drop user test
          *
ERROR at line 1:
ORA-01918: user 'TEST' does not exist


SQL> drop role test;
drop role test
          *
ERROR at line 1:
ORA-01919: role 'TEST' does not exist
SQL> create user test identified by test;
create user test identified by test
            *
ERROR at line 1:
ORA-65096: invalid common user or role name


위 에러를 막기 위해서는 아래의 구문을 먼저 수행한 뒤에 유저를 생성해야 한다고 합니다.

SQL> alter session set "_ORACLE_SCRIPT"=true;  

Session altered.
SQL> create user test identified by test;

User created.



왜, 구문을 수행해야 하는지에 대한 URL 입니다.

http://www.dba-oracle.com/t_ora_65096_create_user_12c_without_c_prefix.htm

블로그 이미지

사용자 꽁담

이전 포스팅에서 나온 오류를 해결 한 후, 계속해서 프로그램을 짜던 중

아래와 같은 에러코드를 다시한번 뱉습니다.

[ERROR] Insert Execute Failure!
SQLCODE : -1001
ERROR MSG : ORA-01001: invalid cursor


오라클 공식문서에서도 뾰족한 해결방법도 안나와있고, 그냥 커서가 적합하지 않다라고만 되어있어서 많이 해멨습니다.


기존에 컴파일한 Makefile 방식과 에러가 나온 부분의 소스는 아래와 같았습니다.

TARGET = sh CC = gcc PROC = proc LIB = -L$(ORACLE_HOME)/lib -lclntsh -lpthread -ldl -lm -lrt -lodbc MYINC = include/ PROCINC = include=$(ORACLE_HOME)/precomp/public/ include=$(ORACLE_HOME)/rdbms/demo/ \ include=$(ORACLE_HOME)/rdbms/public/ \ include=$(ORACLE_HOME)/network/public/ CINC = -I$(ORACLE_HOME)/precomp/public/ -I$(ORACLE_HOME)/rdbms/demo/ \ -I$(ORACLE_HOME)/rdbms/public/ -I$(ORACLE_HOME)/network/public/ ORA_OPT = THREADS=YES PARSE=NONE MODE=ANSI CC_OPT = OBJECT = sh.o ORA_GARBAGE = *.dcl *.cod *.cud *.lis ######## implicit rules .SUFFIXES: .pc .c .pc.c: $(PROC) $(ORA_OPT) $(PROINC) INCLUDE=$(MYINC) $* .c.o: $(CC) -c -o $*.o $*.c -I $(MYINC) $(CINC) ####### build rules all: $(TARGET) $(TARGET): $(OBJECT) $(CC) -o $(TARGET) $(OBJECT) $(LIB) sh.c: sh.pc sh.o: sh.c clean: rm -f $(TARGET) $(TARGET).c $(OBJECT) $(ORA_GARBAGE)

memset(iQuery, 0x00, sizeof(iQuery));
sprintf(iQuery, "INSERT INTO ACCT_BALANCE VALUES (:sno, :sno, SYSDATE, SYSDATE, :sno, :sno, :sno, :sno, 
:sno, 'UPP', 'LOW', 'STT', SYSDATE, :sno, :sno, :sno, :sno, :sno, :sno, :sno )");
EXEC SQL AT :sAT PREPARE INSERT_STMT FROM :iQuery;

for ( sno = start ; sno <= end ; sno ++ )
{
    EXEC SQL AT :sAT
      EXECUTE INSERT_STMT USING :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, 
      :sno, :sno;
    printf("[sno]=%d\n", sno);
    if (sqlca.sqlcode != 0)
    {
        PRINT_SQL_ERROR("[ERROR] Insert Execute Failure!");
        return -1;
    }
EXEC SQL AT :sAT COMMIT;
}



오라클 문서를 찾아보니, ANSI 모드에서 CLOSE_ON_COMMIT 이 YES 가 기본값이며,

이 값의 의미를 찾아보니 COMMIT 이 수행될 때 열려있는 CURSOR 를 모두 닫는다고 나와있습니다. 






더군다나 AUTOCOMMIT 을 OFF 로 할 수 있는 기능도 제공되지 않아, 저 옵션을 꼭 써줘야 한답니다.

( 이 부분, 혹시 Pro*C 에서 AUTOCOMMIT 을 OFF 할 수 있는 구문이 제공된다면 꼭좀 알려주세요. 꼭꼭 )



그래서 MakeFile 과 소스를 아래와 같이 수정한 후 위 에러를 해결하였습니다.

ORA_OPT 에 CLOSE_ON_COMMIT = NO 추가

TARGET          = sh

CC              = gcc
PROC            = proc
LIB             = -L$(ORACLE_HOME)/lib -lclntsh -lpthread -ldl -lm -lrt -lodbc
MYINC           = include/
PROCINC         = include=$(ORACLE_HOME)/precomp/public/ include=$(ORACLE_HOME)/rdbms/demo/ \
                  include=$(ORACLE_HOME)/rdbms/public/ \
                  include=$(ORACLE_HOME)/network/public/
CINC            = -I$(ORACLE_HOME)/precomp/public/ -I$(ORACLE_HOME)/rdbms/demo/ \
        -I$(ORACLE_HOME)/rdbms/public/ -I$(ORACLE_HOME)/network/public/

ORA_OPT         = THREADS=YES PARSE=NONE MODE=ANSI CLOSE_ON_COMMIT=NO
CC_OPT          =

OBJECT          = sh.o
ORA_GARBAGE     = *.dcl *.cod *.cud *.lis

######## implicit rules
.SUFFIXES: .pc .c

.pc.c:
                $(PROC) $(ORA_OPT) $(PROINC) INCLUDE=$(MYINC) $*
.c.o:
                $(CC) -c -o $*.o $*.c -I $(MYINC) $(CINC)

####### build rules

all:            $(TARGET)

$(TARGET):      $(OBJECT)
                $(CC) -o $(TARGET) $(OBJECT) $(LIB)

sh.c: sh.pc
sh.o: sh.c

clean:
                rm -f $(TARGET) $(TARGET).c $(OBJECT) $(ORA_GARBAGE)



블로그 이미지

사용자 꽁담

Pro*C 를 짜던 중 아래와 같은 에러코드를 자꾸 뱉습니다.

[ERROR] Insert Execute Failure! SQLCODE : -2122 ERROR MSG : SQL-02122: Invalid OPEN or PREPARE for this database connection


오라클 사이트에서 찾아보면, 아래처럼 해결하라고 나와있습니다.

Close the cursor to make it available for this connection or use a different cursor for this connection.


대충해석해보자면, 현재 세션이 사용하고 있는 커서를 닫거나, 다른 커서를 사용하세요. 라고 되어있습니다.



데이터베이스를 재구동 해봐도 마찬가지로 발생합니다.

SID 별 열려있는 커서를 확인하니 아래와 같이 나오네요.

SQL> SELECT sid, count(sid) "cursor" 
  2  FROM V$OPEN_CURSOR
  3  GROUP BY sid;

            SID          cursor
--------------- ---------------
            728              20
            607              22
           2422               9
              2              42
           2664               9
           2059               9
           2784              42
           1212              67
            849              20
            486              10
           1091               2
            969              24
              3               2
            122              17

14 rows selected.



조금 황당한 실수였습니다. ( 실전에서는 실수 안먹히죠? ㅠㅠ )

기존 소스는 접속은 AT 절은 사용하고 EXECUTE 절에는 AT 이 없어서 발생한 문제였습니다.

EXEC SQL CONNECT :sUID IDENTIFIED BY :sPWD AT :sAT USING :sDSN;
    for ( sno = start ; sno <= end ; sno ++ )
    {
        EXEC SQL
          EXECUTE INSERT_STMT USING :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :s
no;
        if (sqlca.sqlcode != 0)
        {
            PRINT_SQL_ERROR("[ERROR] Insert Execute Failure!");
            return -1;
        }

        commit_check ++;
        if ( commit_check == interval )
        {
            EXEC SQL AT :sAT COMMIT;
            if (sqlca.sqlcode != 0)
            {
                PRINT_SQL_ERROR("[ERROR] Insert Commit Failure!");
                return -1;
            }
            commit_check = 0;
        }
    }


소스를 아래와 같이 변경한 뒤 해결되었습니다.

    for ( sno = start ; sno <= end ; sno ++ )
    {
        EXEC SQL AT :sAT
          EXECUTE INSERT_STMT USING :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :sno, :s
no;
        if (sqlca.sqlcode != 0)
        {
            PRINT_SQL_ERROR("[ERROR] Insert Execute Failure!");
            return -1;
        }

        commit_check ++;
        if ( commit_check == interval )
        {
            EXEC SQL AT :sAT COMMIT;
            if (sqlca.sqlcode != 0)
            {
                PRINT_SQL_ERROR("[ERROR] Insert Commit Failure!");
                return -1;
            }
            commit_check = 0;
        }
    }


블로그 이미지

사용자 꽁담

ORACLE 에서는 계층데이터를 위해 CONNECT BY 절을 지원합니다.



CONNECT BY 구성


CONNECT BY 는 아래의 3개의 구문으로 구성됩니다.


 구문

 설명

 WHERE

 데이터를 가져온 뒤 마지막으로 조건절에 맞게 정리

 START WITH

 어떤 데이터로 계층구조를 지정하는지 지정

 CONNECT BY 각 행들의 연결 관계를 설정


* START WITH 는 가장 처음에 데이터를 거르는 플랜을 타게 되고, 따라서 이 컬럼에는 인덱스가 걸려있어야 성능을 보장받습니다.

* CONNECT BY 절의 결과에는 LEVEL 이라는 컬럼이 있으며, 이는 계층의 깊이를 의미합니다.



CONNECT BY 사용하기


먼저 EMP 테이블에 데이터가 아래와 같이 있습니다.

SQL> SELECT * FROM EMP;

     EMPNO ENAME                          JOB                                MGR HIREDATE        SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
      7839 KING                           PRESIDENT                              81/11/17       5000                    10
      7698 BLAKE                          MANAGER                           7839 81/05/01       2850                    30
      7782 CLARK                          MANAGER                           7839 81/06/09       2450                    10
      7566 JONES                          MANAGER                           7839 81/04/02       2975                    20
      7902 FORD                           ANALYST                           7566 81/12/03       3000                    20
      7369 SMITH                          CLERK                             7902 80/12/17        800                    20
      7499 ALLEN                          SALESMAN                          7698 81/02/20       1600        300         30
      7521 WARD                           SALESMAN                          7698 81/02/22       1250        500         30
      7654 MARTIN                         SALESMAN                          7698 81/09/28       1250       1400         30
      7844 TURNER                         SALESMAN                          7698 81/09/08       1500          0         30
      7900 JAMES                          CLERK                             7698 81/12/03        950                    30
      7934 MILLER                         CLERK                             7782 82/01/23       1300                    10

12 행이 선택되었습니다.


CONNECT BY 구문을 써서 쿼리를 수행시킨 결과는 다음과 같습니다.

아래의 쿼리는 JOB = PRESIDENT 를 기준으로 ( 계층의 LEVEL 1 ), EMPNO 와 MGR 이 같은 관계를 계층적으로 출력합니다.

SQL> SELECT LEVEL, EMPNO, ENAME, MGR, JOB FROM EMP START WITH JOB = 'PRESIDENT' CONNECT BY PRIOR EMPNO = MGR;

LEVEL EMPNO ENAME MGR JOB ---------- ---------- ------------------------------ ---------- --------------------------- 1 7839 KING PRESIDENT 2 7566 JONES 7839 MANAGER 3 7902 FORD 7566 ANALYST 4 7369 SMITH 7902 CLERK 2 7698 BLAKE 7839 MANAGER 3 7499 ALLEN 7698 SALESMAN 3 7521 WARD 7698 SALESMAN 3 7654 MARTIN 7698 SALESMAN 3 7844 TURNER 7698 SALESMAN 3 7900 JAMES 7698 CLERK 2 7782 CLARK 7839 MANAGER 3 7934 MILLER 7782 CLERK 12 행이 선택되었습니다.


깊이가 깊어지는 경우 LEVEL 을 조건절에 걸어서 원하는 깊이까지만 볼 수 있습니다.

SQL> SELECT LEVEL, EMPNO, ENAME, MGR, JOB FROM EMP START WITH JOB = 'PRESIDENT' CONNECT BY PRIOR EMPNO = MGR AND LEVEL <= 3; LEVEL EMPNO ENAME MGR JOB ---------- ---------- ------------------------------ ---------- --------------------------- 1 7839 KING PRESIDENT 2 7566 JONES 7839 MANAGER 3 7902 FORD 7566 ANALYST 2 7698 BLAKE 7839 MANAGER 3 7499 ALLEN 7698 SALESMAN 3 7521 WARD 7698 SALESMAN 3 7654 MARTIN 7698 SALESMAN 3 7844 TURNER 7698 SALESMAN 3 7900 JAMES 7698 CLERK 2 7782 CLARK 7839 MANAGER 3 7934 MILLER 7782 CLERK 11 행이 선택되었습니다.


혹은 LEVEL 의 깊이가 3이하이며, EMPNO 가 7500 보다 큰 데이터만도 볼수 있습니다.

SQL> SELECT LEVEL, EMPNO, ENAME, MGR, JOB
FROM EMP
WHERE EMPNO > 7500
START WITH JOB = 'PRESIDENT'
CONNECT BY PRIOR EMPNO = MGR AND LEVEL <= 3;

     LEVEL      EMPNO ENAME                                 MGR JOB
---------- ---------- ------------------------------ ---------- ---------------------------
         1       7839 KING                                      PRESIDENT
         2       7566 JONES                                7839 MANAGER
         3       7902 FORD                                 7566 ANALYST
         2       7698 BLAKE                                7839 MANAGER
         3       7521 WARD                                 7698 SALESMAN
         3       7654 MARTIN                               7698 SALESMAN
         3       7844 TURNER                               7698 SALESMAN
         3       7900 JAMES                                7698 CLERK
         2       7782 CLARK                                7839 MANAGER
         3       7934 MILLER                               7782 CLERK

10 행이 선택되었습니다.


블로그 이미지

사용자 꽁담

ORACLE 의 SQL 에서는 사용자와의 편리성을 위해 명령어들을 제공합니다.


아래는, 제가 자주쓰는 명령어들 입니다.


 옵션

 설명

 CONN

 다른계정으로 접속

 !

 리눅스의 명령어를 실행

 @파일

 파일의 쿼리문을 수행

 SET LINESIZE

 한 라인에 출력되는 데이터 길이를 지정

 SET PAGESIZE

 한 페이지에 출력되는 레코드 수를 지정

 l 혹은 ;

 방금 수행한 쿼리를 출력 ( 소문자 L )

 /

 방금 수행한 쿼리를 수행

 ED

 방금 수행한 쿼리를 수정 

 SET TIMING ON 쿼리 수행에 걸린 시간을 출력



SET LINESIZE 를 1000 으로 설정한 후

SQL> select * from emp;

     EMPNO ENAME                          JOB                                MGR
---------- ------------------------------ --------------------------- ----------
HIREDATE        SAL       COMM     DEPTNO
-------- ---------- ---------- ----------
      7839 KING                           PRESIDENT
81/11/17       5000                    10

      7698 BLAKE                          MANAGER                           7839
81/05/01       2850                    30

      7782 CLARK                          MANAGER                           7839
81/06/09       2450                    10
...
SQL> set linesize 1024

SQL> /

     EMPNO ENAME                          JOB                                MGR HIREDATE        SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- -------- ---------- ---------- ----------
      7839 KING                           PRESIDENT                              81/11/17       5000                    10
      7698 BLAKE                          MANAGER                           7839 81/05/01       2850                    30
      7782 CLARK                          MANAGER                           7839 81/06/09       2450                    10
      7566 JONES                          MANAGER                           7839 81/04/02       2975                    20
      7902 FORD                           ANALYST                           7566 81/12/03       3000                    20
      7369 SMITH                          CLERK                             7902 80/12/17        800                    20
      7499 ALLEN                          SALESMAN                          7698 81/02/20       1600        300         30
      7521 WARD                           SALESMAN                          7698 81/02/22       1250        500         30
      7654 MARTIN                         SALESMAN                          7698 81/09/28       1250       1400         30



혹은 HELP SHOW 와 HELP SET 으로 더욱 다양한 명령어들을 찾아볼 수 있습니다.

SQL> help show

 SHOW
 ----

 Shows the value of a SQL*Plus system variable, or the current
 SQL*Plus environment. SHOW SGA requires a DBA privileged login.

 SHO[W] option

 where option represents one of the following terms or clauses:
     system_variable
     ALL
     BTI[TLE]
     CON_ID
     CON_NAME
     ERR[ORS] [{FUNCTION | PROCEDURE | PACKAGE | PACKAGE BODY | TRIGGER
        | VIEW | TYPE | TYPE BODY | DIMENSION | JAVA CLASS} [schema.]name]
     LNO
     PARAMETERS [parameter_name]
     PDBS
     PNO
     RECYC[LEBIN] [original_name]
     REL[EASE]
     REPF[OOTER]
     REPH[EADER]
     SGA
     SPOO[L]
     SPPARAMETERS [parameter_name]
     SQLCODE
     TTI[TLE]
     USER
SQL> help set

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session. For example, to:
     -   set the display width for data
     -   customize HTML formatting
     -   enable or disable printing of column headings
     -   set the number of lines per page

 SET system_variable value

 where system_variable and value represent one of the following clauses:

   APPI[NFO]{OFF|ON|text}                   NEWP[AGE] {1|n|NONE}
   ARRAY[SIZE] {15|n}                       NULL text
   AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}      NUMF[ORMAT] format
   AUTOP[RINT] {OFF|ON}                     NUM[WIDTH] {10|n}
   AUTORECOVERY {OFF|ON}                    PAGES[IZE] {14|n}
   AUTOT[RACE] {OFF|ON|TRACE[ONLY]}         PAU[SE] {OFF|ON|text}
     [EXP[LAIN]] [STAT[ISTICS]]             RECSEP {WR[APPED]|EA[CH]|OFF}
   BLO[CKTERMINATOR] {.|c|ON|OFF}           RECSEPCHAR {_|c}
   CMDS[EP] {;|c|OFF|ON}                    SERVEROUT[PUT] {ON|OFF}
   COLINVI[SIBLE] {OFF|ON}                    [SIZE {n | UNLIMITED}]
   COLSEP {_|text}                            [FOR[MAT]  {WRA[PPED] |
   CON[CAT] {.|c|ON|OFF}                       WOR[D_WRAPPED] |
   COPYC[OMMIT] {0|n}                          TRU[NCATED]}]
   COPYTYPECHECK {ON|OFF}                   SHIFT[INOUT] {VIS[IBLE] |
   DEF[INE] {&|c|ON|OFF}                      INV[ISIBLE]}
   DESCRIBE [DEPTH {1|n|ALL}]               SHOW[MODE] {OFF|ON}
     [LINENUM {OFF|ON}] [INDENT {OFF|ON}]   SQLBL[ANKLINES] {OFF|ON}
   ECHO {OFF|ON}                            SQLC[ASE] {MIX[ED] |
   EDITF[ILE] file_name[.ext]                 LO[WER] | UP[PER]}
   EMB[EDDED] {OFF|ON}                      SQLCO[NTINUE] {> | text}
   ERRORL[OGGING] {ON|OFF}                  SQLN[UMBER] {ON|OFF}
     [TABLE [schema.]tablename]             SQLPLUSCOMPAT[IBILITY] {x.y[.z]}
     [TRUNCATE] [IDENTIFIER identifier]     SQLPRE[FIX] {#|c}
   ESC[APE] {\|c|OFF|ON}                    SQLP[ROMPT] {SQL>|text}
   ESCCHAR {@|?|%|$|OFF}                    SQLT[ERMINATOR] {;|c|ON|OFF}
   EXITC[OMMIT] {ON|OFF}                    SUF[FIX] {SQL|text}
   FEED[BACK] {6|n|ON|OFF}                  TAB {ON|OFF}
   FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}  TERM[OUT] {ON|OFF}
   FLU[SH] {ON|OFF}                         TI[ME] {OFF|ON}
   HEA[DING] {ON|OFF}                       TIMI[NG] {OFF|ON}
   HEADS[EP] {||c|ON|OFF}                   TRIM[OUT] {ON|OFF}
   INSTANCE [instance_path|LOCAL]           TRIMS[POOL] {OFF|ON}
   LIN[ESIZE] {80|n}                        UND[ERLINE] {-|c|ON|OFF}
   LOBOF[FSET] {1|n}                        VER[IFY] {ON|OFF}
   LOGSOURCE [pathname]                     WRA[P] {ON|OFF}
   LONG {80|n}                              XQUERY {BASEURI text|
   LONGC[HUNKSIZE] {80|n}                     ORDERING{UNORDERED|
   MARK[UP] HTML [OFF|ON]                              ORDERED|DEFAULT}|
     [HEAD text] [BODY text] [TABLE text]     NODE{BYVALUE|BYREFERENCE|
     [ENTMAP {ON|OFF}]                             DEFAULT}|
     [SPOOL {OFF|ON}]                         CONTEXT text}
     [PRE[FORMAT] {OFF|ON}]


블로그 이미지

사용자 꽁담