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 |
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 #675216 is a reply to message #675215] |
Fri, 15 March 2019 04:34 |
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 |
John Watson
Messages: 8938 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 #675229 is a reply to message #675225] |
Fri, 15 March 2019 05:30 |
cookiemonster
Messages: 13925 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
|
|
|
|
Goto Forum:
Current Time: Tue Jul 02 20:29:21 CDT 2024
|