2021년 12월 11일 토요일

https://youtu.be/b-IxbRmwpGE오라클 데이터 입력시 중복되는 경우 중복되지 않는 데이터만 입력, IGNORE_ROW_ON_DUPKEY_INDEX 힌트, DML Error Logging사용법(자바학원/오라클학원)

https://youtu.be/b-IxbRmwpGE오라클 데이터 입력시 중복되는 경우 중복되지 않는 데이터만 입력, IGNORE_ROW_ON_DUPKEY_INDEX 힌트, DML Error Logging사용법(자바학원/오라클학원)

http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=326

오라클 데이터 입력시 중복되는 경우 중복되지 않는 데이터만 입력, IGNORE_ROW_ON_DUPKEY_INDEX 힌트, DM

오라클 데이터 입력시 중복되는 경우 중복되지 않는 데이터만 입력, IGNORE_ROW_ON_DUPKEY_INDEX 힌트, DML Error Logging사용법ORACLE HINT데이터 입력시 중복되는 경우 오류를 발생시키지 않고 중복되지 않는

ojc.asia

https://www.youtube.com/watch?v=RsswtJnUFP0&list=PLxU-iZCqT52DFRbLFQIgGUFp-5En2DYRG&index=27

ORACLE HINT

데이터 입력시 중복되는 경우 오류를 발생시키지 않고 중복되지 않는 데이터만 입력

IGNORE_ROW_ON_DUPKEY_INDEX 힌트이용

DML Error Logging 이용

실습 : 오라클19C

자주하는 작업 중 하나는 INSERT… SELECT… 문을 이용하여 대량의 데이터를 INSERT 하는 일이다. 이 경우 키(Primary Key) 값이 중복되어 무결성 제약조건 오류가 발생할 수 있는데 이 때 INSERT문은 롤백 된다.

오라클 10g의 New Feature로 소개되었던 DML Error Logging을 이용하여 오류가 발생한 레코드를 별도의 로깅 테이블에 기록하고 에러 때문에 DML이 중지되는 것을 막을 수 있지만 사용하기에 번거롭다.

오라클 10g의 DML Error Logging은 아래 URL에서 확인하자.
http://ojc.asia/bbs/board.php?bo_table=Cyber&wr_id=17

Oracle 11g에서 새로 소개된 ignore_row_on_dupkey_index 힌트를 사용하면 쉽게 해결할 수 있는데, 데이터 키값이 중복되는 경우 오류를 발생시키지 않고 중복되지 않는 데이터만 입력할 수 있게 해준다. 아쉬운 점이라면 SINGLE INSERT문만 지원하고 UPDATE, DELETE, MERGE, MULTI INSERT 구문에서는 안 된다는 것이며 APPEND, PARALEL 힌트와 같이 사용하면 이 두 힌트가 무시되고 Direct Path Load는 지원하지 않는다.

SQL> create table onj (
id number constraint pk_onj primary key,
name varchar2(20)
);

SQL> insert into onj values (1, '1길동');
SQL> insert into onj values (2, '2길동');
SQL> insert into onj values (3, '3길동');

SQL> commit;

SQL> select * from onj;

ID NAME
---------- --------------------
1 1길동
2 2길동
3 3길동

SQL> create table onj_old (
id number primary key,
name varchar2(20)
);

SQL> insert into onj_old values (1, '1길동');
SQL> insert into onj_old values (4, '4길동');
SQL> insert into onj_old values (5, '5길동');

SQL> commit;

-- onj_old의 내용을 onj 테이블로 insert하자. 1번 데이터가 중복되는 상황이다.
SQL> insert into onj select * from onj_old;

1행에 오류:
ORA-00001: 무결성 제약 조건(SCOTT.PK_ONJ)에 위배됩니다

-- ignore_row_on_dupkey_index 힌트를 사용하자.
SQL> SELECT a.index_name, a.column_name, b.visibility

FROM user_ind_columns a, user_indexes b

WHERE a.table_name = 'ONJ'

AND a.index_name = b.index_name

AND a.column_name = 'ID'

INDEX_NAME COLUMN_NAME VISIBLITY
----------------------------------------------------
PK_ONJ ID VISIBLE

SQL> insert /*+ ignore_row_on_dupkey_index(onj PK_ONJ) */ into onj select * from onj_old;
2 개의 행이 만들어졌습니다.

SQL> commit;

SQL> select * from onj;

ID NAME
---------- --------------------
1 1길동
2 2길동
3 3길동
4 4길동
5 5길동

