Hi All,
Using Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Inserting 103K rows is taking 6 minutes.
The table configuration is :
CREATE TABLE "SAMPLE"."MY_TABLE"
( ... 25 fields,
CONSTRAINT "PK_MY_ID" PRIMARY KEY ("MY_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SAMPLE_USERS" ENABLE,
CONSTRAINT "FK_MEF_BSO" FOREIGN KEY ("BS_ID")
REFERENCES "SAMPLE"."BSO" ("BSO_ID") ENABLE,
CONSTRAINT "FK_MEF_LI" FOREIGN KEY ("LD_ID")
REFERENCES "SAMPLE"."LI" ("LI_ID") ENABLE,
CONSTRAINT "FK_MEF_MODIFIED_BY" FOREIGN KEY ("MODIFIED_BY")
REFERENCES "SAMPLE"."USERS" ("USER_ID") ENABLE,
CONSTRAINT "FK_MEF_PER" FOREIGN KEY ("PER")
REFERENCES "SAMPLE"."PROGRAM_E" ("PER") ENABLE,
CONSTRAINT "FK_MEF_PR" FOREIGN KEY ("PR_ID")
REFERENCES "SAMPLE"."PR" ("PR_ID") ENABLE,
CONSTRAINT "FK_MEF_UNIT_PROGRAM" FOREIGN KEY ("UNIT_PROGRAM_ID")
REFERENCES "SAMPLE"."UNIT_PROGRAM" ("UNIT_PROGRAM_ID") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SAMPLE_USERS" ;
It also contains indexes for the primary key and for the foreign keys.
I disabled the logging and there are no triggers.
The inserts are in loop routine in a coldfusion cfc file, the logic code is:
<cfquery>
execute immediate ' alter session enable parallel dml ';
DELETE FROM my_table
WHERE my_id = XX;
</cfquery>
<cfloop from="1" to="5000" index="x">
<cfquery>
Begin
insert into my_table (25 fields) values (25 values);
insert into my_table (25 fields) values (25 values);
insert into my_table (25 fields) values (25 values);
... insert 5000 lines until gets the 102K ....
End;
</cfquery>
</cfloop>
<cfquery>
commit;
execute immediate ' alter session disable parallel dml ';
</cfquery>
As you see, the loop is set for 5K records at the time, there is not improved if I add the commit after every 5K loop.
I am building every insert row with dynamic code.
Is there a way to improved this insert process?
Thanks in advanced.