Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, narayana.

Asked: September 12, 2020 - 12:29 pm UTC

Answered by: Connor McDonald - Last updated: September 15, 2020 - 4:02 pm UTC

Category: SQL - Version: 12.1

Viewed 100+ times

You Asked

HI,

CREATE TABLE TEMP_TEST
(
  ID  NUMBER(10)
)

ALTER TABLE TEMP_TEST ADD (
  CONSTRAINT temp_test_pk
  UNIQUE (ID)
  );



Scenario:1:
truncate table TEMP_TEST;
ALTER SESSION ENABLE PARALLEL DML;


INSERT INTO  /*+ NOAPPEND PARALLEL(5) */ TEMP_TEST
SELECT /*+ PARALLEL */DISTINCT BUCKET FROM source
LOG ERRORS INTO ERR$_TEMP_TEST ('insert failed') REJECT LIMIT UNLIMITED;


Scenario:2:
truncate table TEMP_TEST;
ALTER SESSION ENABLE PARALLEL DML;


INSERT INTO  /*+ NOAPPEND PARALLEL(5) */ TEMP_TEST
SELECT DISTINCT BUCKET FROM source
LOG ERRORS INTO ERR$_TEMP_TEST ('insert failed') REJECT LIMIT UNLIMITED;



Scenario:1 is failing with unique constraint error instead of error records inserting into error table,
but scenario:2 error records are inserting into ERR$_TEMP_TEST?

The only difference between these two is PARALLEL hint in select statement.




and we said...

As per the documentation:

"Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation."

and whenever you perform *parallel* DML it is implicitly a direct load operation.

Its one of those unfortunate restrictions

and you rated our response

  (2 ratings)

Reviews

what about NOAPPEND

September 14, 2020 - 11:09 am UTC

Reviewer: Narayana from IND

Hi,

I read the same in "Database SQL reference Guide"


https://docs.oracle.com/database/121/SQLRF/statements_9015.htm#SQLRF01604

Restrictions on DML Error Logging

    The following conditions cause the statement to fail and roll back without invoking the error logging capability:

        Violated deferred constraints.

        Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.

        Any update operation UPDATE or MERGE that raises a unique constraint or index violation.




But NOAPPEND hint should override the direct-path insert of PARALLEL. right?



Chris Saxon

Followup  

September 15, 2020 - 4:02 pm UTC

I'm not seeing this effect:

create table t1 as 
  select mod ( level, 10 ) c1 
  from   dual
  connect by level <= 100;
  
create table t2 ( c1 primary key ) as
  select * from t1
  where  1 = 0;
  
exec dbms_errlog.create_error_log('t2', 'errlog');

insert into t2 values ( 1 );
commit;

alter session enable parallel dml;

insert /*+ noappend parallel */into t2 
  select /*+ parallel */distinct c1 from t1
  log errors into errlog ( 'insert failed' )
  reject limit unlimited;

commit;

select count(*) from t2;

COUNT(*)   
         10 

select count(*) from errlog;

COUNT(*)   
          1 

truncate table t2;
insert into t2 values ( 1 );
commit;
  
insert /*+ noappend parallel */into t2 
  select distinct c1 from t1
  log errors into errlog ( 'insert failed' )
  reject limit unlimited;
  
commit;
  
select count(*) from t2;

COUNT(*)   
         10 

select count(*) from errlog;

COUNT(*)   
          2 


Could you post a complete example - including DDL for the source table and sample data?

September 16, 2020 - 7:31 pm UTC

Reviewer: NARAYANA

SOURCE TABLE DDL:

CREATE TABLE source
(
  s1                  VARCHAR2(60 CHAR)     NOT NULL,
  s2                    VARCHAR2(60 CHAR)     NOT NULL,
  i1                    VARCHAR2(60 CHAR)     NOT NULL,
  BUCKET                  NUMBER(12)            NOT NULL,
    M1          NUMBER(11),
   SYS_NC_TYPE             VARCHAR2(25 CHAR),

)
TABLESPACE DATA
PCTUSED    0
PCTFREE    50
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;


CREATE UNIQUE INDEX C9786743126_1_PK ON SOURCE
(S1, S2, I1, BUCKET)
LOGGING
TABLESPACE DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX  I7364638085_790 ON  SOURCE
(M1)
LOGGING
TABLESPACE DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX  I7364638085_791 ON  SOURCE
(SYS_NC_TYPE)
LOGGING
TABLESPACE DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX  I7364638085_792 ON  SOURCE
(S2, S1, I1)
LOGGING
TABLESPACE DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

CREATE INDEX  I7364638085_793 ON  SOURCE
(BUCKET)
LOGGING
TABLESPACE DATA
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          128K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );
     
     
     

ALTER TABLE  SOURCE ADD (
  CONSTRAINT C9786743126_8_FK 
  FOREIGN KEY (S1, S2, I1) 
  REFERENCES  parent1 (S1, S2, I1)
  ENABLE VALIDATE
,  CONSTRAINT C9786743126_9_FK 
  FOREIGN KEY (BUCKET) 
  REFERENCES  TIME_MASTER (BUCKET)
  ENABLE VALIDATE);



Sample data:
BUCKET

20180423
20180601
20180625
20180701
20180702
20180730
20180917
20181101
20190429

select count(*) from source; --->93588521

SELECT Distinct bucket FROM source; -->224


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.