Re: Optimising PL/SQL Script. Takes 2 days to run.

From: Pablo Sanchez <pablo_at_dev.null>
Date: Tue, 21 Jan 2003 10:14:50 -0600
Message-ID: <Xns930A5E144F25Bpingottpingottbah@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.com

Received on Tue Jan 21 2003 - 10:14:50 CST