DROP TABLE dept_loader PURGE ; DROP TABLE dept_loader_log PURGE ; DROP TABLE dept_loader_clone PURGE ; DROP TABLE loc_loader PURGE ; CREATE TABLE loc_loader( locno NUMBER, locname VARCHAR2(20) ); ALTER TABLE loc_loader ADD CONSTRAINT pk_loc_loader PRIMARY KEY (locno) ; ALTER TABLE loc_loader ADD CONSTRAINT uc_loc_loader UNIQUE (locname); INSERT INTO loc_loader VALUES (1,'TEPEBASI'); INSERT INTO loc_loader VALUES (2,'GEBZE'); INSERT INTO loc_loader VALUES (3,'MALTEPE'); exec dbms_stats.gather_table_stats(USER, 'loc_loader'); COMMIT; CREATE TABLE dept_loader( deptno NUMBER, dname VARCHAR2(32), locno NUMBER); ALTER TABLE dept_loader ADD CONSTRAINT pk_dept_loader PRIMARY KEY (deptno) ; ALTER TABLE dept_loader ADD CONSTRAINT uc_dept_loader UNIQUE (dname); ALTER TABLE dept_loader ADD CONSTRAINT fk_dept_loader FOREIGN KEY (locno) REFERENCING loc_loader (locno) ; INSERT INTO dept_loader VALUES (10,'FINANCE',1); INSERT INTO dept_loader VALUES (20,'SALES',1); INSERT INTO dept_loader VALUES (30,'MARKETING',1); INSERT INTO dept_loader VALUES (40,'RESEARCH AND DEVELOPMENT',2); INSERT INTO dept_loader VALUES (50,'NETWORK OPERATIONS',3); exec dbms_stats.gather_table_stats(USER, 'dept_loader'); COMMIT; SELECT dl.dname, ll.locname FROM dept_loader dl, loc_loader ll WHERE dl.locno = ll.locno; DNAME LOCNAME -------------------------------- -------------------- FINANCE TEPEBASI SALES TEPEBASI MARKETING TEPEBASI RESEARCH AND DEVELOPMENT GEBZE NETWORK OPERATIONS MALTEPE CREATE TABLE dept_loader_log( msg VARCHAR2(32), dat DATE DEFAULT sysdate); TRUNCATE TABLE dept_loader_log REUSE STORAGE ; exec dbms_stats.gather_table_stats(USER, 'dept_loader_log'); COMMIT; CREATE OR REPLACE TRIGGER trg_dept_loader AFTER INSERT OR UPDATE OR DELETE ON dept_loader FOR EACH ROW DECLARE vMsg VARCHAR2(30) := 'Trigger Fired'; BEGIN IF INSERTING THEN INSERT INTO dept_loader_log VALUES (vMsg || ' When Inserting', SYSDATE); ELSIF UPDATING THEN INSERT INTO dept_loader_log VALUES (vMsg || ' When Updating', SYSDATE); ELSIF DELETING THEN INSERT INTO dept_loader_log VALUES (vMsg || ' When Deleting', SYSDATE); END IF; END trg_dept_loader; / SELECT uc.constraint_name, uc.constraint_type, uc.table_name, uc.status FROM user_constraints uc WHERE uc.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS ------------------------------ --------------- ------------------------------ -------- PK_DEPT_LOADER P DEPT_LOADER ENABLED UC_DEPT_LOADER U DEPT_LOADER ENABLED FK_DEPT_LOADER R DEPT_LOADER ENABLED PK_LOC_LOADER P LOC_LOADER ENABLED UC_LOC_LOADER U LOC_LOADER ENABLED SELECT ui.index_name, ui.index_type, ui.table_name, ui.status FROM user_indexes ui WHERE ui.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); INDEX_NAME INDEX_TYPE TABLE_NAME STATUS ------------------------------ --------------------------- ------------------------------ -------- UC_DEPT_LOADER NORMAL DEPT_LOADER VALID PK_DEPT_LOADER NORMAL DEPT_LOADER VALID UC_LOC_LOADER NORMAL LOC_LOADER VALID PK_LOC_LOADER NORMAL LOC_LOADER VALID SELECT ut.trigger_name, ut.trigger_type, ut.table_name, ut.status FROM user_triggers ut WHERE ut.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS ------------------------------ ---------------- ------------------------------ -------- TRG_DEPT_LOADER AFTER EACH ROW DEPT_LOADER ENABLED TRUNCATE TABLE dept_loader_log REUSE STORAGE ; -- lets go to command prompt for sql*loader conventional load first [oracle@tcellhost ~]$ cat demo_loader.ctl LOAD DATA INFILE * INTO TABLE dept_loader TRUNCATE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (deptno, dname, locno) BEGINDATA 10,FINANCE,1 20,SALES,1 30,MARKETING,1 40,"RESEARCH AND DEVELOPMENT",2 50,"NETWORK OPERATIONS",3 [oracle@tcellhost ~]$ sqlldr userid=hr/hr control=demo_loader.ctl log=demo_loader.log SQL*Loader: Release 10.2.0.3.0 - Production on Fri Aug 17 10:18:09 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Commit point reached - logical record count 5 [oracle@tcellhost ~]$ cat demo_loader.log SQL*Loader: Release 10.2.0.3.0 - Production on Fri Aug 17 10:18:09 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Control File: demo_loader.ctl Data File: demo_loader.ctl Bad File: demo_loader.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional Table DEPT_LOADER, loaded from every logical record. Insert option in effect for this table: TRUNCATE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , O(") CHARACTER DNAME NEXT * , O(") CHARACTER LOCNO NEXT * , O(") CHARACTER Table DEPT_LOADER: 5 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 49536 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Fri Aug 17 10:18:09 2007 Run ended on Fri Aug 17 10:18:09 2007 Elapsed time was: 00:00:00.22 CPU time was: 00:00:00.03 -- back to sql*plus here for controls before direct path sql*loader load SELECT dl.dname, ll.locname FROM dept_loader dl, loc_loader ll WHERE dl.locno = ll.locno; DNAME LOCNAME -------------------------------- -------------------- FINANCE TEPEBASI SALES TEPEBASI MARKETING TEPEBASI RESEARCH AND DEVELOPMENT GEBZE NETWORK OPERATIONS MALTEPE SELECT uc.constraint_name, uc.constraint_type, uc.table_name, uc.status FROM user_constraints uc WHERE uc.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS ------------------------------ --------------- ------------------------------ -------- PK_DEPT_LOADER P DEPT_LOADER ENABLED UC_DEPT_LOADER U DEPT_LOADER ENABLED FK_DEPT_LOADER R DEPT_LOADER ENABLED PK_LOC_LOADER P LOC_LOADER ENABLED UC_LOC_LOADER U LOC_LOADER ENABLED SELECT ui.index_name, ui.index_type, ui.table_name, ui.status FROM user_indexes ui WHERE ui.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); INDEX_NAME INDEX_TYPE TABLE_NAME STATUS ------------------------------ --------------------------- ------------------------------ -------- UC_DEPT_LOADER NORMAL DEPT_LOADER VALID PK_DEPT_LOADER NORMAL DEPT_LOADER VALID UC_LOC_LOADER NORMAL LOC_LOADER VALID PK_LOC_LOADER NORMAL LOC_LOADER VALID SELECT ut.trigger_name, ut.trigger_type, ut.table_name, ut.status FROM user_triggers ut WHERE ut.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS ------------------------------ ---------------- ------------------------------ -------- TRG_DEPT_LOADER AFTER EACH ROW DEPT_LOADER ENABLED select count(*) from dept_loader_log ; COUNT(*) ---------- 5 TRUNCATE TABLE dept_loader_log REUSE STORAGE ; -- lets go to command prompt again this time for sql*loader direct path load [oracle@tcellhost ~]$ cat demo_loader_direct.ctl UNRECOVERABLE LOAD DATA INFILE * INTO TABLE dept_loader TRUNCATE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (deptno, dname, locno) BEGINDATA 10,FINANCE,1 20,SALES,1 30,MARKETING,1 40,"RESEARCH AND DEVELOPMENT",2 50,"NETWORK OPERATIONS",3 [oracle@tcellhost ~]$ sqlldr userid=hr/hr control=demo_loader_direct.ctl log=demo_loader_direct.log DIRECT=TRUE SQL*Loader: Release 10.2.0.3.0 - Production on Fri Aug 17 10:23:30 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Load completed - logical record count 5. [oracle@tcellhost ~]$ cat demo_loader_direct.log SQL*Loader: Release 10.2.0.3.0 - Production on Fri Aug 17 10:23:30 2007 Copyright (c) 1982, 2005, Oracle. All rights reserved. Control File: demo_loader_direct.ctl Data File: demo_loader_direct.ctl Bad File: demo_loader_direct.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation: none specified Path used: Direct Load is UNRECOVERABLE; invalidation redo is produced. Table DEPT_LOADER, loaded from every logical record. Insert option in effect for this table: TRUNCATE Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , O(") CHARACTER DNAME NEXT * , O(") CHARACTER LOCNO NEXT * , O(") CHARACTER Referential Integrity Constraint/Trigger Information: NULL, UNIQUE, and PRIMARY KEY constraints are unaffected. Constraint DEPT_LOADER.FK_DEPT_LOADER was disabled and novalidated before the load. Trigger HR."TRG_DEPT_LOADER" was disabled before the load. The following index(es) on table DEPT_LOADER were processed: index HR.PK_DEPT_LOADER loaded successfully with 5 keys index HR.UC_DEPT_LOADER loaded successfully with 5 keys HR."TRG_DEPT_LOADER" was re-enabled. Table DEPT_LOADER has no constraint exception table. No CHECK, REFERENTIAL constraints were re-enabled after the load. Table DEPT_LOADER: 5 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Bind array size not used in direct path. Column array rows : 5000 Stream buffer bytes: 256000 Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 0 Total logical records discarded: 0 Total stream buffers loaded by SQL*Loader main thread: 1 Total stream buffers loaded by SQL*Loader load thread: 0 Run began on Fri Aug 17 10:23:30 2007 Run ended on Fri Aug 17 10:23:31 2007 Elapsed time was: 00:00:00.74 CPU time was: 00:00:00.05 -- again lets control with sql*plus SELECT dl.dname, ll.locname FROM dept_loader dl, loc_loader ll WHERE dl.locno = ll.locno; SELECT uc.constraint_name, uc.constraint_type, uc.table_name, uc.status FROM user_constraints uc WHERE uc.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS ------------------------------ --------------- ------------------------------ -------- PK_DEPT_LOADER P DEPT_LOADER ENABLED UC_DEPT_LOADER U DEPT_LOADER ENABLED FK_DEPT_LOADER R DEPT_LOADER DISABLED <-- oops PK_LOC_LOADER P LOC_LOADER ENABLED UC_LOC_LOADER U LOC_LOADER ENABLED SELECT ui.index_name, ui.index_type, ui.table_name, ui.status FROM user_indexes ui WHERE ui.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); INDEX_NAME INDEX_TYPE TABLE_NAME STATUS ------------------------------ --------------------------- ------------------------------ -------- UC_DEPT_LOADER NORMAL DEPT_LOADER VALID PK_DEPT_LOADER NORMAL DEPT_LOADER VALID UC_LOC_LOADER NORMAL LOC_LOADER VALID PK_LOC_LOADER NORMAL LOC_LOADER VALID SELECT ut.trigger_name, ut.trigger_type, ut.table_name, ut.status FROM user_triggers ut WHERE ut.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS ------------------------------ ---------------- ------------------------------ -------- TRG_DEPT_LOADER AFTER EACH ROW DEPT_LOADER ENABLED select count(*) from dept_loader_log ; COUNT(*) ---------- 0 <-- oops -- this time lets check the direct path insert operation CREATE TABLE dept_loader_clone( deptno NUMBER, dname VARCHAR2(32), locno NUMBER); ALTER TABLE dept_loader_clone ADD CONSTRAINT pk_dept_loader_clone PRIMARY KEY (deptno) ; ALTER TABLE dept_loader_clone ADD CONSTRAINT uc_dept_loader_clone UNIQUE (dname); ALTER TABLE dept_loader_clone ADD CONSTRAINT fk_dept_loader_clone FOREIGN KEY (locno) REFERENCING loc_loader (locno) ; INSERT INTO dept_loader_clone VALUES (60,'ADMINISTRATION',1); INSERT INTO dept_loader_clone VALUES (70,'CAPABILITY MANAGEMENT',1); INSERT INTO dept_loader_clone VALUES (80,'PROCUREMENT',1); INSERT INTO dept_loader_clone VALUES (90,'SERVICE NETWORK',3); exec dbms_stats.gather_table_stats(USER, 'dept_loader_clone'); COMMIT; alter table dept_loader enable constraint FK_DEPT_LOADER ; TRUNCATE TABLE dept_loader_log REUSE STORAGE ; INSERT /*+ APPEND PARALLEL(dept_loader,2) */ INTO dept_loader NOLOGGING SELECT /*+ PARALLEL(t,2) */ * FROM dept_loader_clone t ; COMMIT; SELECT dl.dname, ll.locname FROM dept_loader dl, loc_loader ll WHERE dl.locno = ll.locno; DNAME LOCNAME -------------------------------- -------------------- PROCUREMENT TEPEBASI SALES TEPEBASI MARKETING TEPEBASI FINANCE TEPEBASI ADMINISTRATION TEPEBASI CAPABILITY MANAGEMENT TEPEBASI RESEARCH AND DEVELOPMENT GEBZE SERVICE NETWORK MALTEPE NETWORK OPERATIONS MALTEPE 9 rows selected SELECT uc.constraint_name, uc.constraint_type, uc.table_name, uc.status FROM user_constraints uc WHERE uc.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME STATUS ------------------------------ --------------- ------------------------------ -------- PK_DEPT_LOADER P DEPT_LOADER ENABLED UC_DEPT_LOADER U DEPT_LOADER ENABLED FK_DEPT_LOADER R DEPT_LOADER ENABLED PK_LOC_LOADER P LOC_LOADER ENABLED UC_LOC_LOADER U LOC_LOADER ENABLED SELECT ui.index_name, ui.index_type, ui.table_name, ui.status FROM user_indexes ui WHERE ui.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); INDEX_NAME INDEX_TYPE TABLE_NAME STATUS ------------------------------ --------------------------- ------------------------------ -------- UC_DEPT_LOADER NORMAL DEPT_LOADER VALID PK_DEPT_LOADER NORMAL DEPT_LOADER VALID UC_LOC_LOADER NORMAL LOC_LOADER VALID PK_LOC_LOADER NORMAL LOC_LOADER VALID SELECT ut.trigger_name, ut.trigger_type, ut.table_name, ut.status FROM user_triggers ut WHERE ut.table_name IN ('DEPT_LOADER', 'LOC_LOADER'); TRIGGER_NAME TRIGGER_TYPE TABLE_NAME STATUS ------------------------------ ---------------- ------------------------------ -------- TRG_DEPT_LOADER AFTER EACH ROW DEPT_LOADER ENABLED select count(*) from dept_loader_log ; COUNT(*) ---------- 4