Skip to Main Content

Breadcrumb

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.