#IGNORE_ROW_ON_DUPKEY_INDEX, #DML에러로깅, #DML에러로그, #오라클동영상, #INSERT힌트, #오라클교육, #오라클학원, #SQL학원, #SQL교육, IGNORE_ROW_ON_DUPKEY_INDEX, DML에러로깅, DML에러로그, 오라클동영상, INSERT힌트, 오라클교육, 오라클학원, SQL학원, SQL교육,

CHANGE_DUPKEY_ERROR_INDEX힌트, PK, UK 중복시 오라클 에러코드를 변경하는 힌트, 오라클힌트교육, SQL교육, 오라클학원동영상

http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=325

CHANGE_DUPKEY_ERROR_INDEX힌트, PK, UK 중복시 오라클 에러코드를 변경하는 힌트, 오라클힌트교육, SQL교

CHANGE_DUPKEY_ERROR_INDEX힌트, PK, UK 중복시 오라클 에러코드를 변경하는 힌트, 오라클힌트교육, SQL교육ORACLE HINTPK, UK 중복시 오라클 에러코드를 변경하는 힌트 (CHANGE_DUPKEY_ERROR_INDEX)실습 : 오라클19C 오

ojc.asia

https://youtu.be/Wy7m6DZEoSQ

https://www.youtube.com/watch?v=32YaMykHAbc&list=PLxU-iZCqT52Dlz0zGgFiq1mBz21arFUHS&index=34

ORACLE HINT

PK, UK 중복시 오라클 에러코드를 변경하는 힌트 (CHANGE_DUPKEY_ERROR_INDEX)

실습 : 오라클19C

오라클의 Online Application Upgrade를 위한 힌트는 INSERT 또는 UPDATE시 키 중복의 경우에 대응하기 위한 힌트이다. 먼저 CHANGE_DUPKEY_ERROR_INDEX에 대해 알아보자.

CHANGE_DUPKEY_ERROR_INDEX 힌트구문은 Oracle11g R2에서 새롭게 제시한 힌트구문으로 힌트에서 인자로 기술한 칼럼에 대해 키 중복 에러가 발생할 경우 "ORA-00001: unique constraint (XXX.YYY) violated" 대신에 "ORA-38911: unique constraint (XXX.YYY) violated"를 보여준다.

create table ojc (

id number constraint ojc_pk primary key,

name varchar2(20) not null constraint ojc_uk unique);

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

insert into ojc values (1,'1길동');

insert into ojc values (2,'2길동');

COMMIT;

INSERT를 또 실행하는 경우

insert into ojc values (1,'1길동');

insert into ojc values (2,'2길동');

insert into ojc values (1,'1길동');

insert into ojc values (1,'1길동')

1행에 오류:

ORA-00001: 무결성 제약 조건(SCOTT.OJC_PK)에 위배됩니다

-- 이번에는 힌트를 사용해 보자. 힌트 안의 인자에 기인하여 에러메시지가 달라진다.

-- 인자로는 칼럼 명 하나, 또는 제약조건의 이름을 넣어주면 된다.

-- id칼럼에 중복 키 오류가 나면... ORA-38911 에러코드가 출력되고 다른 칼럼에서 중복 키 오류가 발생하면 ORA-00001이 출력된다.

insert /*+ change_dupkey_error_index(ojc(id)) */ into ojc values (2,'2길동');

insert /*+ change_dupkey_error_index(ojc(id)) */ into ojc values (2,'2길동')

1행에 오류:

ORA-38911: 무결성 제약 조건(SCOTT.OJC_PK)에 위배됩니다

-- ID 칼럼은 중복 키 오류가 아니고, NAME 칼럼이 중복 키 오류이므로 ORA-00001이 출력된다.

insert /*+ change_dupkey_error_index(ojc(id)) */ into ojc values (3,'2길동');

insert /*+ change_dupkey_error_index(ojc(id)) */ into ojc values (3,'2길동')

1행에 오류:

ORA-00001: 무결성 제약 조건(SCOTT.OJC_UK)에 위배됩니다

-- NAME 칼럼이 중복 키 오류이므로 ORA-38911이 출력된다.

insert /*+ change_dupkey_error_index(ojc(name)) */ into ojc values (2,'2길동');

insert /*+ change_dupkey_error_index(ojc(name)) */ into ojc values (2,'2길동')

1행에 오류:

ORA-38911: 무결성 제약 조건(SCOTT.OJC_UK)에 위배됩니다

