Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: speeding up conventional path sqlldr

RE: speeding up conventional path sqlldr

From: Hallas, John, Tech Dev <John.Hallas_at_gb.vodafone.co.uk>
Date: Fri, 15 Aug 2003 04:41:02 -0800
Message-ID: <F001.005CAE8D.20030815044102@fatcity.com>


Try external tables or checkout a good article written by Stephen Andert from this list which was a review for Jonathan Gennick's sqlloader book

The link is http://www.oreillynet.com/pub/a/oreilly/oracle/news/oraclesqlload_0401.html

Key factors are bindsize and rows settings

John

-----Original Message-----
Sent: 15 August 2003 12:39
To: Multiple recipients of list ORACLE-L

ive found through repeated use that its faster to direct path load the data to a staging table then do an insert /*+ append */ to move the data over to the master table.

speed of inserts will depend on how many indexes you have.

however, since you are on 9i you dont need to use either. use an external table and a merge. go to asktom. he has an example.

>
> From: John Dunn <john.dunn_at_sefas.co.uk>
> Date: 2003/08/15 Fri AM 07:09:28 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: 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)
> ":ESTI
> MATED_THICKNESS/10000",
> ESTIMATED_POSTAGE POSITION(892:896) DECIMAL EXTERNAL(5)
> ":EST
> IMATED_POSTAGE/10000",
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hallas, John, Tech Dev
  INET: John.Hallas_at_gb.vodafone.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 - 07:41:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US