1 million can I load data into sqlldr faster?

Setp-1 to generate data files:

Getobject. SQL

[SQL]

1. SELECTA. owner |',"'| A. object_name |'",'| A. object_id |','| To_char (a. created,'Yyyy-mm-dd hh24: mi: ss') |','| A. status
2. FROMDba_objects ,(SELECTRownum rnFROMDualConnect BYRownum <= 23) B;

Call. SQL

[SQL]

1. SETEchoOff
2. SETTermOff
3. SETLine 100 pages 0
4. SETFeedbackOff
5. SETHeadingOff
6. Spool C: \ Oracle \ script \ ldr_object.csv
7. @ C: \ oracle \ script \ getobject. SQL
8. SpoolOff
9. SETHeadingOn
10. SETFeedbackOn
11. SETTermOn
12. SETEchoOn

Step 0 initialize the environment:

Createobject. SQL

[SQL]

1. CREATE TABLEObjects (
2. OwnerVarchar(30 ),
3. Object_nameVarchar(50 ),
4. Object_id NUMBER,
5. CreatedDate,
6. Status VARCHAR2 (10)
7. );
8. CREATE INDEXIdx_obj_owner_nameOnObjects (owner, object_name );

Step 1: import for the first time

Ldr_object.ctl

[SQL]

1. LoadData
2. Infile ldr_object.csv
3. Truncate Into TableObjects
4. Fields terminatedBy ","Optionally enclosedBy '"'
5. (
6. Owner,
7. Object_name,
8. Object_id,
9. CreatedDate 'Yyyy-mm-dd hh24: mi: ss',
10. Status"Substr (: status, 1, 5 )"
11. )

C: \ oracle \ script> sqlldr scott/tiger control = ldr_object.ctl errors = 10

Ldr_object.log

[SQL]

1. SQL * Loader: Release 10.2.0.1.0-ProductionOnFriday January 20 02:16:56 2012
3. Copyright (c) 1982,200 5, Oracle.AllRights reserved.
5. Control File: ldr_object.ctl
6. Data File: ldr_object.csv
7. Error file: ldr_object.bad
8. Obsolete file: Not specified
10. (All records can be discarded)
12. Number of objects to be loaded:ALL
13. Number of to be skipped: 0
14. Allowed error: 10
15. Bound array: 64 rows, up to 256000 bytes
16. Continue: Unspecified
17. Path used: General
19. Table OBJECTS, loaded from each logical record
20. Insert option for this tableTRUNCATEEffective
22. Column name location length abort packaging Data Type
23. --------------------------------------------------------------------------
24. OWNERFIRST*, O (")CHARACTER
25. OBJECT_NAMENEXT*, O (")CHARACTER
26. OBJECT_IDNEXT*, O (")CHARACTER
27. CREATEDNEXT*, O (")DATEYyyy-mm-dd hh24: mi: ss
28. STATUSNEXT*, O (")CHARACTER
29. Column SQL string:"Substr (: status, 1, 5 )"
32. Table OBJECTS:
33. 1033298 rows are loaded successfully.
34. Zero rows are not loaded due to data errors.
35. Since allWHENClause failed. 0 rows are not loaded.
36. Because all fields are empty, 0 rows are not loaded.
39. Space allocated to the bound array: 82560 bytes (64 rows)
40. Number of bytes read from the buffer: 1048576
42. Total number of skipped logical records: 0
43. Total number of logical records read: 1033298
44. Total number of rejected logical records: 0
45. Total number of discarded logical records: 0
47. Started running from Friday January 20 02:16:56 2012
48. The operation ended at 02:19:02, January 1, 2012 at on Friday.
50. Elapsed time: 00: 02: 05.55
51. CPU time: 00: 00: 10.38

1 2 3 Next Page