-- PK인 ID칼럼이 중복 키 오류이므로 ORA-38911이 출력된다.

insert /*+ change_dupkey_error_index(ojc, ojc_pk) */ into ojc values (2,'2길동');

insert /*+ change_dupkey_error_index(ojc, ojc_pk) */ into ojc values (2,'2길동')

1행에 오류:

ORA-38911: 무결성 제약 조건(SCOTT.OJC_PK)에 위배됩니다

-- UK인 NAME칼럼이 중복 키 오류이므로 ORA-38911이 출력된다.

insert /*+ change_dupkey_error_index(ojc, ojc_uk) */ into ojc values (2,'2길동');

insert /*+ change_dupkey_error_index(ojc, ojc_uk) */ into ojc values (2,'2길동')

1행에 오류:

ORA-38911: 무결성 제약 조건(SCOTT.OJC_UK)에 위배됩니다

이 힌트를 이용하면 PL/SQL 등에서 에러 메시지를 파싱하지 않고 unique key 오류를 구별할 수 있다. 아래 예문을 보자.

create or replace procedure OJC_TEST

name_uk_error exception;

pragma exception_init(name_uk_error, -38911);

begin

insert /*+ change_dupkey_error_index (ojc(name)) */ into ojc values (1,'1길동');

exception

when name_uk_error then

dbms_output.put_line('이름 중복...');

when dup_val_on_index then

dbms_output.put_line('다른 unique key 제약조건 위배...');

end;

#CHANGE_DUPKEY_ERROR_INDEX, #ORACLE교육, #오라클힌트, #ORACLE학원, #SQL학원, #오라클학원, #SQL힌트, #오라클힌트, #오라클동영상,

CHANGE_DUPKEY_ERROR_INDEX, ORACLE교육, 오라클힌트, ORACLE학원, SQL학원, 오라클학원, SQL힌트, 오라클힌트, 오라클동영상,

오라클TopN쿼리, 상위N명구하기, 오라클12 행제한구,인라인뷰, WITH구문, ROWNUM이용(오라클학원/ORACLE학원교육)

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=703

오라클TopN쿼리, 상위N명구하기, 오라클12 행제한구,인라인뷰, WITH구문, ROWNUM이용

오라클TopN쿼리, 상위N명구하기, 오라클12 행제한구,인라인뷰, WITH구문, ROWNUM이용ORACLE SQL급여 상위 N명 구하기실습 : ORACLE 19C실습데이터 : http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=117&page=7실습 ; 사

ojc.asia

https://youtu.be/32YaMykHAbc

https://www.youtube.com/watch?v=BVeBkyIWUrU&list=PLxU-iZCqT52Dlz0zGgFiq1mBz21arFUHS&index=33

ORACLE SQL

급여 상위 N명 구하기

실습 : ORACLE 19C

실습데이터 : http://ojc.asia/bbs/board.php?bo_table=LecHINT&wr_id=117&page=7

실습 ; 사원테이블에서 급여 상위3명을 추출 하세요.

실습테이블 소개

-- 2000만건

SELECT * FROM MYEMP1;

-- 급여 컬럼에 생성된 인덱스 확인

SELECT A.INDEX_NAME, A.COLUMN_NAME, B.VISIBILITY

FROM USER_IND_COLUMNS A, USER_INDEXES B

WHERE A.TABLE_NAME = 'MYEMP1'

AND A.INDEX_NAME = B.INDEX_NAME

AND A.COLUMN_NAME = 'SAL';

IDX_MYEMP1_SAL SAL VISIBLE

1. 인라인뷰 & ROWNUM 이용

-- 아래처럼 하지 마세요!
-- ORDER BY는 맨 나중에 실행됩니다.
SELECT ENAME, SAL
FROM MYEMP1
WHERE ROWNUM < 8
ORDER BY SAL DESC;

-- 2.5초 SAL 컬럼 인덱스를 이용못하고 MYEMP1 테이블 FULL SCAN함
SELECT *
FROM (
    SELECT ENAME,
                   SAL
    FROM MYEMP1
    ORDER BY SAL DESC
    ) T1
WHERE ROWNUM < 8;

-- 0초, WHERE절에 SAL 컬럼이 출현함에 따라 SAL 컬럼 인덱스를 이용함
SELECT *
FROM (
    SELECT ENAME,
                   SAL
    FROM MYEMP1
    WHERE SAL > 0
    ORDER BY SAL DESC
    ) T1
WHERE ROWNUM < 8;

