Home » SQL & PL/SQL » SQL & PL/SQL » Please help guys.
Please help guys. [message #348583] Wed, 17 September 2008 05:40 Go to next message
gmoth
Messages: 17
Registered: September 2008
Location: South Africa
Junior Member
I need help Smile

I am writing a very complex package for my company and need some assistance. Ok I am selecting a large amount of data currently into a cursor. Once I have selected it I need to loop through each element and format data within each loop and write to a flat file. So question 1 is can I use the BULK collect here to improve performance? The reason I am not sure is because I still need to format each record so would bulk collect work here? I was thinking would bulk collecting into a collection then looping through each element of the collection work? Does it mean less switches between the PL/SQL engine and SQL engine? Surely it does because I select all data into the collection then manipulate the collection my side? Is that quicker than using a standard cursor WITHOUT bulk collect and then looping through each record that way? Please suggest the fastest way as performance is a major issue here.

Lastly if you do recommend the bulk collect into a collection what collection do i use as I am selecting data from different tables with different data types and will not know the size of the collection until it is populated.

I guess a nested table?
Re: Please help guys. [message #348590 is a reply to message #348583] Wed, 17 September 2008 06:13 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
You can bulk collect in cursor, create a global temporary table
and bulk insert few records at a time (eg 10000 at a time ) from cursor to that table.now use your GTT to process furthur and commit records. in this way you will end up processing smaller number of records at one time which will be faster .


Re: Please help guys. [message #348600 is a reply to message #348590] Wed, 17 September 2008 06:48 Go to previous messageGo to next message
gmoth
Messages: 17
Registered: September 2008
Location: South Africa
Junior Member
Thanks for the help.

The other thing is a have a package where i have a whole bunch of functions created. Once i have the data I send it to a package and then write to UTL_FILE. So in this case ill collect into a table can I then send a table to a function in Oracle?

Lastly what type of collection is a global table and is it the best to use in this situation?
Re: Please help guys. [message #348604 is a reply to message #348600] Wed, 17 September 2008 07:07 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
I am not sure how much data your cursor is fetching .If the amount of data is huge then it may benifit.
GTT is not a collection. it is just like a table but contains data temporarily within that session only
(google for on commit delete rows ,on commit preserve rows options of GTT).
I had faced similay situation when my cursor was fetching 50 milion records.then i had to process those records and insert into another table.I used GTT in that case ,populating 1 Million records at a time and then merging with another table.I had craeted index on GTT in that case so access was faster.More over I was commiting after 1M records.the entire process was around 9 times faster than the single loop cursor.



Re: Please help guys. [message #348608 is a reply to message #348604] Wed, 17 September 2008 07:22 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
What i think getting data in cursor and manipulating it
with collection and then generating a file by UTL_FILE
is not gonna be very fast operation if the records you have to
process is large.

If you need to generate a file use SQLPLUS and a script to
spool data to a file.

Even this simplest method takes time if data is large.


Regards,
Rajat
Re: Please help guys. [message #348636 is a reply to message #348604] Wed, 17 September 2008 08:10 Go to previous messageGo to next message
gmoth
Messages: 17
Registered: September 2008
Location: South Africa
Junior Member
Ok so its not a collection just another storage space that is there for the session only. just like a structure in other languages? surely using the Bulk Collect and then writing to the table will create less context switches and will improve performance?

I reckon ill go that route as just using a cursor and then looping through each record must cause a lot of switches?

Ok so that table can be passes through to another function then I guess?

Thanks guys...
Re: Please help guys. [message #348650 is a reply to message #348636] Wed, 17 September 2008 08:43 Go to previous messageGo to next message
gmoth
Messages: 17
Registered: September 2008
Location: South Africa
Junior Member
Ok I have done some reading up about them its seems they are different to structures? I am still not sure if you can pass a temp table to another procedure or would you just query that table from within the other procedure? My other worry is after doing the bulk collect into the GTT I then will need to format each row before writing to a file can this be done?
Re: Please help guys. [message #348655 is a reply to message #348650] Wed, 17 September 2008 08:50 Go to previous message
gmoth
Messages: 17
Registered: September 2008
Location: South Africa
Junior Member
You see there are User Defined Types, Arrays and GTT's so how do you know whats best to use?
Previous Topic: displaying table definition
Next Topic: Help with splitting partitioned table
Goto Forum:
  


Current Time: Tue Dec 06 04:09:48 CST 2016

Total time taken to generate the page: 0.07037 seconds