Re: Dimension table load - PLSQL question

  • From: Edgar Chupit <chupit@xxxxxxxxx>
  • To: Ranko Mosic <ranko.mosic@xxxxxxxxx>
  • Date: Thu, 29 Sep 2005 23:58:06 +0300

First of all I would like to suggest that you trade code simpleness to
efficiency. If you will remove all this small lookup select statements and
construct one "big" statement instead (something like I was showing in my
example), this function will perform better, because there will be less
context switches between SQL Engine and PLSQL engine.

Than I would suggest you to review your "Dimension table load" algorithm,
because from what I see here, I can deduce (I hope I'm wrong on this one)
that you are populating this table row by row and somewhere in the code you
have procedure that does:

for r in ( select x from y ) loop
z := f_d_address_category_desc( r.x );
update t set t.z = z;
end loop;

If you can construct single SQL statement that will perform this task, it
will perform much much better and will save you a lot of time. Tom Kyte has
written dozen of articles on this, for example: or
just search for the ETL on <>

And the third point that I would like to mention, is that you can make your
code look beautifier if you will use %rowtype variable or record type to
store variables/pass parameters, but basically the code will do the same

Unfortunately there is no good way to reduce this code, because SQL is
designed this way. SQL has to know tablename in advance, you can trick SQL
engine using PLSQL and Native SQL, but this will not perform as good as
hardcoded tablename values.

So I would suggest you to try different approaches and measure the
performance/code quality benefits of each approach and than make proved

On 9/29/05, Ranko Mosic <ranko.mosic@xxxxxxxxx> wrote:
> Because this is how it looks now ( cursor can be removed because it will
> return only one row );
> and we could do you when no_data_found thing. But this logic has to be
> repeated for dozens of tables.
> IS

Best regards,
Edgar Chupit

Other related posts: