Home » RDBMS Server » Server Utilities » SQL Loader or external table with a trigger
SQL Loader or external table with a trigger [message #593185] Wed, 14 August 2013 15:47 Go to next message
MitchM
Messages: 9
Registered: August 2013
Junior Member
I have to have a sequence added to a large(288 million rows) file when I load the file into the table. If I use SQL Loader I can't use direct since I have a trigger for each row for the sequence but I am not sure if an external table will be any faster since the trigger will be firing for each row also. In this scenario is one better than the other ?

[Updated on: Wed, 14 August 2013 15:48]

Report message to a moderator

Re: SQL Loader or external table with a trigger [message #593190 is a reply to message #593185] Wed, 14 August 2013 18:08 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

I don't clearly understand what you have or what you need to accomplish.

What is Operating System name & version?
What is Oracle version to 4 decimal places?

>In this scenario is one better than the other ?
yes, but only by actual testing can you determine the answer.
Re: SQL Loader or external table with a trigger [message #593193 is a reply to message #593185] Wed, 14 August 2013 19:07 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
You can definitely use external tables. Since you haven't mentioned version details, assuming your DB version is 9i or up.

Have a look at this ETL process
Re: SQL Loader or external table with a trigger [message #593272 is a reply to message #593185] Thu, 15 August 2013 07:44 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
What if you use ROWS=1 parameter in SQL*Loader?
Re: SQL Loader or external table with a trigger [message #593288 is a reply to message #593272] Thu, 15 August 2013 09:29 Go to previous messageGo to next message
MitchM
Messages: 9
Registered: August 2013
Junior Member
Joy- please explain how that would help

Thanks

To give more details , I have a pipedelimited file of 288 million tows to load. I would use SQL Loader with Direct but I have to add a sequence number to each record which means I would have to use the conventional load. Does snyone haave a guestimate of what would work faster(and yes blackswan I am aware of testing I was asking for advice)

1. DIrect path to a staging table and then writing a procedure to load in the target table adding the sequence number
2.Use an external table and then load into target table adding the sequence number.
3. Smething else

Thanks,
Mitch
Re: SQL Loader or external table with a trigger [message #593290 is a reply to message #593288] Thu, 15 August 2013 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 23162
Registered: January 2009
Senior Member
I was asking for details about your environment which have avoided providing twice now.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: SQL Loader or external table with a trigger [message #593339 is a reply to message #593288] Thu, 15 August 2013 12:59 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
MitchM wrote on Thu, 15 August 2013 10:29
Joy- please explain how that would help


Use the trigger and ROWS=1. There is no buffer and the rows load one at a time (slowly) and the trigger executes for each one.
Unless I am just not understanding your problem, which can surely be the case.
Re: SQL Loader or external table with a trigger [message #593344 is a reply to message #593288] Thu, 15 August 2013 13:08 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2544
Registered: May 2013
Location: World Wide on the Web
Senior Member
MitchM wrote on Thu, 15 August 2013 19:59

1. DIrect path to a staging table and then writing a procedure to load in the target table adding the sequence number
2.Use an external table and then load into target table adding the sequence number.


Why do you want to use a staging table, when you can skip it using pipelined function in any ETL process. Have a look at thisETL process
Re: SQL Loader or external table with a trigger [message #593353 is a reply to message #593344] Thu, 15 August 2013 14:31 Go to previous message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
If you use a SQL*Loader sequence, not a database sequence, then you can use the SQL*Loader direct path, and that will probably be your fastest method. Please see the simplified demonstration below.

SCOTT@orcl12c_11gR2> host type test.dat
ACCOUNTING|NEW YORK
RESEARCH|DALLAS
SALES|CHICAGO
OPERATIONS|BOSTON

SCOTT@orcl12c_11gR2> host type test.ctl
load data
into table dept2
fields terminated by '|'
trailing nullcols
(dname, loc, deptno sequence)

SCOTT@orcl12c_11gR2> create table dept2 as select * from dept where 1 = 2
  2  /

Table created.

SCOTT@orcl12c_11gR2> select * from dept2
  2  /

no rows selected

SCOTT@orcl12c_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log direct=true

SQL*Loader: Release 12.1.0.1.0 - Production on Thu Aug 15 12:30:28 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 4.

Table DEPT2:
  4 Rows successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl12c_11gR2> select * from dept2
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
         1 ACCOUNTING     NEW YORK
         2 RESEARCH       DALLAS
         3 SALES          CHICAGO
         4 OPERATIONS     BOSTON

4 rows selected.

Previous Topic: SELECT on external is very slow
Next Topic: import only certain number of rows in oracle
Goto Forum:
  


Current Time: Mon Dec 22 19:40:41 CST 2014

Total time taken to generate the page: 0.29716 seconds