OraFAQ Forum: Server Utilities » SQL*Loader suggestion for huge records

Home » RDBMS Server » Server Utilities » SQL*Loader suggestion for huge records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL*Loader suggestion for huge records [message #140274]

Mon, 03 October 2005 06:29

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

I have a flat file which has around 1 million records. There are no delimiter in the records and whole line has to be
considered as 1 column. There are around 8,00,000 records starting with "Warning" and rest starting with "Start" or "End".
I am loading all the records in 1 temporary table and I need only count of records starting with "Warning" and for other
I have to do some calculation.

what other option can I use?

1. Should I load the data in seperate tables for seperate parameters (1 for Warning ones and other for "Start/End")? Will
it have any performance problem while loading?
2. Or should I load in single table and then load in main tables after doing the calculation? This will result in
degrade of performance while doing calculation?

Any suggestions?

File looks like this:-

START: read routing_data (NULL) (03:01:00)
END: read routing_data (NULL) (03:01:00): 0.000 sec
Warning: deleting mfg order 515763300_1-001 which is no longer pegged.
Warning: deleting mfg order 515885400_1-001 which is no longer pegged.
Warning: deleting mfg order 518833100_1-001 which is no longer pegged.
Warning: deleting mfg order 519245700_1-001 which is no longer pegged.

Regards
Himanshu

Report message to a moderator

Re: SQL*Loader suggestion for huge records [message #140277 is a reply to message #140274]

Mon, 03 October 2005 06:53

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

What is your intention?
Could you please explain?
Just getting the count of certain records is much simpler.
Like this.

oracle@mutation#cat file.data
START: read routing_data (NULL) (03:01:00)
END: read routing_data (NULL) (03:01:00): 0.000 sec
Warning: deleting mfg order 515763300_1-001 which is no longer pegged.
Warning: deleting mfg order 515885400_1-001 which is no longer pegged.
Warning: deleting mfg order 518833100_1-001 which is no longer pegged.
Warning: deleting mfg order 519245700_1-001 which is no longer pegged
oracle@mutation#cat file.data | grep 'Warning:'|wc -l
       4

If you want to load all the data into a table, just do it with direct option. Much Faster. Then use sql methods to do your business.
Or consider external files ( for simple manipulations. Not recomended for Huge files).
With external files, you are considering the flat file as a table.

Report message to a moderator

Re: SQL*Loader suggestion for huge records [message #140279 is a reply to message #140277]

Mon, 03 October 2005 07:01

himang
Messages: 282
Registered: March 2005
Location: Bangalore

Senior Member

Thanks for the reply. Yes we can directly get the count of warning files but for future enhancements we need to keep the records in table. I am using the direct option. Can't considered the EXTERNAL table feature as we need to some calculations which is not possible and file is also huge.

Only thing I want to know is that is it advisable to load data into 2 seperate tables as the queries/manipulation can be faster but only thing which bothers me is while using "WHEN" clause in SQL*Loader will it result in delay in loading or not?

Thanks in advance.

Regards
Himanshu

Report message to a moderator

Re: SQL*Loader suggestion for huge records [message #140280 is a reply to message #140279]

Mon, 03 October 2005 07:07

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

with sqlldr I prefer to keep things simple.
Load data into oracle table.
use sql means to do your business.
Did you also try parallel load?
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96652/ch09.htm#1008227

Report message to a moderator

Previous Topic:	Oracle version (merged 3 threads)
Next Topic:	constraint and index issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 12:20:52 CDT 2024