Re: Optimising PL/SQL Script. Takes 2 days to run.
Date: Tue, 21 Jan 2003 10:14:50 -0600
Message-ID: <Xns930A5E144F25Bpingottpingottbah_at_216.166.71.233>
david_at_fitzg.com (David FitzGerald) wrote in news:7f966bc5.0301210517.5d3b54ae_at_posting.google.com:
> The problem is that this is taking an awfully long time to process
> (more than 2 days). I am new to PL/SQL so don't know if I am
> approaching this the wrong way, or if this is the quickest I'll ever
> create over half a billion rows? Any pointers would be massively
> appreciated.
You might consider re-writing the query so it's a more set-based approach rather than taking the procedural approach.
What you need to do is create a cartesian product between your table and another table so that you get 7x the number of rows. This way you avoid the looping construct and gives you the set-based approach.
You might need to use a large rollback segment for this transaction though. :)
Let's walk you through an example and you can carry it over to your PL/SQL code -- after all, you don't want me to write your PL/SQL right? <g>
Simple query
The following shows that my 'user_tab_columns' table has 19 rows:
SQL> select count(*) from user_tab_columns;
COUNT(*)
19
The multiplier table
Create a simple table and add the number of rows you wish to multiple by:
SQL> create table multiplier (id char(1));
SQL> insert into multiplier values ('1');
...
SQL> select count(*) from multiplier;
COUNT(*)
7
Be a rabbit
7 * 19 is 133. Therefore, the following query should return a count of 133:
SQL> select count(*) from multiplier, user_tab_columns;
COUNT(*)
133
and it does! :)
This is a cartesian product that most folks want to avoid but in your case, take advantage of it.
In your situation, you want to do something like this:
INSERT INTO ...
SELELCT ...
No cursors, just one INSERT/SELECT combo. Again, you might need a
large rollback segment (ask your DBA) in order to get this to work.
Let us know if it works!
Thx!
-- Pablo Sanchez, High-Performance Database Engineering http://www.hpdbe.comReceived on Tue Jan 21 2003 - 17:14:50 CET