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: http://tinyurl.com/9hznb or just search for the ETL on asktom.oracle.com <http://asktom.oracle.com>
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 thing.
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 decision.
On 9/29/05, Ranko Mosic <ranko.mosic@(protected)> 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. > > > CREATE OR REPLACE PACKAGE BODY DW_OLAP_UTIL > IS
-- Best regards, Edgar Chupit callto://edgar.chupit
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. <br><br>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:<br> <br> for r in ( select x from y ) loop<br> z := f_d_address_category_desc( r.x );<br> update t set t.z = z;<br> end loop;<br><br> 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: <a href="http:/ /tinyurl.com/9hznb">http://tinyurl.com/9hznb</a> or just search for the ETL on <a href="http://asktom.oracle.com">asktom.oracle.com</a><br> <br> 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 thing.<br> <br> 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. <br> <br> So I would suggest you to try different approaches and measure the performance/code quality benefits of each approach and than make proved decision.<br> <br>On 9/29/05, Ranko Mosic <<a href="mailto:ranko.mosic@(protected)">ranko .mosic@(protected)</a>> wrote:<br>> Because this is how it looks now ( cursor can be removed because it will<br>> return only one row );   ; <br>> and we could do you when no_data_found thing. But this logic has to be <br>> repeated for dozens of tables. <br>> <br>> <br>> CREATE OR REPLACE PACKAGE BODY DW_OLAP_UTIL<br>> IS<br><br> -- <br>Best regards,<br> Edgar Chupit<br> <a href="callto ://edgar.chupit">callto://edgar.chupit</a><br><br><br>
