There are three tables

CREATE TABLE A ( ID NUMBER, B_ID NUMBER NOT NULL, ........... CONSTRAINT FK_A_B FOREIGN KEY (B_ID) REFERENCES B (ID) ENABLE VALIDATE, CONSTRAINT PK_A PRIMARY KEY (ID) ENABLE VALIDATE ) CREATE TABLE B ( ID NUMBER, TYPE NUMBER NOT NULL, ........... CONSTRAINT PK_B PRIMARY KEY (ID) ENABLE VALIDATE ) CREATE TABLE C ( ID NUMBER, A_ID NUMBER NOT NULL, DATA NUMBER NOT NULL, ........... CONSTRAINT FK_C_A FOREIGN KEY (A_ID) REFERENCES A (ID) ENABLE VALIDATE, CONSTRAINT PK_A PRIMARY KEY (ID) ENABLE VALIDATE )

( A_ID pair, DATA not unique and can be repeated up to three times)

After (or during) data import into these tables, it is necessary for all new records to insert records into such a table.

 CREATE TABLE D ( ID NUMBER GENERATED ALWAYS AS IDENTITY, A_ID NUMBER NOT NULL, DATA NUMBER, CONSTRAINT FK_D_A FOREIGN KEY (A_ID) REFERENCES A (ID) ON DELETE CASCADE ENABLE VALIDATE, CONSTRAINT PK_D PRIMARY KEY (ID) ENABLE VALIDATE, CONSTRAINT UNQ_D UNIQUE (A_ID, "DATA") ENABLE VALIDATE )

But insert by tricky algorithm.

  1. For each new entry in Tables A and C through Table A , get the value B.TYPE
  2. For this value, call the function my_func , which returns 0 or 1

  3. If the function returns 0, then execute the query

     INSERT INTO D (A_ID, DATA) SELECT ID, NULL FROM A WHERE ID = current_id;

  4. If the function returns 1, then execute such a request

     INSERT INTO D (A_ID, DATA) SELECT DISTINCT A_ID, DATA FROM C WHERE A_ID = current_a_id;

The function my_func is

 CREATE FUNCTION my_func (in_type IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN IF (in_type IN (5, 13, 17, 20, 65, 79)) THEN RETURN 1; ELSE RETURN 0; END IF; END;

How to solve this problem more effectively? Given that a situation is possible when importing the same data can occur in parallel (if the data already exists, then nothing needs to be done. Rather, you need to update other fields, but they do not belong to this algorithm). The task is complicated by the fact that a situation is possible (although it is unlikely) when additional entries are added to existing entries in С that refer to one entry from A that refer to the same entry and for my_func() = 1 you will need to add entries to D

Thousands of entries in A can come in one import package (it’s not a fact that they will all be new). Up to 10 entries from C can refer to each entry from A

  • And what import is performed? - Viktorov
  • Some kind of nonsense ... If the function returned X, then execute the query - insert into the table D all the records from A or C? I guess that somehow you need to correlate by A_ID to the inserted record ... then the task is complicated by the fact that - there is even worse - we insert in A, the function returns 1, we climb in C, and there is such an A_ID ... - Akina
  • What actually do you see any problems? " if there is already data - do nothing ", do not do it, insert and will not try to go through, it means nothing else will happen. And as for the rest, there is no “optimal” path, there is only one existing one, the trigger on Tables A and C should work through these situations and take actions. If the my_func function is deterministic and at the same time takes a long time, it may be worthwhile to store ready-made values ​​0 and 1 for all B.TYPE somewhere. PS although there is an excellent optimization, based on the structure shown, table D is not needed at all - Mike
  • @Akina is not everything , but the current view is Anton Shchyrov
  • @Viktorov External samopisny utility. The utility for insert / update calls DB procedures - Anton Shchyrov

1 answer 1

Total stopped at AFTER INSERT trigger for C

 CREATE TRIGGER TR_C_AI AFTER INSERT ON C REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE var_action NUMBER; BEGIN SELECT my_func(B."TYPE") INTO var_action FROM A LEFT JOIN B ON ( A.B_ID = B.ID ) WHERE A.ID = :NEW.A_ID; IF (var_action = 1) THEN MERGE INTO D USING ( SELECT :NEW.A_ID AS A_ID, :NEW.DATA AS DATA FROM dual ) sub ON ( D.A_ID = sub.A_ID AND D.DATA = sub.DATA ) WHEN NOT MATCHED THEN INSERT ( A_ID, DATA ) VALUES ( :NEW.A_ID, :NEW.DATA ); ELSE MERGE INTO D USING ( SELECT :NEW.A_ID AS A_ID FROM dual ) sub ON ( D.A_ID = sub.A_ID ) WHEN NOT MATCHED THEN INSERT ( A_ID, DATA ) VALUES ( :NEW.A_ID, NULL ); END IF; END;