StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Firstly you need to find where the performance problem actually is. Then you can look at trying to solve it.</p> <ol> <li><p>What is the performance of the view like? How long does it take the view to execute without any of the function calls? Try running the command</p> <p>How well does it perform? Does it take 1 minute or 1 hour?</p> <pre> create table the_view_table as select * from the_view; </pre></li> <li><p>How well do the functions perform? According to the description you are making approximately 5 million function calls. They had better be pretty efficient! Also are the functions defined as <code>deterministic</code>. If the functions are defined using the <code>deterministic</code> keyword, the Oracle has a chance of optimizing away some of the calls.</p></li> <li><p>Is there a way of reducing the number of function calls? The function are being called once the view has been evaluated and the million rows of data are available. BUT are all the input values from the highest level of the query? Can the function calls be imbeded into the view at a lower level. Consider the following two queries. Which would be quicker?</p> <pre>select f.dim_id, d.dim_col_1, long_slow_function(d.dim_col_2) as dim_col_2 from large_fact_table f join small_dim_table d on (f.dim_id = d.dim_id)</pre> <pre>select f.dim_id, d.dim_col_1, d.dim_col_2 from large_fact_table f join ( select dim_id, dim_col_1, long_slow_function(d.dim_col_2) as dim_col_2 from small_dim_table) d on (f.dim_id = d.dim_id)</pre> <p>Ideally the second query should run quicker as it calling the function fewer times. </p></li> </ol> <p>The performance issue could be in any of these places and until you investigate the issue, it would be difficult to know where to direct your tuning efforts.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload