Hi

Thank you for the opportunity

Large table having 2 columns with 2 Billions of data and growing and no partition. One insert trigger which generates nextval from sequence for value in Column2 when insert in column1 is initiated.

Column1 has primary key so we search if value doesn't exist in Column1 then insert and then eventually trigger generate value for column2.

In next instance again table is queried on column1 to get column2 value.

We use another ETL tool so number of records we process , that number of times we call value existence in column1 and retrieve value of column2.

I came with approach that remove trigger when external tool insert data into table.

Insead i write stored proc to take value for column1 from external ETL , store nextval into variable v_sequencevalue, insert into table values(IN value1, v_sequencevalue);

assign v_sequencevalue to OUT variable and store into final table so I can avoid one extra call to this large table to fetch value of column2.

Is this good approach in multiple data processing environment?

create sequence s;
create table t (
  c1 int,
  c2 int
    default s.nextval
);

var v number;
insert into t values ( 1, default )
  returning c2 into :v;

print :v

         V
----------
         1

insert into t values ( 999, s.nextval )
  returning c2 into :v;

print :v

         V
----------
         2

drop table t 
  cascade constraints purge;
create table t (
  c1 int,
  c2 int
);

create or replace trigger trig
before insert on t
for each row
when ( new.c2 is null )
begin
  dbms_output.put_line ( 'Fired!' );
  :new.c2 := s.nextval;
end;
/

insert into t values ( 100, s.nextval )
  returning c2 into :v;
  
print :v

         V
----------
         3
  
insert into t ( c1 ) values ( 101 )
  returning c2 into :v;
  
Fired!
  
print :v

         V
----------
         4