Oracle SQL - computed column with function

Question

Is it possible and is it good solution create computed column where use function to calculate value?

eg.

table_1

id   type  date_from    date_to       TOTAL_CURRENT_YEAR (computed column)
-------------------------------------------------------------------
1    A     01.01.2022   01.05.2022    if type = A call function to calculate TOTAL_CURRENT_YEAR  => Select f_calculate_A(date_from,nvl(check_out,sysdate)) from dual)
2    B     01.01.2022   01.05.2022    if type = B call function to calculate TOTAL_CURRENT_YEAR  => Select f_calculate_B(date_from,nvl(check_out,sysdate)) from dual)

How define TOTAL_CURRENT_YEAR (computed column)?

Alter table table_1 add  TOTAL_CURRENT_YEAR.........

Assuming your function is needing to access details from other rows/tables your function is not deterministic. You will not be able to store the computed value (in an index etc), so you may as well just have a view. It’s possible you need the materializarion for some performance issue - materialized views are the way to do this (using the actual SQL the function uses directly) — Andrew Sayer, Jun 01 '22 at 06:50
it depends on your need, but make sure that the column within the WHERE clause of the correlated subquery should be indexed... As if `date_from` and `nvl(check_out,date_to)` should be indexed for the current case. — Barbaros Özhan, Jun 01 '22 at 06:52
anyway, you can create indexes for them(if they don't exist), even functional ones ... — Barbaros Özhan, Jun 01 '22 at 07:19

MT0 · Accepted Answer · 2022-06-01T08:47:29.177

is it good solution create computed column where use function to calculate value?

If the function is deterministic (i.e. if you give it the same input values then it will ALWAYS return the same output) then you can use a virtual column.

Since SYSDATE is not a deterministic function, you cannot use it in a virtual column and if you try you will get the exception:

ORA-54002: only pure functions can be specified in a virtual column expression

If you were to move NVL(check_out, SYSDATE) from the column definition to inside your function then the function would not be deterministic (because it relies on SYSDATE and the return value now and the return value in 1 second time would be different).

Therefore, it is not a good solution to use a virtual column.

Note: there are hacks you can use such as wrapping a call to SYSDATE in a deterministic function to trick the compiler into accepting it; but that is a hack and may cause unexpected behaviour if the SQL engine caches the output from the function, which it expects to be deterministic and so could reasonably expect to be able to keep using a cached value, and then does reuse the cached value rather than recalculating from SYSDATE and then your clock will stick for a while and then jump forwards as the cache is updated.

Instead, you should create a view and compute the values in the view.

Oracle SQL - computed column with function

1 Answers1