Home » SQL & PL/SQL » SQL & PL/SQL » Performance of Virtual column referencing another table!
Performance of Virtual column referencing another table! [message #675214] Fri, 15 March 2019 04:16 Go to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
I have created table1 & table2.

In the table2, i need to create effective_date as virtual column based on the frequency in table1.
If the frequency is monthly then the effective_date should be start_period + 1 month
If the frequency is Quarterly then the effective_date should be start_period + 1 quarter

create table table1(id number, frequency varchar2(2))

insert into table1 values(1,'M');
insert into table1 values(2,'Q');


create table table2(id number, start_period varchar2(2), version_no number, value number)

insert into table2 values(1,'01-Nov-2009',1,200)
insert into table2 values(1,'01-Dec-2009',1,210)
insert into table2 values(1,'01-Dec-2009',2,300)
insert into table2 values(1,'01-Jan-2010',1,100)
insert into table2 values(2,'01-sep-2009',1,150)
insert into table2 values(2,'01-oct-2009',1,200)
insert into table2 values(2,'01-oct-2009',2,250)
insert into table2 values(2,'01-oct-2009',3,270)


I have tried to implement this by creating standalone function and referred that function in the new virtual column.

create or replace 
function eff_dt_col(id_in number,start_period_in date,version_in number) return date deterministic as
l_freq varchar2(2);
l_eff_dt date;
begin 
select frequency into l_freq from table1 where id=id_in;

select (case when l_freq='M' then add_months(start_period,1) when l_freq='Q' then add_months(start_period,3) else null end) into l_eff_dt 
from table2 where id=id_in and start_period=start_period_in and version_no=version_in ;
  
return l_eff_dt;

end;

Added Virtual column
alter table table2 add effective_date as (eff_dt_col(id,start_period,version_no))

could any one please tell me will there be any performance issue if i do this way.

Otherwise, can i update the effective_date column?

Please suggest, which one is the better approach.

Re: Performance of Virtual column referencing another table! [message #675215 is a reply to message #675214] Fri, 15 March 2019 04:26 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Please correct your code, simple things like start_period being varchar2(2) You have not even bothered to put semi colons at the end of each statement.
Re: Performance of Virtual column referencing another table! [message #675216 is a reply to message #675215] Fri, 15 March 2019 04:34 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Please find the updated script for table creation & insertion

create table table1(id number, frequency varchar2(2));

insert into table1 values(1,'M');
insert into table1 values(2,'Q');


create table table2(id number, start_period date, version_no number, value number);

insert into table2 values(1,'01-Nov-2009',1,200);
insert into table2 values(1,'01-Dec-2009',1,210);
insert into table2 values(1,'01-Dec-2009',2,300);
insert into table2 values(1,'01-Jan-2010',1,100);
insert into table2 values(2,'01-sep-2009',1,150);
insert into table2 values(2,'01-oct-2009',1,200);
insert into table2 values(2,'01-oct-2009',2,250);
insert into table2 values(2,'01-oct-2009',3,270);

Re: Performance of Virtual column referencing another table! [message #675217 is a reply to message #675216] Fri, 15 March 2019 04:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Still no good: you have not used to_date. You will never be successful in your work if you do not pay attention to detail. Have you actually run all that code?

orclx>
orclx> select * from table2;

        ID START_PERIOD        VERSION_NO      VALUE
---------- ------------------- ---------- ----------
         1 0001-11-20:09:00:00          1        200
         1 0001-12-20:09:00:00          1        210
         1 0001-12-20:09:00:00          2        300
         1 0001-01-20:10:00:00          1        100
         2 0001-09-20:09:00:00          1        150
         2 0001-10-20:09:00:00          1        200
         2 0001-10-20:09:00:00          2        250
         2 0001-10-20:09:00:00          3        270

8 rows selected.

orclx>

[Updated on: Fri, 15 March 2019 04:38]

Report message to a moderator

Re: Performance of Virtual column referencing another table! [message #675218 is a reply to message #675217] Fri, 15 March 2019 04:40 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi,
I have created the table & inserted. it is working fine for me.
Re: Performance of Virtual column referencing another table! [message #675219 is a reply to message #675217] Fri, 15 March 2019 04:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just stop doing this.
Your function isn't deterministic.
Lying to oracle about whether or not a function is deterministic is a stupid idea that can lead to all sorts of interesting bugs.

Create a view instead.
Re: Performance of Virtual column referencing another table! [message #675220 is a reply to message #675218] Fri, 15 March 2019 04:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
ramya29p wrote on Fri, 15 March 2019 09:40
Hi,
I have created the table & inserted. it is working fine for me.
Fine. It ran for me. Do you think it ran correctly?

Why don't you just write a view that joins the tables?
Re: Performance of Virtual column referencing another table! [message #675221 is a reply to message #675220] Fri, 15 March 2019 04:52 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi Watson,
do you mean to create a view and use in the virtual column instead of writing a function?
Re: Performance of Virtual column referencing another table! [message #675222 is a reply to message #675221] Fri, 15 March 2019 04:55 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I mean give up this crazy approach and do it the easy way: join the tables in a view.

Write code that is reliable, not stuff that works by luck. When what you have written goes through a code review, it will be thrown out. The DBA in charge of the review may throw you out too.
Re: Performance of Virtual column referencing another table! [message #675225 is a reply to message #675222] Fri, 15 March 2019 05:04 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
When the approach is not clear that is why people are posting in the forum. So should be given a proper response instead of giving other comments.

My requirement is , i need to have Virtual column in a table. which would be used for other reporting purpose.

[Updated on: Fri, 15 March 2019 05:11]

Report message to a moderator

Re: Performance of Virtual column referencing another table! [message #675229 is a reply to message #675225] Fri, 15 March 2019 05:30 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
No it's not.
No reporting purpose requires a virtual column. None, ever.

What we've got here is an XY Problem

You, or whoever gave you this task, decided that a virtual column is the best way to cater to some functional requirement.

But in this case it isn't and can't be.

Because, as I noted above, the only way you can get this to appear to work is by lying to oracle about the nature of the function.

If you lie to oracle about something then oracle will go off and do things based on the assumption that you are actually telling it the truth. And sooner or later that discrepancy will bite you on the behind. It'll probably be in a way you don't expect. It may well be in a way that you don't immediately link to the fact that you lied to oracle (also because you will probably have forgotten that you did that). It may corrupt your data, it may give wrong results. If you're lucky it'll cause whatever process to error out.
And you may ask us, or some other forum, or even oracle support for help, and when we finally discover the culprit we'll all tell you to stop doing that.


Or you could do the correct thing of not lying to oracle in order to try and use a construct for a purpose for which it was not designed to work.

Use a view.


EDIT: some typos

[Updated on: Fri, 15 March 2019 05:36]

Report message to a moderator

Re: Performance of Virtual column referencing another table! [message #675230 is a reply to message #675229] Fri, 15 March 2019 05:33 Go to previous message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
ok... thank you
Previous Topic: ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1720 ORA-29024: Certificate valida
Next Topic: trunc function and inner joins
Goto Forum:
  


Current Time: Fri Mar 29 09:00:28 CDT 2024