-- 0초, 힌트이용, ORDER BY 생략
-- SAL 컬럼 인덱스가 오름차순으로 생성되어 있어서 역순으로 스캔
SELECT *
FROM (
    SELECT /*+ INDEX_DESC(MYEMP1 IDX_MYEMP1_SAL) */
           ENAME,
           SAL
    FROM MYEMP1
    WHERE SAL > 0
    ) T1
WHERE ROWNUM < 8;

2.ORACLE 12C 행제한구 이용

-- 2.5초 SAL 컬럼 인덱스를 이용못하고 MYEMP1 테이블 FULL SCAN함
-- 중복되는 급여가 있더라도 무조건 6개 자름
SELECT ENAME, SAL
FROM MYEMP1
ORDER BY SAL DESC
FETCH FIRST 7 ROWS ONLY;

-- 0초, SAL 컬럼 인덱스를 이용함
-- 중복되는 급여가 있더라도 무조건 6개 자름
SELECT ENAME, SAL
FROM MYEMP1
WHERE SAL > 0
ORDER BY SAL DESC
FETCH FIRST 7 ROWS ONLY;

-- 0초, 오라클 힌트 이용, SAL 컬럼 인덱스를 이용함
-- ORDER BY절 생략(힌트구문 때문에 인덱스 영역에서 역순으로 이미 가져오니 생략됨)
SELECT /*+ INDEX_DESC(MYEMP1 IDX_MYEMP1_SAL) */
ENAME, SAL
FROM MYEMP1
WHERE SAL > 0
FETCH FIRST 7 ROWS ONLY;

-- 0초, SAL 컬럼 인덱스를 이용함
-- 중복되는 급여가 있으면 모두 SELECT함
SELECT ENAME, SAL
FROM MYEMP1
WHERE SAL > 0
ORDER BY SAL DESC
FETCH FIRST 4 ROWS WITH TIES;

3.WITH구문과 ROWNUM 이용

-- 2.5초 SAL 컬럼 인덱스를 이용못하고 MYEMP1 테이블 FULL SCAN함
WITH A AS
  ( SELECT ENAME, SAL
    FROM MYEMP1
    ORDER BY SAL DESC)
SELECT ENAME, SAL
FROM A
WHERE rownum <= 3;

-- 0초, WHERE절에 SAL 컬럼이 출현함에 따라 SAL 컬럼 인덱스를 이용함
WITH A AS
  ( SELECT ENAME, SAL
    FROM MYEMP1
    WHERE SAL > 0
    ORDER BY SAL DESC)
SELECT ENAME, SAL
FROM A
WHERE rownum <= 3;

-- 0초, 힌트이용, ORDER BY 생략
-- SAL 컬럼 인덱스가 오름차순으로 생성되어 있어서 역순으로 스캔
WITH A AS
  ( SELECT /*+ INDEX_DESC(MYEMP1 IDX_MYEMP1_SAL) */
           ENAME, SAL
    FROM MYEMP1
    WHERE SAL > 0 )
SELECT ENAME, SAL
FROM A
WHERE rownum <= 3;

#TopN쿼리, #상위N명쿼리, #오라클교육, #WITH구문, #인라인뷰, #행제한구문, #오라클강의, #오라클학원, #ORACLE교육, #ORACLE동영상, #SQL교육, #SQL학원, TopN쿼리, 상위N명쿼리, 오라클교육, WITH구문, 인라인뷰, 행제한구문, 오라클강의, 오라클학원, ORACLE교육, ORACLE동영상, SQL교육, SQL학원,

2021년 12월 11일 토요일

https://youtu.be/b-IxbRmwpGE오라클 데이터 입력시 중복되는 경우 중복되지 않는 데이터만 입력, IGNORE_ROW_ON_DUPKEY_INDEX 힌트, DML Error Logging사용법(자바학원/오라클학원)

DML Error Logging 이용

CHANGE_DUPKEY_ERROR_INDEX힌트, PK, UK 중복시 오라클 에러코드를 변경하는 힌트, 오라클힌트교육, SQL교육, 오라클학원동영상

오라클TopN쿼리, 상위N명구하기, 오라클12 행제한구,인라인뷰, WITH구문, ROWNUM이용(오라클학원/ORACLE학원교육)

(C#교육동영상)C# ADO.NET 실습 ODP.NET/ODAC 설치 오라클 함수 호출 실습, C#학원, WPF학원, 닷넷학원, 자바학원

신고하기

태그