Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, narayana.

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

Last updated: September 15, 2020 - 4:02 pm UTC

Version: 12.1

Viewed 1000+ 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 Connor 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

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

what about NOAPPEND

Narayana, September 14, 2020 - 11:09 am UTC

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
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?

NARAYANA, September 16, 2020 - 7:31 pm UTC

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.