| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> speeding up conventional path sqlldr
I am using sqlldr conventional path to load some data.
My understanding is that I cannot use direct path since my users require
access to the table at the same time as the load is in progress. Is this a
correct assumption?
The speed of the upload is slow, or I think so....10,000 rows is taking 20-30 seconds.
The first record in the file(rec id 0) is loaded into one table, the rest of the rows into another table.
The data records have a fixed length of 1100 bytes.
I am running Oracle 9.2 on a Red Hat Linux 9 box with 384 MB RAM.
Below is my control file.
Any hints on speeding up a conventional path load?
load data
into table mailpieces_header
append
when recid = "0"
(recid FILLER POSITION(1:1) INTEGER EXTERNAL,
JOB_ID CONSTANT 002744,
PRINT_STREAM_NAME POSITION(2:9) CHAR,
INSERT_NAME_01 POSITION(10:29) CHAR,
INSERT_SCAN_CODE_01 POSITION(30:41) INTEGER EXTERNAL,
INSERT_NAME_02 POSITION(42:61) CHAR,
INSERT_SCAN_CODE_02 POSITION(62:73) INTEGER EXTERNAL,
INSERT_NAME_03 POSITION(74:93) CHAR,
INSERT_SCAN_CODE_03 POSITION(94:105) INTEGER EXTERNAL,
INSERT_NAME_04 POSITION(106:125) CHAR,
INSERT_SCAN_CODE_04 POSITION(126:137) INTEGER EXTERNAL,
INSERT_NAME_05 POSITION(138:157) CHAR,
INSERT_SCAN_CODE_05 POSITION(158:169) INTEGER EXTERNAL,
INSERT_NAME_06 POSITION(170:189) CHAR,
INSERT_SCAN_CODE_06 POSITION(190:201) INTEGER EXTERNAL,
INSERT_NAME_07 POSITION(202:221) CHAR,
INSERT_SCAN_CODE_07 POSITION(222:233) INTEGER EXTERNAL,
INSERT_NAME_08 POSITION(234:253) CHAR,
INSERT_SCAN_CODE_08 POSITION(254:265) INTEGER EXTERNAL,
INSERT_NAME_09 POSITION(266:285) CHAR,
INSERT_SCAN_CODE_09 POSITION(286:297) INTEGER EXTERNAL,
INSERT_NAME_10 POSITION(298:317) CHAR,
INSERT_SCAN_CODE_10 POSITION(318:329) INTEGER EXTERNAL,
INSERT_NAME_11 POSITION(330:349) CHAR,
INSERT_SCAN_CODE_11 POSITION(350:361) INTEGER EXTERNAL,
INSERT_NAME_12 POSITION(362:381) CHAR,
INSERT_SCAN_CODE_12 POSITION(382:393) INTEGER EXTERNAL,
INSERT_NAME_13 POSITION(394:413) CHAR,
INSERT_SCAN_CODE_13 POSITION(414:425) INTEGER EXTERNAL,
INSERT_NAME_14 POSITION(426:445) CHAR,
INSERT_SCAN_CODE_14 POSITION(446:457) INTEGER EXTERNAL,
INSERT_NAME_15 POSITION(458:477) CHAR,
INSERT_SCAN_CODE_15 POSITION(478:489) INTEGER EXTERNAL,
INSERT_NAME_16 POSITION(490:509) CHAR,
INSERT_SCAN_CODE_16 POSITION(510:521) INTEGER EXTERNAL,
INSERT_NAME_17 POSITION(522:541) CHAR,
INSERT_SCAN_CODE_17 POSITION(542:553) INTEGER EXTERNAL,
INSERT_NAME_18 POSITION(554:573) CHAR,
INSERT_SCAN_CODE_18 POSITION(574:585) INTEGER EXTERNAL,
INSERT_NAME_19 POSITION(586:605) CHAR,
INSERT_SCAN_CODE_19 POSITION(606:617) INTEGER EXTERNAL,
INSERT_NAME_20 POSITION(618:637) CHAR,
INSERT_SCAN_CODE_20 POSITION(638:649) INTEGER EXTERNAL,
INSERT_NAME_21 POSITION(650:669) CHAR,
INSERT_SCAN_CODE_21 POSITION(670:681) INTEGER EXTERNAL,
ACCOUNT_NUMBER POSITION(682:689) CHAR,
SUB_ACCOUNT_NUMBER POSITION(690:697) CHAR,
PRINT_KEYLINE POSITION(698:698) CHAR,
PRINT_RECIPIENT_ADDRESS POSITION(699:699) CHAR,
PRINT_RETURN_ADDRESS POSITION(700:700) CHAR,
PRINT_MARKETING_MESSAGE POSITION(701:701) CHAR,
PRINT_CERTIFIED_MAIL_BARCODE POSITION(702:702) CHAR,
PRINT_ENDORSEMENT_LINE POSITION(703:703) CHAR,
PRINT_ENCLOSURE_MESSAGE POSITION(704:704) CHAR,
MANIFEST_JOB POSITION(705:705) CHAR,
REPRINTS_ORG_MANIFEST POSITION(706:706) CHAR,
REPRINTS_ORG_TRAY POSITION(707:707) CHAR,
REPRINTS_REASSIGN_SEQUENCES POSITION(708:708) CHAR,
METER_RATE_1 POSITION(709:714) CHAR,
METER_RATE_2 POSITION(715:720) CHAR,
TRAY_SIZE POSITION(721:723) CHAR,
JOB_DISPOSITION POSITION(724:727) CHAR,
SITE_ID POSITION(728:728) CHAR,
FUTURE_USE_1 POSITION(729:900) CHAR,
INITIAL_RUN_DATETIME POSITION(901:914) DATE
"YYYYMMDDhh24miss",
REPRINT_RUN_NUMBER POSITION(915:916) CHAR,
FINAL_RUN_DATETIME POSITION(917:930) DATE
"YYYYMMDDhh24miss",
TOTAL_PIECES POSITION(931:937) INTEGER EXTERNAL,
PIECES_OUTSTANDING POSITION(938:944) INTEGER EXTERNAL,
CURRENT_FILE_STATUS POSITION(945:945) CHAR,
SEFAS_ENTERPRISE_ID POSITION(946:953) CHAR,
KERN_ADF_ID POSITION(954:961) CHAR,
FUTURE_USE_2 POSITION(962:1099) CHAR,
ASTERISK POSITION(1100:1100) CHAR)
into table mailpieces_detail
append
when recid = "1"
(recid FILLER POSITION(1:1) INTEGER EXTERNAL,
JOB_ID CONSTANT 002744,
PIECE_NUMBER POSITION(10:15) INTEGER EXTERNAL,
PRINT_STREAM_NAME POSITION(2:9) CHAR,
TOTAL_PRIMARY_PAGES POSITION(16:17) INTEGER EXTERNAL,
TOTAL_SECONDARY_PAGES POSITION(18:19) INTEGER EXTERNAL,
FEEDER_SELECT_01 POSITION(20:20) CHAR,
FEEDER_SELECT_02 POSITION(21:21) CHAR,
FEEDER_SELECT_03 POSITION(22:22) CHAR,
FEEDER_SELECT_04 POSITION(23:23) CHAR,
FEEDER_SELECT_05 POSITION(24:24) CHAR,
FEEDER_SELECT_06 POSITION(25:25) CHAR,
FEEDER_SELECT_07 POSITION(26:26) CHAR,
FEEDER_SELECT_08 POSITION(27:27) CHAR,
FEEDER_SELECT_09 POSITION(28:28) CHAR,
FEEDER_SELECT_10 POSITION(29:29) CHAR,
FEEDER_SELECT_11 POSITION(30:30) CHAR,
FEEDER_SELECT_12 POSITION(31:31) CHAR,
FEEDER_SELECT_13 POSITION(32:32) CHAR,
FEEDER_SELECT_14 POSITION(33:33) CHAR,
FEEDER_SELECT_15 POSITION(34:34) CHAR,
FEEDER_SELECT_16 POSITION(35:35) CHAR,
FEEDER_SELECT_17 POSITION(36:36) CHAR,
FEEDER_SELECT_18 POSITION(37:37) CHAR,
FEEDER_SELECT_19 POSITION(38:38) CHAR,
FEEDER_SELECT_20 POSITION(39:39) CHAR,
OUTSORT_BIN POSITION(40:40) INTEGER EXTERNAL,
SEAL_FLAG POSITION(41:41) INTEGER EXTERNAL,
METER_DEVICE POSITION(42:42) INTEGER EXTERNAL,
CLEAR_DECK POSITION(43:43) INTEGER EXTERNAL,
REPRINT_NUMBER POSITION(44:55) CHAR ,
PRESORT_LEVEL POSITION(56:57) CHAR ,
FORCE_SINGLE_PIECE_RATE POSITION(58:58) INTEGER EXTERNAL,
DELIVERY_POINT_BAR_CODE POSITION(59:73) CHAR ,
RECIPIENT_ADDRESS_1 POSITION(74:118) CHAR ,
RECIPIENT_ADDRESS_2 POSITION(119:163) CHAR ,
RECIPIENT_ADDRESS_3 POSITION(164:208) CHAR ,
RECIPIENT_ADDRESS_4 POSITION(209:253) CHAR ,
RECIPIENT_ADDRESS_5 POSITION(254:298) CHAR ,
RETURN_ADDRESS_1 POSITION(299:343) CHAR ,
RETURN_ADDRESS_2 POSITION(344:388) CHAR ,
RETURN_ADDRESS_3 POSITION(389:433) CHAR ,
RETURN_ADDRESS_4 POSITION(434:478) CHAR ,
RETURN_ADDRESS_5 POSITION(479:523) CHAR ,
LOGO_CHARACTERS POSITION(524:528) CHAR ,
INDICIA_BITMAP POSITION(529:533) CHAR,
CERTIFIED_MAIL_NUMBER POSITION(534:553) CHAR,
RECIPIENT_ZIP_CODE POSITION(554:562) INTEGER EXTERNAL,
MARKETING_MESSAGE_LINE_1 POSITION(563:602) CHAR,
MARKETING_MESSAGE_LINE_2 POSITION(603:642) CHAR,
ENDORSEMENT POSITION(643:672) CHAR,
ENCLOSURE_LINE POSITION(673:702) CHAR,
TRAY_TAG_DESTINATION POSITION(703:727) CHAR,
TRAY_TAG_BARCODE POSITION(728:737) CHAR,
TRAY_TAG_CONTENT_DESCRIPTION POSITION(738:767) CHAR,
TRAY_NUMBER POSITION(768:770) INTEGER EXTERNAL,
TRAY_TAG_RATE POSITION(771:771) CHAR,
TRAY_TAG_DESTINATION_ZIP POSITION(772:776) CHAR,
ACCOUNT_NUMBER POSITION(777:784) CHAR,
ESTIMATED_WEIGHT POSITION(785:790) CHAR,
FILLER_1 POSITION(791:800) CHAR,
POLICY_NUMBER POSITION(801:809) CHAR,
RECIPIENT_CODE POSITION(810:812) CHAR,
RECIPIENT_SORT_KEY POSITION(813:842) CHAR,
PROCESS_STATE POSITION(843:844) INTEGER EXTERNAL,
TRANSACTION_CODE POSITION(845:849) CHAR,
RECIPIENT_ID_CODE POSITION(850:850) INTEGER EXTERNAL,
CHANNEL POSITION(851:851) INTEGER EXTERNAL,
PRODUCT_CODE POSITION(852:853) CHAR,
RATE_MANUAL POSITION(854:855) CHAR,
CYCLE_DATE POSITION(856:863) CHAR,
ORIGINAL_CYCLE_DATE POSITION(864:871) DATE "YYYYMMDD",
ORIGINAL_PRINT_STREAM POSITION(872:879) CHAR,
ORIGINAL_PIECE_NUMBER POSITION(880:886) INTEGER EXTERNAL,
ESTIMATED_THICKNESS POSITION(887:891) DECIMAL EXTERNAL(5)
FILLER_2 POSITION(897:900) CHAR,
PAGES_FED_FROM_INPUTS POSITION(901:902) CHAR,
INSERT_FEED_01 POSITION(903:903) INTEGER EXTERNAL,
INSERT_FEED_02 POSITION(904:904) INTEGER EXTERNAL,
INSERT_FEED_03 POSITION(905:905) INTEGER EXTERNAL,
INSERT_FEED_04 POSITION(906:906) INTEGER EXTERNAL,
INSERT_FEED_05 POSITION(907:907) INTEGER EXTERNAL,
INSERT_FEED_06 POSITION(908:908) INTEGER EXTERNAL,
INSERT_FEED_07 POSITION(909:909) INTEGER EXTERNAL,
INSERT_FEED_08 POSITION(910:910) INTEGER EXTERNAL,
INSERT_FEED_09 POSITION(911:911) INTEGER EXTERNAL,
INSERT_FEED_10 POSITION(912:912) INTEGER EXTERNAL,
INSERT_FEED_11 POSITION(913:913) INTEGER EXTERNAL,
INSERT_FEED_12 POSITION(914:914) INTEGER EXTERNAL,
INSERT_FEED_13 POSITION(915:915) INTEGER EXTERNAL,
INSERT_FEED_14 POSITION(916:916) INTEGER EXTERNAL,
INSERT_FEED_15 POSITION(917:917) INTEGER EXTERNAL,
INSERT_FEED_16 POSITION(918:918) INTEGER EXTERNAL,
INSERT_FEED_17 POSITION(919:919) INTEGER EXTERNAL,
INSERT_FEED_18 POSITION(920:920) INTEGER EXTERNAL,
INSERT_FEED_19 POSITION(921:921) INTEGER EXTERNAL,
INSERT_FEED_20 POSITION(922:922) INTEGER EXTERNAL,
DISPOSITION_CODE POSITION(923:924) INTEGER EXTERNAL,
PROCESS_DATETIME POSITION(925:932) DATE
"YYYYMMDDhh24miss",
OPERATOR_ID POSITION(937:956) CHAR,
MACHINE POSITION(957:976) CHAR,
SEGMENT_NUMBER POSITION(977:980) CHAR,
KEY_LINE_SEQUENCE POSITION(981:987) INTEGER EXTERNAL,
KEY_LINE_POSTAGE POSITION(988:992) CHAR,
MANIFEST_NAME POSITION(993:1008) CHAR,
MANIFEST_REPORTER_ID POSITION(1009:1018) CHAR,
EXIT_LOCATION POSITION(1019:1019) CHAR,
REPRINT_RUN_NUMBER POSITION(1020:1021) INTEGER EXTERNAL,
ACTUAL_POSTAGE POSITION(1022:1027) CHAR,
ASTERISK POSITION(1100:1102) CHAR )
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Dunn INET: john.dunn_at_sefas.co.uk Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Fri Aug 15 2003 - 06:09:28 CDT
 |
 |