For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.
{noformat} (lowercase, curly brackets, no spaces) so that the text appears in
. "Science is more than a body of knowledge; it is a way of thinking" Carl Sagan
SQL> select dbms_metadata.get_ddl('TABLE','PS_COMB_EXP_TAO4','SYSADM') from dual; DBMS_METADATA.GET_DDL('TABLE','PS_COMB_EXP_TAO4','SYSADM') -------------------------------------------------------------------------------- CREATE TABLE "SYSADM"."PS_COMB_EXP_TAO4" ( "PROCESS_INSTANCE" NUMBER(10,0) NOT NULL ENABLE, "SETID" VARCHAR2(5) NOT NULL ENABLE, "TREE_EFFDT" DATE, "PROCESS_GROUP" VARCHAR2(10) NOT NULL ENABLE, "COMBINATION" VARCHAR2(10) NOT NULL ENABLE, "SEQUENCE_NBR_6" NUMBER(*,0) NOT NULL ENABLE, "CHARTFIELD" VARCHAR2(18) NOT NULL ENABLE, "RANGE_FROM_30" VARCHAR2(30) NOT NULL ENABLE, "EFFDT_FROM" DATE, "EFFDT_TO" DATE ) PCTFREE 10 PCTUSED 80 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "GLAPP" SQL> select dbms_metadata.get_ddl('INDEX','PSACOMB_EXP_TAO4','SYSADM') from dual; DBMS_METADATA.GET_DDL('INDEX','PSACOMB_EXP_TAO4','SYSADM') -------------------------------------------------------------------------------- CREATE INDEX "SYSADM"."PSACOMB_EXP_TAO4" ON "SYSADM"."PS_COMB_EXP_TAO4" ("PROCESS_INSTANCE", "SETID", "PROCESS_GROUP", "CHARTFIELD", "RANGE_FROM_30", "EFFDT_FROM", "EFFDT_TO", "COMBINATION", "SEQUENCE_NBR_6") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 40960 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSINDEX" SQL> select dbms_metadata.get_ddl('INDEX','PSBCOMB_EXP_TAO4','SYSADM') from dual; DBMS_METADATA.GET_DDL('INDEX','PSBCOMB_EXP_TAO4','SYSADM') -------------------------------------------------------------------------------- CREATE INDEX "SYSADM"."PSBCOMB_EXP_TAO4" ON "SYSADM"."PS_COMB_EXP_TAO4" ("PROCESS_INSTANCE", "SETID", "PROCESS_GROUP", "CHARTFIELD", "COMBINATION", "SEQUENCE_NBR_6", "RANGE_FROM_30", "EFFDT_FROM", "EFFDT_TO") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 40960 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PSINDEX" SQL>
DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE >= 0;
INSERT INTO PS_COMB_EXP_TAO4 (PROCESS_INSTANCE, SETID, TREE_EFFDT, PROCESS_GROUP, COMBINATION, SEQUENCE_NBR_6, CHARTFIELD, RANGE_FROM_30, EFFDT_FROM, EFFDT_TO) SELECT DISTINCT some_constant_number ..... from multiple tables joined
DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE >= 0; INSERT INTO PS_COMB_EXP_TAO4 ... SELECT DISTINCT some_constant_number ..... commit;
INSERT INTO PS_COMB_EXP_TAO4 ... SELECT DISTINCT some_constant_number .....
It seems that the process id (PROCESS_INSTANCE) is obtained by another process (the Process Scheduler). Therefore, it is already committed in another transaction before it is passed to this program (which is run by the Process Scheduler). This is the "some_constant_value" that is inserted as the same value in every row at each run (but a new value for a run by another / next session).
It can so happen that while one session is still processing with PS_COMB_EXP_TAO4, another session comes along and issues the "DELETE FROM PS_COMB_EXP_TAO4 WHERE PROCESS_INSTANCE > 0". Obviously, there is no Row Lock (TX Enqueue) because the other session cannot see the rows in PS_COMB_EXP_TAO4 that have been inserted by the first (still processing) session. Therefore, the second session issue it's own DELETE statement followed by another INSERT (with a different PROCESS_INSTANCE) value.
Since there are multiple transactions concurrently active on the table and each session issuing it's own "delete all rows" (because all rows will always be PROCESS_INSTANCE > 0), ... wouldn't the index grow as deleted entries are not reused ? (deleted entries would be reused if the index was a Unique Index). ?