Re: speeding up conventional path sqlldr
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 [EMAIL PROTECTED] Date: 2003/08/15 Fri AM 07:09:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 so10,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_01POSITION(30:41) INTEGER EXTERNAL, INSERT_NAME_02 POSITION(42:61) CHAR, INSERT_SCAN_CODE_02POSITION(62:73) INTEGER EXTERNAL, INSERT_NAME_03 POSITION(74:93) CHAR, INSERT_SCAN_CODE_03POSITION(94:105) INTEGER EXTERNAL, INSERT_NAME_04 POSITION(106:125) CHAR, INSERT_SCAN_CODE_04POSITION(126:137) INTEGER EXTERNAL, INSERT_NAME_05 POSITION(138:157) CHAR, INSERT_SCAN_CODE_05POSITION(158:169) INTEGER EXTERNAL, INSERT_NAME_06 POSITION(170:189) CHAR, INSERT_SCAN_CODE_06POSITION(190:201) INTEGER EXTERNAL, INSERT_NAME_07 POSITION(202:221) CHAR, INSERT_SCAN_CODE_07POSITION(222:233) INTEGER EXTERNAL, INSERT_NAME_08 POSITION(234:253) CHAR, INSERT_SCAN_CODE_08POSITION(254:265) INTEGER EXTERNAL, INSERT_NAME_09 POSITION(266:285) CHAR, INSERT_SCAN_CODE_09POSITION(286:297) INTEGER EXTERNAL, INSERT_NAME_10 POSITION(298:317) CHAR, INSERT_SCAN_CODE_10POSITION(318:329) INTEGER EXTERNAL, INSERT_NAME_11 POSITION(330:349) CHAR, INSERT_SCAN_CODE_11POSITION(350:361) INTEGER EXTERNAL, INSERT_NAME_12 POSITION(362:381) CHAR, INSERT_SCAN_CODE_12POSITION(382:393) INTEGER EXTERNAL, INSERT_NAME_13 POSITION(394:413) CHAR, INSERT_SCAN_CODE_13POSITION(414:425) INTEGER EXTERNAL, INSERT_NAME_14 POSITION(426:445) CHAR, INSERT_SCAN_CODE_14POSITION(446:457) INTEGER EXTERNAL, INSERT_NAME_15 POSITION(458:477) CHAR, INSERT_SCAN_CODE_15POSITION(478:489) INTEGER EXTERNAL, INSERT_NAME_16 POSITION(490:509) CHAR, INSERT_SCAN_CODE_16POSITION(510:521) INTEGER EXTERNAL, INSERT_NAME_17 POSITION(522:541) CHAR, INSERT_SCAN_CODE_17POSITION(542:553) INTEGER EXTERNAL, INSERT_NAME_18 POSITION(554:573) CHAR, INSERT_SCAN_CODE_18POSITION(574:585) INTEGER EXTERNAL, INSERT_NAME_19 POSITION(586:605) CHAR, INSERT_SCAN_CODE_19POSITION(606:617) INTEGER EXTERNAL, INSERT_NAME_20 POSITION(618:637) CHAR, INSERT_SCAN_CODE_20POSITION(638:649) INTEGER EXTERNAL, INSERT_NAME_21 POSITION(650:669) CHAR, INSERT_SCAN_CODE_21POSITION(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_ADDRESSPOSITION(699:699) CHAR, PRINT_RETURN_ADDRESS POSITION(700:700) CHAR, PRINT_MARKETING_MESSAGEPOSITION(701:701) CHAR, PRINT_CERTIFIED_MAIL_BARCODE POSITION(702:702) CHAR, PRINT_ENDORSEMENT_LINE POSITION(703:703) CHAR, PRINT_ENCLOSURE_MESSAGEPOSITION(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
RE: speeding up conventional path sqlldr
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 [EMAIL PROTECTED] Date: 2003/08/15 Fri AM 07:09:28 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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 so10,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_01POSITION(30:41) INTEGER EXTERNAL, INSERT_NAME_02 POSITION(42:61) CHAR, INSERT_SCAN_CODE_02POSITION(62:73) INTEGER EXTERNAL, INSERT_NAME_03 POSITION(74:93) CHAR, INSERT_SCAN_CODE_03POSITION(94:105) INTEGER EXTERNAL, INSERT_NAME_04 POSITION(106:125) CHAR, INSERT_SCAN_CODE_04POSITION(126:137) INTEGER EXTERNAL, INSERT_NAME_05 POSITION(138:157) CHAR, INSERT_SCAN_CODE_05POSITION(158:169) INTEGER EXTERNAL, INSERT_NAME_06 POSITION(170:189) CHAR, INSERT_SCAN_CODE_06POSITION(190:201) INTEGER EXTERNAL, INSERT_NAME_07 POSITION(202:221) CHAR, INSERT_SCAN_CODE_07POSITION(222:233) INTEGER EXTERNAL, INSERT_NAME_08 POSITION(234:253) CHAR, INSERT_SCAN_CODE_08POSITION(254:265) INTEGER EXTERNAL, INSERT_NAME_09 POSITION(266:285) CHAR, INSERT_SCAN_CODE_09POSITION(286:297) INTEGER EXTERNAL, INSERT_NAME_10 POSITION(298:317) CHAR, INSERT_SCAN_CODE_10POSITION(318:329) INTEGER EXTERNAL, INSERT_NAME_11 POSITION(330:349) CHAR, INSERT_SCAN_CODE_11POSITION(350:361) INTEGER EXTERNAL, INSERT_NAME_12 POSITION(362:381) CHAR, INSERT_SCAN_CODE_12POSITION(382:393) INTEGER EXTERNAL, INSERT_NAME_13 POSITION(394:413) CHAR, INSERT_SCAN_CODE_13POSITION(414:425) INTEGER EXTERNAL, INSERT_NAME_14 POSITION(426:445) CHAR, INSERT_SCAN_CODE_14POSITION(446:457) INTEGER EXTERNAL, INSERT_NAME_15 POSITION(458:477) CHAR, INSERT_SCAN_CODE_15POSITION(478:489) INTEGER EXTERNAL, INSERT_NAME_16 POSITION(490:509) CHAR, INSERT_SCAN_CODE_16POSITION(510:521) INTEGER EXTERNAL, INSERT_NAME_17 POSITION(522:541) CHAR, INSERT_SCAN_CODE_17POSITION(542:553) INTEGER EXTERNAL, INSERT_NAME_18 POSITION(554:573) CHAR, INSERT_SCAN_CODE_18POSITION(574:585) INTEGER EXTERNAL, INSERT_NAME_19 POSITION(586:605) CHAR, INSERT_SCAN_CODE_19POSITION(606:617) INTEGER EXTERNAL, INSERT_NAME_20 POSITION(618:637) CHAR, INSERT_SCAN_CODE_20POSITION(638:649) INTEGER EXTERNAL, INSERT_NAME_21 POSITION(650:669) CHAR, INSERT_SCAN_CODE_21POSITION(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_ADDRESSPOSITION(699:699) CHAR, PRINT_RETURN_ADDRESS POSITION(700:700) CHAR, PRINT_MARKETING_MESSAGEPOSITION(701:701) CHAR,
