virtual columns
Anto, February 21, 2008 - 11:52 am UTC
How is it different from computed colum of SQL Server ?
Anto
February 21, 2008 - 12:38 pm UTC
describe a computed column of SQL Server first
and then I'll tell how much better this is :)
Ok, found it:
http://www.informit.com/library/content.aspx?b=STY_Sql_Server_7&seqNum=101 ...
Computed columns cannot be used as any part of keys or indexes; this includes Fkeys, Pkeys, and unique indexes.
.....
ops$tkyte%ORA11GR1> create table p
2 ( b varchar2(2) primary key
3 );
Table created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create table c
2 ( a varchar2(10),
3 b as (substr( a, 5, 2 )) references p
4 );
Table created.
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create index c_idx on c(b);
Index created.
ops$tkyte%ORA11GR1> alter table c add constraint b_unique unique(b);
Table altered.
ops$tkyte%ORA11GR1> alter table c drop constraint b_unique;
Table altered.
ops$tkyte%ORA11GR1> alter table c add constraint c_pk primary key(b);
Table altered.
data properties of virtual column
AMIR RIAZ, February 25, 2008 - 2:53 am UTC
Hi Tom
in your example at otn you used function with virtual column. so when will the function will execute at insert time or at select. kind of confusing because if the function execute at insert time then column is not virtual any more because it store data. if the function execute for select statemet or integrity constraint does not sound right.
regards
Amir Riaz
February 25, 2008 - 8:02 am UTC
virtual columns are evaluated when they are selected - in the where clause, in the select list.
why does not that sound right? how else would a virtual column work??!
virtual column in 11g
Anto, February 25, 2008 - 10:48 am UTC
storage
A reader, February 26, 2008 - 5:41 pm UTC
Please note that virtual columns are not stored in the table, however, If you create index on it, it is computed on update of the columns used in virtual column.
Index on virtual column, based on function - bug?
Dusan, May 19, 2009 - 8:40 am UTC
Hi Tom,
I started to investigate virtual columns, and I reached strange situation. I created master, detail table, and wrote function, which works as running total for master table. Then, I created virtual column on master table, based on function, and created an index for the column.
Select statement which uses the index in execution plan returns different values after dml statement on detail table was issued.
When I rebuild the index, SQL statement returns correct values (so, it works in "materialized view" manner).
I do not know - is this a bug or correct behaviour (that's scary!)
Please, follow my example below.
Thanks,
Dusan
valasekd@ORCL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
Elapsed: 00:00:00.03
valasekd@ORCL> drop table big_table;
Table dropped.
Elapsed: 00:00:00.31
valasekd@ORCL> create table big_table
2 as
3 select rownum id
4 ,a.*
5 from all_objects a
6 where 1=0;
Table created.
Elapsed: 00:00:00.25
valasekd@ORCL>
valasekd@ORCL> alter table big_table nologging;
Table altered.
Elapsed: 00:00:00.01
valasekd@ORCL>
valasekd@ORCL> declare
2 l_cnt number;
3 l_rows number:=1000000;
4 begin
5 insert /*+ append */
6 into big_table
7 select rownum
8 ,a.*
9 from all_objects a;
10 l_cnt := sql%rowcount;
11 commit;
12 while (l_cnt<l_rows) loop
13 insert /*+ APPEND */ into big_table
14 select rownum+l_cnt
15 ,OWNER
16 ,OBJECT_NAME
17 ,SUBOBJECT_NAME
18 ,OBJECT_ID
19 ,DATA_OBJECT_ID
20 ,OBJECT_TYPE
21 ,CREATED
22 ,LAST_DDL_TIME
23 ,TIMESTAMP
24 ,STATUS
25 ,TEMPORARY
26 ,GENERATED
27 ,SECONDARY
28 ,NAMESPACE
29 ,EDITION_NAME
30 from big_table
31 where rownum <= l_rows-l_cnt;
32 l_cnt := l_cnt + sql%rowcount;
33 commit;
34 end loop;
35 end;
36 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:20.75
valasekd@ORCL> drop table big_table_owners
2 /
Table dropped.
Elapsed: 00:00:00.07
valasekd@ORCL>
valasekd@ORCL> create table big_table_owners as
2 select distinct owner from big_table;
Table created.
Elapsed: 00:00:03.73
valasekd@ORCL>
valasekd@ORCL> alter table big_table_owners add constraint
2 big_table_owners_pk primary key (owner)
3 /
Table altered.
Elapsed: 00:00:00.03
Elapsed: 00:00:00.09
valasekd@ORCL>
valasekd@ORCL>
valasekd@ORCL> alter table big_table add constraint
2 big_table_pk primary key (id)
3 using index (create unique index big_table_pk
4 on big_table(id)
5 --reverse
6 --tablespace big_table
7 )
8 /
Table altered.
Elapsed: 00:00:04.00
valasekd@ORCL>
valasekd@ORCL> create index big_table_owner_fk_i on big_table(owner)
2 parallel nologging compute statistics
3 /
Index created.
Elapsed: 00:00:08.42
-- Note - f_owner_count works as running total
valasekd@ORCL>
valasekd@ORCL> create or replace function f_owner_count (p_owner in varchar2)
2 return number
3 deterministic
4 as
5 l_return number;
6 begin
7 select count(*) into l_return
8 from big_table
9 where owner=p_owner;
10 return l_return;
11 end;
12 /
Function created.
Elapsed: 00:00:00.07
-- Note - creation of virtual column on master table, based on function f_owner_count
valasekd@ORCL>
valasekd@ORCL> alter table big_table_owners add (
2 owner_count number
3 generated always as
4 (f_owner_count(owner)
5 ) virtual
6 );
Table altered.
Elapsed: 00:00:00.03
valasekd@ORCL>
valasekd@ORCL>
-- Note - virtual column index
valasekd@ORCL> create index big_table_owners_owner_count_i on big_table_owners(owner_count)
2 /
Index created.
Elapsed: 00:00:02.04
valasekd@ORCL>
valasekd@ORCL> ALTER TABLE big_table ADD (CONSTRAINT
2 big_table_owner_fk FOREIGN KEY
3 (owner) REFERENCES big_table_owners
4 (owner))
5 /
Table altered.
Elapsed: 00:00:02.12
valasekd@ORCL> begin
2 dbms_stats.gather_table_stats (
3 ownname => user
4 ,tabname => 'BIG_TABLE_OWNERS'
5 ,method_opt => 'for all indexed columns'
6 ,cascade => TRUE);
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.06
valasekd@ORCL> begin
2 dbms_stats.gather_table_stats (
3 ownname => user
4 ,tabname => 'BIG_TABLE'
5 ,method_opt => 'for all indexed columns'
6 ,cascade => TRUE);
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:12.51
-- Note - Full scan
valasekd@ORCL> select * from big_table_owners;
OWNER OWNER_COUNT
------------------------------ -----------
WKSYS 3990
AA 15
MDSYS 14820
TSMSYS 32
WK_TEST 615
PUBLIC 404053
OUTLN 112
CTXSYS 2850
OLAPSYS 8595
FLOWS_FILES 150
HR 405
SYSTEM 7373
EXFSYS 2715
SCOTT 90
VALASEKD 210
SH 4535
DBSNMP 608
ORDSYS 34155
ORDPLUGINS 75
SYSMAN 30615
OE 960
PM 90
XDB 5160
FLOWS_030000 13890
IX 555
BI 120
SYS 418420
WMSYS 3872
SI_INFORMTN_SCHEMA 120
REPADM 40800
30 rows selected.
Elapsed: 00:00:00.53
valasekd@ORCL>
-- Note - Using index on virtual column
valasekd@ORCL> select * from big_table_owners
2 where owner_count=owner_count;
OWNER OWNER_COUNT
------------------------------ -----------
AA 15
TSMSYS 32
ORDPLUGINS 75
SCOTT 90
PM 90
OUTLN 112
BI 120
SI_INFORMTN_SCHEMA 120
FLOWS_FILES 150
VALASEKD 210
HR 405
IX 555
DBSNMP 608
WK_TEST 615
OE 960
EXFSYS 2715
CTXSYS 2850
WMSYS 3872
WKSYS 3990
SH 4535
XDB 5160
SYSTEM 7373
OLAPSYS 8595
FLOWS_030000 13890
MDSYS 14820
SYSMAN 30615
ORDSYS 34155
REPADM 40800
PUBLIC 404053
SYS 418420
30 rows selected.
Elapsed: 00:00:00.03
valasekd@ORCL> set autotrace traceonly explain statistics
valasekd@ORCL>
valasekd@ORCL>
valasekd@ORCL> set autotrace traceonly explain statistics
valasekd@ORCL> select * from big_table_owners;
30 rows selected.
Elapsed: 00:00:00.50
Execution Plan
----------------------------------------------------------
Plan hash value: 2956941330
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 300 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| BIG_TABLE_OWNERS | 30 | 300 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
2434 consistent gets
0 physical reads
0 redo size
1046 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
valasekd@ORCL>
valasekd@ORCL> select * from big_table_owners
2 where owner_count=owner_count;
30 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3345558246
----------------------------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
----------------------------------------------------------------------------------------------------
----------
| 0 | SELECT STATEMENT | | 30 | 300 | 2 (0)|
00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE_OWNERS | 30 | 300 | 2 (0)|
00:00:01 |
|* 2 | INDEX FULL SCAN | BIG_TABLE_OWNERS_OWNER_COUNT_I | 30 | | 1 (0)|
00:00:01 |
----------------------------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER_COUNT" IS NOT NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1039 bytes sent via SQL*Net to client
426 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30 rows processed
valasekd@ORCL> set autotrace off
valasekd@ORCL>
valasekd@ORCL> drop table big_table_backup
2 /
Table dropped.
Elapsed: 00:00:00.17
-- Note: Just store few rows for 'SYS' in backup table
valasekd@ORCL> create table big_table_backup
2 as select * from big_table
3 where owner='SYS'
4 and rownum <=420;
Table created.
Elapsed: 00:00:00.15
valasekd@ORCL>
valasekd@ORCL>
valasekd@ORCL> delete from big_table where id in (select id from big_table_backup);
420 rows deleted.
Elapsed: 00:00:00.06
valasekd@ORCL> select * from big_table_owners;
OWNER OWNER_COUNT
------------------------------ -----------
WKSYS 3990
AA 15
MDSYS 14820
TSMSYS 32
WK_TEST 615
PUBLIC 404053
OUTLN 112
CTXSYS 2850
OLAPSYS 8595
FLOWS_FILES 150
HR 405
SYSTEM 7373
EXFSYS 2715
SCOTT 90
VALASEKD 210
SH 4535
DBSNMP 608
ORDSYS 34155
ORDPLUGINS 75
SYSMAN 30615
OE 960
PM 90
XDB 5160
FLOWS_030000 13890
IX 555
BI 120
SYS 418000 <--Good result (full scan)
WMSYS 3872
SI_INFORMTN_SCHEMA 120
REPADM 40800
30 rows selected.
Elapsed: 00:00:00.51
valasekd@ORCL> select * from big_table_owners
2 where owner_count=owner_count;
OWNER OWNER_COUNT
------------------------------ -----------
AA 15
TSMSYS 32
ORDPLUGINS 75
SCOTT 90
PM 90
OUTLN 112
BI 120
SI_INFORMTN_SCHEMA 120
FLOWS_FILES 150
VALASEKD 210
HR 405
IX 555
DBSNMP 608
WK_TEST 615
OE 960
EXFSYS 2715
CTXSYS 2850
WMSYS 3872
WKSYS 3990
SH 4535
XDB 5160
SYSTEM 7373
OLAPSYS 8595
FLOWS_030000 13890
MDSYS 14820
SYSMAN 30615
ORDSYS 34155
REPADM 40800
PUBLIC 404053
SYS 418420 <--Bad result (using index)
30 rows selected.
Elapsed: 00:00:00.01
-- Note: Lets try to rebuild index
valasekd@ORCL> alter index big_table_owners_owner_count_i rebuild
2 ;
Index altered.
Elapsed: 00:00:00.56
valasekd@ORCL> select * from big_table_owners;
OWNER OWNER_COUNT
------------------------------ -----------
WKSYS 3990
AA 15
MDSYS 14820
TSMSYS 32
WK_TEST 615
PUBLIC 404053
OUTLN 112
CTXSYS 2850
OLAPSYS 8595
FLOWS_FILES 150
HR 405
SYSTEM 7373
EXFSYS 2715
SCOTT 90
VALASEKD 210
SH 4535
DBSNMP 608
ORDSYS 34155
ORDPLUGINS 75
SYSMAN 30615
OE 960
PM 90
XDB 5160
FLOWS_030000 13890
IX 555
BI 120
SYS 418000 <--Good result (full scan)
WMSYS 3872
SI_INFORMTN_SCHEMA 120
REPADM 40800
30 rows selected.
Elapsed: 00:00:00.53
valasekd@ORCL> select * from big_table_owners
2 where owner_count=owner_count;
OWNER OWNER_COUNT
------------------------------ -----------
AA 15
TSMSYS 32
ORDPLUGINS 75
SCOTT 90
PM 90
OUTLN 112
BI 120
SI_INFORMTN_SCHEMA 120
FLOWS_FILES 150
VALASEKD 210
HR 405
IX 555
DBSNMP 608
WK_TEST 615
OE 960
EXFSYS 2715
CTXSYS 2850
WMSYS 3872
WKSYS 3990
SH 4535
XDB 5160
SYSTEM 7373
OLAPSYS 8595
FLOWS_030000 13890
MDSYS 14820
SYSMAN 30615
ORDSYS 34155
REPADM 40800
PUBLIC 404053
SYS 418000 <--Good result (using index)
30 rows selected.
Elapsed: 00:00:00.04
-- Note: Insert rows from backup
valasekd@ORCL> insert into big_table select * from big_table_backup
2 /
420 rows created.
Elapsed: 00:00:00.01
valasekd@ORCL> commit;
Commit complete.
Elapsed: 00:00:00.00
valasekd@ORCL> select * from big_table_owners;
OWNER OWNER_COUNT
------------------------------ -----------
WKSYS 3990
AA 15
MDSYS 14820
TSMSYS 32
WK_TEST 615
PUBLIC 404053
OUTLN 112
CTXSYS 2850
OLAPSYS 8595
FLOWS_FILES 150
HR 405
SYSTEM 7373
EXFSYS 2715
SCOTT 90
VALASEKD 210
SH 4535
DBSNMP 608
ORDSYS 34155
ORDPLUGINS 75
SYSMAN 30615
OE 960
PM 90
XDB 5160
FLOWS_030000 13890
IX 555
BI 120
SYS 418420 <-- Good result (full scan)
WMSYS 3872
SI_INFORMTN_SCHEMA 120
REPADM 40800
30 rows selected.
valasekd@ORCL> select * from big_table_owners
2 where owner_count=owner_count;
OWNER OWNER_COUNT
------------------------------ -----------
AA 15
TSMSYS 32
ORDPLUGINS 75
SCOTT 90
PM 90
OUTLN 112
BI 120
SI_INFORMTN_SCHEMA 120
FLOWS_FILES 150
VALASEKD 210
HR 405
IX 555
DBSNMP 608
WK_TEST 615
OE 960
EXFSYS 2715
CTXSYS 2850
WMSYS 3872
WKSYS 3990
SH 4535
XDB 5160
SYSTEM 7373
OLAPSYS 8595
FLOWS_030000 13890
MDSYS 14820
SYSMAN 30615
ORDSYS 34155
REPADM 40800
PUBLIC 404053
SYS 418000 <--Bad result (using index)
30 rows selected.
valasekd@ORCL> spool off
May 23, 2009 - 12:03 pm UTC
valasekd@ORCL> create or replace function f_owner_count (p_owner in varchar2)
2 return number
3 deterministic
4 as
5 l_return number;
6 begin
7 select count(*) into l_return
8 from big_table
9 where owner=p_owner;
10 return l_return;
11 end;
12 /
please tell me how a function that queries a table that is modifiable could be deterministic?
You might as well be indexing the dbms_random.random function - think about it.
Your function is not even remotely deterministic.
the function you index MUST return the same exact value every single time it is called with the same inputs.
that if, if f(x) returns 42 when x=55, f(x) MUST return 42 every time - forever - that x=55 is passed to it.
You lied to us, your function is clearly not deterministic, this is not a bug, this is something you did to yourself.
Ok, I agree, but ..
A reader, May 27, 2009 - 4:32 am UTC
You know, at least a warning or something else should be raised.
I just test how far can I go, whoever can do the same :-), causing nasty results, frustrations etc.
Thanks,
Dusan
May 27, 2009 - 11:23 am UTC
huh??
the documentation is the warning????????????? I think you are missing something really big here.
You cannot index a function
unless you make it deterministic
that is how YOU tell US that you have done YOUR due diligence and made a function that is safe to index (by default, we PRESUME your code is not safe - YOU have to tell us "no no, I made this special - it is safe to index"
what more "warning" can we do? We do not remember what you told us the result was last year from this function - we rely on you to not lie to us and to provide us a function that is deterministic.
I'm not sure you read about this before using it.
I think you did something like this:
ops$tkyte%ORA10GR2> create function f ( x in number ) return number as begin return dbms_random.random; end;
2 /
Function created.
ops$tkyte%ORA10GR2> create index t_idx on t( f(data) );
create index t_idx on t( f(data) )
*
ERROR at line 1:
ORA-30553: The function is not deterministic
and a simple looking up of the error message would reveal:
ops$tkyte%ORA10GR2> !oerr ora 30553
30553, 00000, "The function is not deterministic"
// *Cause: The function on which the index is defined is not deterministic
// *Action: If the function is deterministic, mark it DETERMINISTIC. If it
// is not deterministic (it depends on package state, database state,
// current time, or anything other than the function inputs) then
// do not create the index. The values returned by a deterministic
// function should not change even when the function is rewritten or
// recompiled.
and you might have read the first sentence of the action and marked it deterministic
ops$tkyte%ORA10GR2> create function f ( x in number ) return number DETERMINISTIC as begin return dbms_random.random; end;
2 /
Function created.
ops$tkyte%ORA10GR2> create index t_idx on t( f(data) );
but obviously that function IS NOT deterministic.
a simple small research project for deterministic would have revealed:
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref943
Any user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function will always return the same output return value for any given set of input argument values, now and in the future. http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_indexing.htm#sthref611 Any top-level or package-level PL/SQL functions that are used in the index expression must be declared as DETERMINISTIC. That is, they always return the same result given the same input, for example, the UPPER function. You must ensure that the subprogram really is deterministic, because Oracle Database does not check that the assertion is true.that last bit should have been written as:
because Oracle Database cannot possibly check that the assertion is true - in my opinion
you were warned - a lot.
Ok, it is clear now
Dusan, June 01, 2009 - 5:56 am UTC
I really did not check what "DETERMINISTIC" exactly means, I only knew that "DETERMINISTIC" is used when creating index based on function. Next time I'll do research in documentation first.
Thanks a lot,
Dusan
virtual column
eshwar, March 20, 2010 - 7:51 am UTC
Tom, i have a table with 350M data in which date is stored in 3 columns in the format YY,MM and DD. I want to create another table with the same data using range partition. I tried to create range partition by concatinating all the 3 columns. It didn't work :-( and then tried with (YY,MM,DD). I was able to create a table but when year is YY = '01' since it is less than '96' , the data was going to the partition which is less than '96'. When i searched in google, I came to know about virtual column in 11g and created a table using virtual column. But my question here is , will this help me to get best performance throughput? or is there any way i can create range partition having 4 digit year (table has got two digit YY ) so that data goes into correct partition.
anticipating your reply.
thanks in advance
March 20, 2010 - 9:36 am UTC
you now know why you use the RIGHT TYPE FOR THE DATA. What brain surgeon decided to use three attributes to represent a date! IF they wanted to kill the optimizers ability to do things correctly, they wanted to make it hard on the end users querying the data, and they wanted to make it hard to use and reuse this data intelligently. They certainly achieved their goal.
even better yet, in a little while, what will the data:
50, 12, 05
represent? YY - what kind of coder/DBA uses YY in the year 2010?? Probably one that started coding after the year 2000 :(
Why, instead of trying to hack around the deficiencies of the existing bad data model, wouldn't you even consider fixing the data?
anticipating your reply.
Confusion
A reader, March 20, 2010 - 11:34 am UTC
"virtual columns are evaluated when they are selected - in the where clause, in the select list."
Tom, I am a little bit confused about this. Then how does Oracle do it when they are part of a constraint ? Don't they have to be evaluated at the time of the DML as well?
March 22, 2010 - 8:18 am UTC
sure, they are evaluated when referenced - where ever they are referenced. Meaning, they are virtual, they do not consume any space in the table itself.
Is USER a Virtual Column in all the Tables in DB?
Mukund Nampally, October 13, 2010 - 12:41 pm UTC
Hi Tom,
IS USER a Virtual Column in every Table in the Database?
SELECT *
FROM DUAL
WHERE USER='SCOTT'
Doesn't throw any Error.What is itz significance?
October 15, 2010 - 7:08 am UTC
USER is an Oracle Function
Ibrahim Bakhsh, October 26, 2010 - 11:08 am UTC
I Think That USER is one of Oracle Functions that provides you with value to compare with or "use it" for what ever you need
But if you need to go deeper of using oracle defined functions with columns i can say that you can use it in default statement of the table column or even in check
for example we have a table of arabic Hijry data by the Type of Number(8) and sometimes it's easier to read and write this value as number and not as something else
to prevent users from inserting or updating non_Date_Values you can make This type of Check Constraints :
Create Table DATE_TEST(HDATE NUMBER(8),MDATE NUMBER(8));
ALTER TABLE DATE_TEST
ADD CONSTRAINT CK_DT_TST_HDATE
CHECK TO_DATE(MDATE,'YYYYMMDD')=TO_DATE(MDATE,'YYYYMMMDD'))
I Thinks this is a solution to soo many problems
but the as ability in 11g added the ability to use only one column and the second as hijrah(mdate) user defined function
Regards
Ibrahim Bakhsh
October 26, 2010 - 8:14 pm UTC
You don't even have to think for that one, I pointed you to the documentation which states:
USER returns the name of the session user (the user who logged on) with the data type VARCHAR2. Oracle Database compares values of this function with blank-padded comparison semantics.
I don't see at all what that has to do with anything else you wrote however.
Using Deterministic Functions with Tables
Ibrahim Bakhsh, October 26, 2010 - 11:44 pm UTC
<!--I don't see at all what that has to do with anything else you wrote however. -->
just needed to know if i can post a review with you in this site ....
it worked :) walah !!!
----------------------------------------------------------
Using deterministic function with virtual functions with SETUP_FLAG in some small cases
----------------------------------------------------------
This is a discussion and CLARIFICATION before starting to do anything:
i may be WRONG and missing some of the facts
so bear with me :
i have an idea if it's points out to be considerable then i think that the deterministic virtual column idea can work
in some cases with some adjustments:
if you have table a column of value despondent on others tables that there records are setup information for the targeted table then you can create a column in which you
add the (SETUP_FLAG) in the target (detail) table
and each time any update accures in master setup tables or in other tables effecting the result of this formulla we add one to this flag like (SETUP_FLAG=SETUP_FLAG+1)
then use this column in a deterministic function
and add EMP_LAST_SAL column as of the function you created using for example the primary key columns + SETUP_FLAG as passing variables
if this column is based on that function for almost 90% there well be no change in the master setup tables
the query well be fine right valued i think and fast
and in every and each table of the master tables you create a Triggers to update this column(SETUP_FLAG) in this field by update or delete on master tables
it's yet to be tested , and before starting
is it a save idea if you have less DML on Master Tables
and More in Detail Table
and much more much more much more in Query Modes
Consider that executing that big trigger in master tables can be tricky and some what heavy but if you consider the conditioning like
start_date - end_date - dept - job_cat - sal_per_houre
in our case this information are written once and changed so little in time
but would it work and give us what we wish to achieve ?
October 27, 2010 - 5:56 am UTC
I could not follow this thought at all. I don't know what you are trying to address.
add the (SETUP_FLAG) in the target (detail) table
and each time any update accures in master setup tables or in other tables
effecting the result of this formulla we add one to this flag like
(SETUP_FLAG=SETUP_FLAG+1)
then use this column in a deterministic function
I had to stop right around there.
what target table? (don't know what you are trying to do)
what master table?
how can you use a column that is constantly changing in a *DETERMINISTIC FUNCTION* (a function whose return value cannot change given the same inputs)?
oh, and what function???
One Of The Inputs well be The FLAG
Ibrahim Bakhsh, October 27, 2010 - 6:34 am UTC
you just said "function whose return value cannot change given the same inputs"
one of the inputs well be This FLAG and every time i need the function to Re Calculate i Just Change one of the inputs by adding one for example to the FLAG and when the function get's new flag value it well give me the new result .
and even if i need the previous "before setup change" value i can see it by deterministic aproch
Don't you Think ?
October 27, 2010 - 6:45 am UTC
I don't think anything - but only because I have no clue what problem you are trying to solve - none.
big page here, lots of stuff on it, if you want to ask something - make it self contained - but everything germaine to the question in the question itself.
did you read what I wrote?
what target table?
........ dot dot dot dot dot..
Using Deterministic Functions with Tables
Ibrahim Bakhsh, October 27, 2010 - 10:19 pm UTC
ALTER TABLE EMP ADD(SETUP_FLAG NUMBER DEFAULT 1);
ALTER TABLE DEPT ADD(HH_VALUE NUMBER);
UPDATE DEPT SET HH_VALUE = DEPTNO;
CREATE OR REPLACE FUNCTION
CAL_EMP_PAYMENT(X_EMPNO EMP.EMPNO%TYPE,X_DEPTNO EMP.DEPTNO%TYPE,X_SAL EMP.SAL%TYPE,X_FLAG EMP.SETUP_FLAG%TYPE)
RETURN NUMBER
DETERMINISTIC
AS
V_HH_VALUE DEPT.HH_VALUE%TYPE;
V_VAL NUMBER;
V_FLAG EMP.SETUP_FLAG%TYPE;
BEGIN
-- I WELL NOT USE IT JUST FOR RE CALCULATE
V_FLAG := X_FLAG;
SELECT HH_VALUE
INTO V_HH_VALUE
FROM DEPT
WHERE DEPTNO = X_DEPTNO;
V_VAL := TRUNC((X_SAL-250)/30)+V_HH_VALUE;
RETURN V_VAL;
END;
ALTER TABLE emp add(HOURE_PAYMENT NUMBER generated always as (CAL_EMP_PAYMENT(EMPNO,DEPTNO,SAL,SETUP_FLAG)) virtual);
CREATE OR REPLACE TRIGGER TRIG_DEPT_EMP_HU
AFTER DELETE OR UPDATE
ON IGB.EMP
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
UPDATE EMP
SET SETUP_FLAG = SETUP_FLAG + 1
WHERE DEPTNO = nvl(:NEW.DEPTNO,:old.deptno);
END;
/
October 28, 2010 - 11:57 am UTC
I'll repeat
big page here, lots of stuff on it, if you want to ask something - make it self contained - but everything germaine to the question in the question itself.
Now I see a bunch of code , but not a single bit of "context". Now what?
Using Deterministic Functions with Tables
Ibrahim Bakhsh, October 28, 2010 - 11:07 pm UTC
i am sorry for the miss writing
due to busy in work stuff :
any how lets tack an example of the tables dept,emp in scott schema
lets pretend that we need to set extra hourly salary of all personal as there departments
in other words each department will have its Owen bounces value to implement on their employees
so we made some changes to both dept,emp tables to implement this example as following:
--adding setup_flag into EMP table :
--it represent the number of changes made
--to this employees bones salary amount
--after inserting this employees record:
ALTER TABLE EMP ADD(SETUP_FLAG NUMBER DEFAULT 1);
--adding the column HH_VALUE on DEPT table:
--it well represent the value from which we calculate the bones amount sal for each emp
ALTER TABLE DEPT ADD(HH_VALUE NUMBER);
--setting some example values for each department:
UPDATE DEPT SET HH_VALUE = DEPTNO;
--now in order to add a column that represent the result value of the calculation
--we created a deterministic function with these inputs:
--(EMPNO,DEPTNO,SAL,SETUP_FLAG)
CREATE OR REPLACE FUNCTION
CAL_EMP_PAYMENT(X_EMPNO EMP.EMPNO%TYPE,X_DEPTNO EMP.DEPTNO%TYPE,X_SAL EMP.SAL%TYPE,X_FLAG
EMP.SETUP_FLAG%TYPE)
RETURN NUMBER
DETERMINISTIC
AS
V_HH_VALUE DEPT.HH_VALUE%TYPE;
V_VAL NUMBER;
V_FLAG EMP.SETUP_FLAG%TYPE;
BEGIN
-- I WELL NOT USE IT JUST FOR RE CALCULATE
V_FLAG := X_FLAG;
SELECT HH_VALUE
INTO V_HH_VALUE
FROM DEPT
WHERE DEPTNO = X_DEPTNO;
V_VAL := TRUNC((X_SAL-250)/30)+V_HH_VALUE;
RETURN V_VAL;
END;
--Now we added a column to EMP table
--driving the value of this column from USER-DEFINED-FUNCTION
ALTER TABLE emp add(HOURE_PAYMENT NUMBER generated always as
(CAL_EMP_PAYMENT(EMPNO,DEPTNO,SAL,SETUP_FLAG)) virtual);
--in order for the deterministic function
--to give the right answer we always change
--one input of the function's inputs
--while changing the bounces value of
--the department on DEPT table.
CREATE OR REPLACE TRIGGER TRIG_DEPT_EMP_HU
AFTER DELETE OR UPDATE
ON IGB.EMP
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
UPDATE EMP
SET SETUP_FLAG = SETUP_FLAG + 1
WHERE DEPTNO = nvl(:NEW.DEPTNO,:old.deptno);
END;
/
That's it
does this satisfy everyone as a complete explanation of the problem
and an idea on how to work around it for solving.
the question is does this solution work for all cases ?
or it's just for this case alone ?
November 01, 2010 - 5:01 am UTC
DETERMINISTIC
AS
V_HH_VALUE DEPT.HH_VALUE%TYPE;
V_VAL NUMBER;
V_FLAG EMP.SETUP_FLAG%TYPE;
BEGIN
-- I WELL NOT USE IT JUST FOR RE CALCULATE
V_FLAG := X_FLAG;
SELECT HH_VALUE
INTO V_HH_VALUE
FROM DEPT
WHERE DEPTNO = X_DEPTNO;
V_VAL := TRUNC((X_SAL-250)/30)+V_HH_VALUE;
RETURN V_VAL;
I stopped reading right there.
that function is not deterministic. all I have to do is update dept set hh_value = something else and that function returns a different value given the same input.
so, this is not a solution, it is a problem/buggy bit of implementation
In general, if your function includes "select" or calls another bit of code that does.... the odds are it IS NOT deterministic (in a big way)
what is a "bones amount" anyway?
@Ibrahim Bakhsh
al0, October 31, 2010 - 5:04 am UTC
Hi Ibrahim,
You described a solution for the unspecified problem and asked to asses it this solution is any good. It is simply impossible as nobody knows the problem that you strive to solve.
Please, describe the problem first!
Do not try to explain how but what and why.
Oleksandr
November 01, 2010 - 2:59 pm UTC
well said sir
Fasting Results of Query By Adding Vertual Columns
Ibrahim Bakhsh, October 31, 2010 - 8:21 am UTC
hi Back
First of , Borden by week writing skills
The Main Problem is :
I need to Use deterministic function with virtual Column to fasten some Query result of the a huge complicated queries depends on data collected from more than one table
The reason i use Database functions is to create once a formula that i can use any where
in the application side , reports , auto generated reports , and other type of output's ... etc
in this function i do all of my calculation and conduct all of the conditions needed to be done
for the result of this function to be true and as requested from the system designer
the result of this work is amazingly wonderful
but in large queries takes more than 2 minutes to generate group by results after completing all the calculations
but the same query when we don't use the function in the Select statement the result comes fast in 2 seconds.
truly the application developers tried to make so many formula in the application side but the perfection and united result was not approached and
the speed over large amount of data was not the same as it's with oracle built in functions
that's why i try to find solution's to fasten the function results.
but before you start you all have to know that it's not right there is something wrong in it
but i do not know it yet:
--adding setup_flag into EMP table :
--it represent the number of changes made
--to this employees bones salary amount
--after inserting this employees record:
ALTER TABLE EMP ADD(SETUP_FLAG NUMBER DEFAULT 1);
--adding the column HH_VALUE on DEPT table:
--it well represent the value from which we calculate the bones amount sal for each emp
ALTER TABLE DEPT ADD(HH_VALUE NUMBER);
--setting some example values for each department:
UPDATE DEPT SET HH_VALUE = DEPTNO;
--now in order to add a column that represent the result value of the calculation
--we created a deterministic function with these inputs:
--(EMPNO,DEPTNO,SAL,SETUP_FLAG)
CREATE OR REPLACE FUNCTION
CAL_EMP_PAYMENT(X_EMPNO EMP.EMPNO%TYPE,X_DEPTNO EMP.DEPTNO%TYPE,X_SAL EMP.SAL%TYPE,X_FLAG
EMP.SETUP_FLAG%TYPE)
RETURN NUMBER
DETERMINISTIC
AS
V_HH_VALUE DEPT.HH_VALUE%TYPE;
V_VAL NUMBER;
V_FLAG EMP.SETUP_FLAG%TYPE;
BEGIN
-- I WELL NOT USE IT JUST FOR RE CALCULATE
V_FLAG := X_FLAG;
SELECT HH_VALUE
INTO V_HH_VALUE
FROM DEPT
WHERE DEPTNO = X_DEPTNO;
V_VAL := TRUNC((X_SAL-250)/30)+V_HH_VALUE;
RETURN V_VAL;
END;
--Now we added a column to EMP table
--driving the value of this column from USER-DEFINED-FUNCTION
ALTER TABLE emp add(HOURE_PAYMENT NUMBER generated always as
(CAL_EMP_PAYMENT(EMPNO,DEPTNO,SAL,SETUP_FLAG)) virtual);
--in order for the deterministic function
--to give the right answer we always change
--one input of the function's inputs
--while changing the bounces value of
--the department on DEPT table.
CREATE OR REPLACE TRIGGER TRIG_DEPT_EMP_HU
AFTER DELETE OR UPDATE
ON IGB.DEPT
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
UPDATE EMP
SET SETUP_FLAG = SETUP_FLAG + 1
WHERE DEPTNO = nvl(:NEW.DEPTNO,:old.deptno);
END;
/
the queries is very fast and the group result as it's like normal query "without functioned column"
at lease the second time i issue the command .
but i still think that i am missing something !?
@Ibrahim
Oleksandr Alesinskyy, November 01, 2010 - 6:55 am UTC
Hi Ibrahim,
The Main Problem is :
I need to Use deterministic function with virtual Column to fasten some Query result of the a huge
complicated queries depends on data collected from more than one table
This description nicely mix
what and
how - something your were advised to avoid.
The problem (
what) is "I have a query that performs not fat enough. It takes ... seconds to process ... rows, while it has to be no more than ... seconds. How can I achieve it. My query is ..., the the table structure is ..., indexes are .... The execution plan is ..." - that's all, nothing more.
Now
how part (your approach to the solution):
I
guess (
not "I need deterministic fumction with virtual column") that virtual columns may be of some use in this case ... .
Why not use a view?
A reader, December 29, 2010 - 10:14 am UTC
Wasn't this feature available in earlier versions by using views, function-based indexes and such? Do virtual columns offer any benefits that are not available using those other techniques? Thanks
December 30, 2010 - 1:21 pm UTC
ops$tkyte%ORA11GR2> create table p
2 ( b varchar2(2) primary key
3 );
Table created.
ops$tkyte%ORA11GR2> create table c
2 ( a varchar2(10),
3 b as (substr( a, 5, 2 )) references p on delete cascade
4 );
Table created.
ops$tkyte%ORA11GR2> insert into p (b) values ( 'AA' );
1 row created.
ops$tkyte%ORA11GR2> insert into c (a) values ( '1234AA7890' );
1 row created.
ops$tkyte%ORA11GR2> insert into c (a) values ( '1234xx7890' );
insert into c (a) values ( '1234xx7890' )
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.SYS_C0012420) violated - parent key not found
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select * from c;
A B
---------- --
1234AA7890 AA
do that with a view.
You can gather statistics on a virtual column... (try that with a view)
You can partition by a virtual column... (try that...)
You cannot create a TRUE unique constraint using a view/function based index. It will not be registered as a constraint in the dictionary - with virtual columns it would be.
Data duplication
A reader, December 31, 2010 - 8:38 am UTC
OK I understand. The "expression" part can be easily done in a view. I guess the real benefits are that you can partition by them, use them in declarative constraints. I like that you can put create a complicated expression as a virtual column and put a CHECK constraint on it, moving the data integrity logic closer to the data instead of using triggers, APIs, etc. Thanks
Rowtype
A reader, December 31, 2010 - 8:48 am UTC
create table xx(i date,k as (to_char(i,'yyyy')) check (k in ('2010','2011')));
declare xx_rec xx%rowtype;begin xx_rec.i:=sysdate;insert into xx values xx_rec;end;
2 /
declare xx_rec xx%rowtype;begin xx_rec.i:=sysdate;insert into xx values xx_rec;end;
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
ORA-06512: at line 1
I guess this means that existing code that uses %ROWTYPE will break if a virtual column is added to a table. Any way around this? Thanks
January 03, 2011 - 8:49 am UTC
It is what it is. Not any different than if you were not granted insert on that column.
Even worse would be if your code use the record type to blindly insert all columns without thought - AND someone adds a new column with a default value. Guess what would happen - you would defeat the default.
Recommend always - always - to use the column list when inserting - always.
insert into t ( c1, c2, c3, .. ) values ....
never
insert into t values ( ...);
I don't like the insert into T values <record> "ability" at all - a very shaky proposition.
Don't use rowtype
Sokrates, January 02, 2011 - 12:21 pm UTC
Is the answer
You cannot expect existing Code is valid when using a New Feature
Can I?
A reader, August 05, 2011 - 5:04 pm UTC
Tom:
I have processed_id, company_type, company_id in a table.
processed_id is null for the non-processed records.Can I create two virtual columns such as v_company_type, v_company_id and compute them as
v_company_type as(decode(processed_id,null,company_type,null));
v_company_id as (decode(processed_id,null,company_id,null))
and create an index on v_company_type+v_company_id to effectively select the non_processed records of a company.
Do you see an issue here with this approach?
Another thing that I tried to do:
create index x_idx on trans_table(processed_id,company_type,company_id)
partition on
partition processed (processed_id) values less than ('999999999999999')
partition non_processed values less than (maxvalue))
unusable;
then alter index x_idx rebuild partition non_processed;
but the updates are suffering.
Thanks for your help in advance.
Regards,
August 06, 2011 - 8:35 am UTC
why wouldn't you just
create index my_idx on my_table( decode( processed_id, null, 1, null ) );
and then
select * from my_table where decode( processed_id, null, 1, null ) = 1;
?
that would index only the NULL records. You could use a virtual column using that expression as well to 'hide' the function.
A reader, August 06, 2011 - 8:48 am UTC
Tom:
We do processing based company_id and company_type for the null (non-processed) records. The above will only give me all non-processe records (which would be close to a billion). Since we process per company and per type how can we achive that compositeness using an index for non-processed records which will again be updated after we process....
Regards,
A reader, August 06, 2011 - 8:58 am UTC
...cont...
To further clarify:
We process only some companies per hour through out the day. We do not process all companies at the same time.
Thanks,
A reader, August 06, 2011 - 9:11 am UTC
..cont...
We currently have a big function based composite index like
(nvl(processed_id,1),company_type, company_id) which is done of the costly update which is time consuming. Select's perform with the abov index , but updates are slow.
Regards,
August 06, 2011 - 2:39 pm UTC
and that is exactly the cost of having an index - an index is always a tradeoff.
But why not create the index on:
(
decode( processed_id, null, 1, null ),
decode( processed_id, null, company_type, null ),
decode( processed_id, null, company_id, null )
)
in that fashion only the NULL processed_id records will appear in there. Which is sort of what you were suggesting above (yes, you could use virtual columns - six one one, half a dozen the other - no effect on performance)
The update is *going* to have the pay the price of index maintenance - that is precisely the tradeoff you make when you index. You are trading off speedier access to find data, but slower to modify it.
If you partitioned the data by company_type/company_id - and then further subdivided by processed_id, putting NULL into one partition and everything else into another - you could avoid the index and just full scan - but, your update would still be impacted as the update of the processed_id would be internally processed as a delete+insert (so it would hit ALL OTHER indexes)
Virtual columns
Kannan, August 14, 2011 - 11:03 am UTC
Tom
I have rephrased the above question , as it had a fundamental issue.
I have verified in 11Gr2 , just by creating a virtual column oracle by itself does not create an index on it. Oracle implements a function based index , when an index is created on this virtual column.
From a performance perspective, which among the two techniques would you suggest for creating the virtual column 1) creating it directly using an expression or
2)hiding the expression within a cached deterministic function . Data analysis on the table ensures, that the number of rows in the cache will be very small compared to the total number of rows in the whole table .
And last , do we have any method for estimating the size for the cache.
August 14, 2011 - 8:36 pm UTC
#1 would be the only sensible approach - by creating it directly.
I fail to see any real advantages of #2 given that switching from SQL to PLSQL has a high penalty and evaluating a simple expression would likely take less cpu than that context switch would.
and a really fail to see how you can say "that the number of rows in the caches will be very small" in general? I can see there being tons of outputs.
using a result cached function doesn't seem to compute here.
But as always - feel free to benchmark. We don't know your entire situation - there could be one or two edge cases whereby it might make sense
But in general - nope
Virtual column creation ( to use a deterministic function or not )
Kannan, August 15, 2011 - 7:34 pm UTC
Thanks Tom. I really appreciate your inputs.
Clarification on virtual column expression
Kannan, August 15, 2011 - 7:58 pm UTC
The expression on the virtual column would perform the equivalent of coalesce(column a,column b,column c) . This could also be written as a case statement. Going by the plsql switching that would be incurred , I opine , a case statement would do a better job in this situation , than coalesce, when used to define the virtual column. I would like to know whether this assumption is right , as I see many topics were case is introduced as a function , I am yet to see any article which confirms case as an operator , as in other Languages like C.
August 16, 2011 - 2:02 am UTC
benchmark it, then you tell us.
That is the scientific method, you have a hypothesis " a
case statement would do a better job in this situation , than coalesce"
so, test it out - that is what I would have to do for you...
I personally think coalesce would be better - just a gut feeling. coalesce short circuts:
ops$tkyte%ORA11GR2> select coalesce( 1, 1/0 ) from dual;
COALESCE(1,1/0)
---------------
1
ops$tkyte%ORA11GR2>
so it would only evaluate what it needs to to find the first non-null value.
Benchmarking of virtual column definitions
Kannan, August 19, 2011 - 7:31 pm UTC
Created a test table with 100000 rows , which had three equivalent indexed virtual columns ,defined as listed below. The application would ensure atleast one column would be not null .
1)coalesce(test_completion_date,test_started_date,test_created_date)
2)nvl(test_completion_date,nvl(test_started_date,test_created_date)
3)case when test_completion_date is not null then test_completion_date
when test_started_date is not null then test_started_date
when test_created_date is not null then test_created_date
end
decode is not chosen , as case statement seems to be preferred going forward.
explain plans , sql response time were evaluated on each of these , and they were all behaving similarly. There was no appreciable differences in terms of statistics or time taken , that one could spot.For equi joins or selections the response was fastest .
There was no way to measure codeswitches.
As we have an RAC environment , the idea of implementing it as a cached function was given up , due to perceived code switching , and articles also have hinted that the result_cache would be different among the instances , and is not syncedm and needs additional cache clearance dba steps , when the function is to be refurnished for whatever reasons.
Toward a situation where mathematics could favor a cached function virtual column implementation , here is the problem definition .
We assume a system, tracking student responses on assignments . Given a population of 15000 students , each assignment duration of one week ,and 100 faculty members, The application initially creates 1500000 entries with creation date populated. Later on as students start responding , the start_date and completion date would eventually be filled in . Given the nature of the problem only seven entries would be expected to be rendered by the cache per assignment duration.In fact cache size would be directly proportional to the overall duration span of all the assignments and independent of everything else . This will be a very active table , when dealing with many professors , assignments , duration span and students. Your comments on this problem , more as a general tip , on cached function virtual column implementations , would be highly appreciated .
August 30, 2011 - 1:30 pm UTC
I don't see where caching would come into play here. Caching helps when you can put off doing something that takes a long time and do something faster instead.
Going to a cache (context switch out from sql and hit the SGA millions of times) would take longer than evaluating these trivial expressions.
You are not doing something that takes very long here.
I would just use coalesce and be done with it.
Index based on a cached deterministic function 11G
Kannan, August 29, 2011 - 9:59 am UTC
Tom , Oracle magazine issue sep/oct-2011, Page 76, details the topic perfectly. The function might be deterministic , might be result cached , and one could also discover calls made to the function zero times , yet the cpu time registered would be more , due to context switching, and hence there is a good reason to wrap these calls in a select func() from dual , so that , oracle gets a chance to reduce function calls made while dealing with a scalar subquery . The bottom line Creating a plsql function based index , would be resource intensive , due to context switching . This is Something which you have already covered and advised upon . Thanks
CTAS Syntax for Virtual Columns
Martin Rose, November 07, 2011 - 5:43 pm UTC
I'm trying to add a virtual column into the mix when doing a CTAS, but I cannot figure out the syntax.
CREATE TABLE ABC (USERNAME,
USER_ID,
CREATED,
TEST_COLUMN GENERATED ALWAYS AS (LENGTH(USERNAME)))
AS SELECT *
FROM ALL_USERS;
ORA-01773: may not specify column datatypes in this CREATE TABLE
I'm clearly not specifying a column datatype, so is this a bug?
November 07, 2011 - 7:18 pm UTC
there is a closed bug saying "CTAS doesn't support virtual columns", however, my reading of the documentation differs with that finding.
So, it is either
o a software bug
o a documentation bug
so, I've filed a bug to let them sort it out.
Bug 13357345
Can't figure this query using Virtual Column
Anand, January 05, 2012 - 4:32 pm UTC
I am trying to experiment using Virtual columns however I am having an issue with the results of a query I was trying. Hope you could shed some light.
create table t1 ( row_id int, zip varchar2(10), vrt_zip varchar2(5) generated always as (substr(zip, 1, 5)) virtual);
insert into t1 (row_id, zip) values(1, '63044');
insert into t1 (row_id, zip) values(2, '63043');
create table t2(row_id int, test_zip varchar(10));
insert into t2 (row_id, test_zip) values(1, '63044');
insert into t2 (row_id, test_zip) values(2, '63043');
commit;
select t1.zip, t1.vrt_zip from t2 inner join t1 on t2.row_id = t1.row_id;
/** Results of the Query 1
ZIP VRT_Z
---------- -----
63044 63044
63043 63043
**/
select t1.zip, t1.vrt_zip from t2 left join t1 on t2.row_id = t1.row_id;
/** Results of Query 2
ZIP VRT_Z
---------- -----
63044
63043
**/
/** Question..
Why does the Virtual column in the Second query return null... I am trying this on an 11.1.0.6 server.
Hope it can be reproduced on your side. Please feel free to lambast me if there is something silly going on here that I am overlooking
**/
January 05, 2012 - 4:41 pm UTC
if you are getting that result (why not a cut and paste? they are very compelling) - that would be a resolved bug, please contact support:
ops$tkyte%ORA11GR2> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
ops$tkyte%ORA11GR2> @test
ops$tkyte%ORA11GR2> drop table t1;
Table dropped.
ops$tkyte%ORA11GR2> drop table t2;
Table dropped.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t1 ( row_id int, zip varchar2(10), vrt_zip varchar2(5) generated always as (substr(zip, 1, 5)) virtual);
Table created.
ops$tkyte%ORA11GR2> insert into t1 (row_id, zip) values(1, '63044');
1 row created.
ops$tkyte%ORA11GR2> insert into t1 (row_id, zip) values(2, '63043');
1 row created.
ops$tkyte%ORA11GR2> create table t2(row_id int, test_zip varchar(10));
Table created.
ops$tkyte%ORA11GR2> insert into t2 (row_id, test_zip) values(1, '63044');
1 row created.
ops$tkyte%ORA11GR2> insert into t2 (row_id, test_zip) values(2, '63043');
1 row created.
ops$tkyte%ORA11GR2> commit;
Commit complete.
ops$tkyte%ORA11GR2> select t1.zip, t1.vrt_zip from t2 inner join t1 on t2.row_id = t1.row_id;
ZIP VRT_Z
---------- -----
63044 63044
63043 63043
ops$tkyte%ORA11GR2> select t1.zip, t1.vrt_zip from t2 left join t1 on t2.row_id = t1.row_id;
ZIP VRT_Z
---------- -----
63044 63044
63043 63043
Anand, January 05, 2012 - 4:54 pm UTC
Thank You Tom for the immediate response. I will contact support regarding the issue.
(I could have made a copy and paste, but I thought with the way I did, you could do a copy-paste and make your work easy..)
Virtual Column vs Views
Soumadip, April 16, 2012 - 11:56 am UTC
Hi Tom,
What is the difference between Virtual Column and Views?
View can also accomplish the same functionality as of Virtual column.
So when should we use Virtual column and when should we use a view?
As an example :-
SQL> CREATE TABLE tab1
2 (sal1 number
3 ,sal2 number
4 ,sum_sal number generated always as (sal1 + sal2) virtual
5 );
Table created
SQL> insert into tab1 (sal1, sal2) values (10,20);
1 row inserted
SQL> select * from tab1;
SAL1 SAL2 SUM_SAL
---------- ---------- ----------
10 20 30
SQL> CREATE VIEW view1
2 AS
3 SELECT sal1
4 ,sal2
5 ,sal1 + sal2 AS sum_sal
6 FROM tab1;
View created
SQL> SELECT * FROM view1;
SAL1 SAL2 SUM_SAL
---------- ---------- ----------
10 20 30
In this case which one should we prefer - Virtual column or a View?
April 16, 2012 - 4:09 pm UTC
View can also accomplish the same functionality as of Virtual column.
create view x
as
select dummy, dummy || 'x' dummy2
from dual;
can I gather stats on dummy2?
can I index dummy2?
can I put a constraint on dummy2?
could I have partitioned dual by dummy2?
for example - a virtual column behaves just like a column, an expression in a view is just an expression in a view - you can't do anything other than select it really.
Your comments please ...
Parthiban Nagarajan, April 29, 2012 - 11:15 am UTC
April 30, 2012 - 8:18 am UTC
how would this differ from a create view?
how would this differ from someone that can create a procedure (no view, no table) and creates a procedure that does this same thing and asks you to run it?
It is no more (or less) dangerous than a view, than just a procedure all by itself.
Two column names pointing to the same column
Manas, July 17, 2012 - 12:56 am UTC
We have a need wherein two different parties want the same column with different names. Say my column is named emp_name and some require it to be called as employee_name. i though virtual function could help me out. so I tried creating a table like-
create table test(
emp_id NUMBER,
emp_name VARCHAR2(100),
employee_name AS (emp_name));
The following error is generated-
Error starting at line 1 in command:
Error at Command Line:4 Column:21
Error report:
SQL Error: ORA-54016: Invalid column expression was specified
However, the following code works-
create table test(
emp_id NUMBER,
emp_name VARCHAR2(100),
employee_name AS (UPPER(emp_name)));
I understand that Virtual column needs an expression or a function. But why does it not allows to refer to another column directly. Is there already a functionality for our need. (We do not want to create a view ..)
July 17, 2012 - 8:14 am UTC
why does it not let this? probably because there is no real need ever to do something like this...
(We do not want to create a view ..)sorry, sometimes the best way to do something is something you "don't" want to do.
it doesn't seem to make sense to do this though, I hope you don't have any code that uses * or an insert without a column list, you are going to be in a really bad way if you do.
ops$tkyte%ORA11GR2> create table test(
2 emp_id NUMBER,
3 emp_name VARCHAR2(100),
4 employee_name AS (emp_name||''));
Table created.
will do it without changing the meaning of your data.
hopefully the party that wants employee_name won't ever be inserting or updating this column... because virtual columns cannot be modified...
if they do, you need a view.
I would actually change the requirement, I see nothing but bad things as a result of having the same exact column referred to by two different names. I would create a view for each party and let them have at it.
Virtual columns and window functions
Stewart Bryson, September 03, 2012 - 10:24 am UTC
Tom:
Either window functions are not allowed in virtual columns, or I'm not getting the syntax correct. I don't see window functions specified as a restriction in the documention:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_7002.htm#BABIIGBD
Here's my code:
SQL> create table stewart.test1 (test_id number, record_id number, sequence_id number);
Table created.
Elapsed: 00:00:00.03
SQL> ALTER TABLE stewart.test1 ADD (record_rank AS (DENSE_RANK() over ( PARTITION by test_id, record_id ORDER BY sequence_id DESC)));
ALTER TABLE stewart.test1 ADD (record_rank AS (DENSE_RANK() over ( PARTITION by test_id, record_id ORDER BY sequence_id DESC)))
*
ERROR at line 1:
ORA-30483: window functions are not allowed here
Elapsed: 00:00:00.01
SQL>
September 10, 2012 - 7:13 pm UTC
i believe this falls into the "non-deterministic" issue here. the addition of a row into the table could change the value for every other row in the table.
The problem with the analytic is that you cannot go to a row and figure out what the value is - you have to go to ALL rows for that test_id - virtual columns are limited to the data in that row and that row alone.
virtual column_expression rules?
Peter, October 23, 2012 - 6:25 pm UTC
The SQL Reference says the column_expression can be a simple expression. When I try to create one, it seem's I must apply some function to an existing column reference. Why will it not simply accept an existing physical column name, which is valid for a simple expression?
OK, why do I want to do this? As a friendly alias. Someone in their insane mind created a column name 3RD_PARTY_SW and I must double quote this in every reference. I renamed it to THIRD_PARTY_SW, but need to create an alias because it is referenced in a few reports. Creating a virtual column as (third_party_sw) gives an ORA-54016: Invalid column expression was specified. But (TRIM(third_party_sw)) works.
Thanks.
October 25, 2012 - 8:45 am UTC
I suppose the intention was not to use this as a renaming mechanism.
I might suggest the use of a view for this, rename the table - create a view named after the table.
ORA-54016 is ridiculous here, isn't it ?
Sokrates, October 25, 2012 - 10:22 am UTC
I suppose the intention was not to use this as a renaming mechanism. Anyone who understands this intention ?
Especially when it's so easy to workaround ?
And what's the logic behind
we implement a new feature but we don't want it to be used in that and that way ( for some reason we don't tell ) ?@Peter:
THIRD_PARTY_SW || ''
might probably perform a bit better than
trim( THIRD_PARTY_SW )
( haven't measured it )
October 25, 2012 - 11:31 am UTC
you are not working around it, you are putting an expression there, it is not the "same thing" anymore.
we implement a new feature but we don't want it to be used in that and that way ( for some reason we don't tell ) ?
it was the specification - you want to have a simple expression as a virtual column. that is all. I don't see what the deal is???
The goal, the sole goal - the intent - of a virtual column is to have a simple expression that looks, smells, and feels like a real column - so you can index it, constrain it, and so on.
there - there is one reason. You cannot have the same set of columns indexed (in 11gr2 and before). If you had selected the same column out twice - you would not be able to index both of them - but you should be able to since each column is to be a separate distinct thing.
got the point
Sokrates, October 25, 2012 - 2:03 pm UTC
unjustified ORA-54015 ?
Sokrates, October 25, 2012 - 2:25 pm UTC
sokrates@11.2 > create table t(c0 char(1));
Table created.
sokrates@11.2 > declare sqla clob;
2 begin
3 for i in 1..260 loop
4 sqla := 'alter table t add( c' || i || ' as (c0';
5 for j in 1..i loop
6 sqla := sqla || q'<||''>';
7 end loop;
8 sqla := sqla || '))';
9 begin execute immediate sqla; exception when others then dbms_output.put_line(sqla); raise; end;
10 end loop;
11 end;
12 /
alter table t add( c259 as
(c0||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||'')
)
declare sqla clob;
*
ERROR at line 1:
ORA-54015: Duplicate column expression was specified
ORA-06512: at line 9
How can there be a duplicate column expression ?
258 virtual columns successfully added, the 259th suddenly finds a duplicate, though each column expression has bigger length than the one before ?
October 28, 2012 - 11:06 pm UTC
here is a much faster running example:
ops$tkyte%ORA11GR2> declare sqla clob;
2 begin
3 for i in 258..260 loop
4 sqla := 'alter table t add( c' || i || ' as (c0';
5 for j in 1..i loop
6 sqla := sqla || q'<||''>';
7 end loop;
8 sqla := sqla || '))';
9 begin execute immediate sqla; exception when others then dbms_output.put_line(length(sqla) || '-'
10 || sqla); raise; end;
11 end loop;
12 end;
13 /
1072-alter table t add( c260 as
(c0||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''
||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||'
'||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||
''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''
||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||'
'||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||
''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''
||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||'
'||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||
''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''|
|''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''||''
||''||''||''||''))
declare sqla clob;
*
ERROR at line 1:
ORA-54015: Duplicate column expression was specified
ORA-06512: at line 10
do you have access to support to file the test case?
thanks for tuning !
Sokrates, October 29, 2012 - 5:51 am UTC
i filed this to support
virtual varchar2(1) is a char(1) ?
Sokrates, February 06, 2013 - 10:38 am UTC
I found a strange behaviour:
create and compile java source named J as
public class J
{
public static void describe(String view) throws java.sql.SQLException
{
java.sql.Connection conn = (new oracle.jdbc.OracleDriver()).defaultConnection();
java.sql.PreparedStatement ps = conn.prepareCall("select * from \"" + view + "\" where 1=0");
java.sql.ResultSet rs = ps.executeQuery();
java.sql.ResultSetMetaData md = rs.getMetaData();
for (int i = 1; i <= md.getColumnCount(); ++i) {
System.out.println(
md.getColumnName(i) + " = " +
md.getColumnTypeName(i) + "(" +
md.getPrecision(i) + ")"
);
}
rs.close();
ps.close();
}
}
/
create procedure descvj( view_name in varchar2 ) as language java name 'J.describe(java.lang.String)';
/
create table t(
nv varchar2(2),
v varchar2(1) AS (CASE nv WHEN 'A' THEN 'T' ELSE 'F' END)
);
create view v as select nv, v from t;
create view v2 as select nv, cast(v as varchar2(1)) as v from t;
Now, V and V2 have exactly the same structure, haven't they ?
sokrates@11.2 > desc v
Name Null? Type
----------------------------------------- -------- ----------------
NV VARCHAR2(2)
V VARCHAR2(1)
sokrates@11.2 > desc v2
Name Null? Type
----------------------------------------- -------- ----------------
NV VARCHAR2(2)
V VARCHAR2(1)
especially, column V is a VARCHAR2(1) in both.
However, descvj shows me a different output:
sokrates@11.2 > exec dbms_java.set_output(1e6)
PL/SQL procedure successfully completed.
sokrates@11.2 > exec descvj('V')
NV = VARCHAR2(2)
V = CHAR(1)
PL/SQL procedure successfully completed.
sokrates@11.2 > exec descvj('V2')
NV = VARCHAR2(2)
V = VARCHAR2(1)
PL/SQL procedure successfully completed.
for it, it is a CHAR(1) in view V
( although it is a VARCHAR2(1) in the underlying table t !)
sokrates@11.2 > desc t
Name Null? Type
----------------------------------------- -------- ---------------
NV VARCHAR2(2)
V VARCHAR2(1)
February 06, 2013 - 2:18 pm UTC
sure enough, it is a char(1)
ops$tkyte%ORA11GR2> create table t(
2 nv varchar2(2),
3 v varchar2(1) AS (CASE nv WHEN 'A' THEN 'T' ELSE 'F' END)
4 );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace view v as select nv, v from t;
View created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace view v2 as select nv, cast(v as varchar2(1)) as v from t;
View created.
ops$tkyte%ORA11GR2> insert into t (nv) values ( 'A' );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select dump(v) from v;
DUMP(V)
-------------------------------------------------------------------------------
Typ=96 Len=1: 84
ops$tkyte%ORA11GR2> exec p( 'select * from v' );
col_name = NV
col_type = 1
col_name = V
col_type = 96
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> select dump(v) from v2;
DUMP(V)
-------------------------------------------------------------------------------
Typ=1 Len=1: 84
ops$tkyte%ORA11GR2> exec p( 'select * from v2' );
col_name = NV
col_type = 1
col_name = V
col_type = 1
PL/SQL procedure successfully completed.
a char(1) and varchar2(1) are semantically, physically, everything - 100% the same. I'm not sure why it is doing that - it looks like a "bug" - wrong behaviour.
can you log with support? if not, let me know and I will.
ok
Sokrates, February 06, 2013 - 3:04 pm UTC
filed SR
"virtual varchar2(1) is described as char(1)" ( by dbms_sql.describe_columns and java.sql.ResultSetMetaData.getMetaData )
to support
Of course, no view is needed to reproduce - a single table with a virtual varchar2(1) is sufficent
February 07, 2013 - 6:20 am UTC
thanks!
Documentation on Deterministic
Galen Boyer, February 07, 2013 - 12:16 pm UTC
Hi Tom,
I was rereading this thread. The fellow at the top that said, "Could be a
warning or something" I think has a point which is revealed in the way he
answered you. He is somebody, probably alot like others, that was trying to
figure something out performance related, read about virtual functions and how
it might help out, reads some snippet that talks about marking it deterministic,
tries it and vhalla, it works, continue down the path of the project and do not
revisit the DETERMINISTIC definition again. Yes, he most definitely caused his
own issue, but IMO, it calls out the level of warning that just is not there
compared to the affect the mistake can have. The documentation says how to
determine if a function is deterministic, so, clearly the user should have known
not to "I need a deterministic function so just do it anyways". But, the
documentation does not SCREAM the huge bug this can cause, the fact that the
mistake ends up giving inconsistent answers? That is not anywhere in the same
snippet of documentation. Getting inconsistent answers anywhere in Oracle is
just a huge "Gasp, What is going On, Can we trust this, My world is over" kind
of issue. The docs don't really call it out that way. I would think a big
WARNING: Make sure you understand fully what deterministic means!!! It can
cause big big issues if the function is NOT DETERMINISTIC! Put that warning
before the description that the function needs to be marked deterministic. I
would go so far as to say maybe the PLSQL compiler should note that there are
actual database objects present in the body and warn the person, "This is
suspect. A deterministic function usually will not reference tables, views ..."
Its seems to be along the lines of your "I wish we never had a WHEN OTHERS".
You hate that so much and "SCREAM" it all over the place, that anybody reading
your site understands. But, the docs still have WHEN OTHERS dbms_output.xxx
There, the docs actually shoulder a reasonable amount of the blame for the WHEN
OTHERS scourge we find in code everywhere.
Anyways, just my opinion. The poster has no one to blame but himself. But,
Oracle documentation could be a quite a bit more loud and in-your-face of the
ramifications.
February 11, 2013 - 8:46 am UTC
The documentation says how to
determine if a function is deterministic, so, clearly the user should have
knownanyone programming a computer should know what deterministic is actually!
also, we do document (we do call it out) that it will result in inconsistent results:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_packages.htm#ADFNS386 <quote>
The database cannot recognize if the action of the function is indeed deterministic. If the DETERMINISTIC keyword is applied to a function whose action is not truly deterministic, then the result of queries involving that function is unpredictable.
</quote>But, the docs still have WHEN OTHERS dbms_output.xxx
There, the docs actually shoulder a reasonable amount of the blame for the WHEN
OTHERS scourge we find in code everywhere.I'm working on that... There is a lot of doc out there :)
Why does IamNotVirtual show as virtual ?
Sokrates, October 17, 2013 - 1:40 pm UTC
sokrates@11.2 > create table x ( IamNotVirtual xmltype );
Table created.
sokrates@11.2 > select virtual_column from user_tab_cols where table_name='X' and column_name='IAMNOTVIRTUAL';
VIR
---
YES
November 01, 2013 - 8:07 pm UTC
object types are like that.
there are really "many" columns to physically support an object type.
create type mytype ( a int, b date, c varchar2 );
create table t ( x mytype );
there is "virtually" one column there - but you know for a fact there are three really.
object types always lead to virtual columns.
About virtual columns and %rowtype
Sparc, January 07, 2014 - 10:19 am UTC
The recommendation of not use %ROWTYPE to insert values when the table has virtual columns has a issue when we need to use it for bulk inserts, like this pseudo-example:
TYPE tab_tc IS TABLE OF my_tab%ROWTYPE INDEX BY BINARY_INTEGER;
rows_in tab_tc;
...
FORALL indx IN rows_in.first .. rows_in.last
INSERT INTO my_tab
VALUES rows_in(indx);
Because you cannot refer to individual record fields within DML statements called by a FORALL statement we have a problem when add a virtual column to tables with this scenario.
There is any suggestion to address this approach?
Thanks
January 08, 2014 - 5:35 pm UTC
I am personally against using "insert into my_tab value RECORD_TYPE" period. It is dangerous.
With things like "invisible columns", "virtual columns" and the fact that someone can insert using a record with columns defined in a different order than the table appears to have them in (the insert is POSITIONAL, not by name) - it is just "dangerous".
Using a view, in my opinion, to define what you are inserting into - explicitly listing the columns YOU want, for THIS specific process, in the order YOU need/want them in - is the way to go.
create view v as select a, b, c from my_tab;
use V as the record type.
True, if you select from MY_TAB (which would include virtual columns) - you would need to map that structure into the new structure (assign the attributes attribute by attribute).
But think about all of the cases where virtual columns can/will come into play.
table T1 has a virtual column.
table T2 does not.
table T1 has columns a,b,c,virt
table T2 has columns l,m,n,o
you have a process that bulk collects some rows from T1
it then iterates over the selected data and sets virt(i) := f(virt(i))
it then bulk inserts into T2.
Now - what happens? if you treated virtual columns "special" in the record - then "virt" wouldn't be used in the insert. Or what if T2 has a virtual column - or two - M and N are virtual. what happens then? think about all of the cases - not just the simple "we read from table T, we modify in a procedural fashion each record, we insert into T again". That is just one simple case out of *lots* of possible cases.
Re:About virtual columns and %rowtype
A reader, January 08, 2014 - 9:05 am UTC
CTAS again
Sokrates, June 13, 2014 - 7:38 pm UTC
Bug 13357345 you mentioned above doesn't seem to be public ( it seems that I am not able to look at it ).
Any news here ?
Is it possible to use CTAS and the resulting table has some virtual columns ? It doesn't seem possible to me ( Version 11.2 ).
Is it possible in V12 ?
Thanks for having a look.
Exchange partition with Virtual column
Zee, June 19, 2015 - 5:25 pm UTC
In using partition exchange with virtual column, Oracle permits exchange when "source" table DOES NOT have virtual column and "target" table has. Oracle does not permit partition exchange other way round (see script below).
My guess for the reason is that data for virtual column is not available until it is explicitly selected.
Having said that I could not understand the rationale that exchange partition syntax wouldn't consider two tables physically the same (or atleast this be written in the code for exchange partition syntax)?
SQL> drop table t;
Table dropped
SQL> create table t(id number, dt date, dtid as (dt||'_'||id)) ;
Table created
SQL> drop table t_x;
Table dropped
SQL> create table t_x(id number, dt date, dtid varchar2(4000))partition by range(id)(partition p_default values less than (maxvalue));
Table created
SQL> alter table t_x exchange partition p_default with table t;
alter table t_x exchange partition p_default with table t
ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns
Internal Storage of Virtual Columns
Snehasish Das, October 08, 2015 - 7:43 am UTC
Hi Tom,
Good day.
I am a bit more interested to understand the internals of Virtual column and how they are stored (As metadata) in database.
I understand that for a virtual column we store the metadata only, is it stored in the table header per block or only once at segment level. Also when we do DML on the tables containing the virtual column is it always doing a lookup to the metadata information, and calculating the value in runtime which i guess is CPU bound. If yes, won't it be a good idea to compensate space to add the column and save on the cpu cycles.
Kindly help.
Regards,
Snehasish Das
October 08, 2015 - 8:28 am UTC
Its just stored in the dictionary, and the concept has actually been around "forever" (well going back to version 7 when function based indexes came in).
They are just "special" columns. For example:
SQL> create table T
2 ( x int,
3 y int);
Table created.
SQL> create index IX on T (x+0);
Index created.
SQL> alter table T set unused column Y;
Table altered.
SQL> select column_name, data_default
2 from USER_TAB_COLS
3 where table_name = 'T'
4 order by column_id;
COLUMN_NAME DATA_DEFAULT
------------------------------ ----------------
X
SYS_NC00003$ "X"+0
SYS_C00002_14020720:50:28$
Notice that function based indexes and "set column unused" effectively "keep" the column, but its just hidden away from you. Invisible columns (12c) are very similar, and virtual column similar again (although they are visible to you).
Hope this helps.
CPU Cycles
Snehasish Das, October 08, 2015 - 11:19 am UTC
Hi,
Thanks.
Could you please help me with the second part of the question, i.e on the cpu cycles. If we create Virtual columns we are using the CPU cycles to translate the values for DML operations. So If space is not a issue, is it a good idea to keep it as normal column and save the cpu cycles.
Looking forward to your answer.
Regards,
Snehasish
October 08, 2015 - 12:06 pm UTC
You will use CPU cycles in either case, ie, it takes CPU to navigate along a row and find the data for the column you have stored on disk.
So which is better ? Well ... it depends.
If your virtual column is (say) sqrt(ln(cos(...))) then you can be pretty confident you are going to burn a lot of CPU.
Also, its a question of query frequency - the expression will only be evaluated if its in the select list or predicates. If that is rare, then you get benefit from not evaluating it on insert, and save on column space (making other queries more efficient)
Hope this helps.
It Helps
Snehasish Das, October 08, 2015 - 1:42 pm UTC
Hi,
Thanks. Yeah it helps a lot.
Regards,
Snehasish Das.
Performance of virtual column on SELECT
Milind, October 17, 2015 - 12:10 pm UTC
"virtual columns are evaluated when they are selected - in the where clause, in the select list."
One of the use of calculated field is to improve performance of a query (With some drawbacks for DML). If virtual columns are evaluated when they are selected, will it not have effect on query performance?
Thanks in advance!
October 18, 2015 - 2:32 am UTC
Yes, but as I said - you use CPU cycles just in the processing of row anyway - its not free, ie, it takes CPU to navigate along a row and find the data for the column you have stored on disk.
So (*depending* on the calculation being performed) a virtual column might not be such an overhead as many people would think.
For example, if the calculation yielded a large string, then storing as a real column make might queries *worse* (due to space increase).
There is no "rule" which defines virutal columns being better or worse, the only rule is - does it *benefit* your application
IM Virual columns in Oracle 18c
Rajeshwaran, Jeyabal, March 18, 2019 - 11:56 am UTC
Team,
Was reading this link about "Enabling a specific IM virtual column for a IM Column store" at this below link.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/populating-objects-in-memory.html#GUID-E75BEB47-6242-418B-9DE8-8AAC8910A2FA even after explicitly defining the INMemory option for Virtual columns, don't see that is show in v$im_col_cu dynamic dictionary tables. kindly advice.
demo@PDB1> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
demo@PDB1> show parameter inmemory_virtual_columns
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_virtual_columns string MANUAL
demo@PDB1> create table t (
2 owner varchar2(30),
3 object_type varchar2(80),
4 object_id number,
5 object_details generated always as ( owner||'-'||object_type) );
Table created.
demo@PDB1> insert into t (owner,object_type,object_id)
2 select owner,object_type,object_id
3 from all_objects;
68398 rows created.
demo@PDB1> alter table t inmemory;
Table altered.
demo@PDB1> select count(*) from t;
COUNT(*)
----------
68398
demo@PDB1> col owner format a10
demo@PDB1> col segment_name format a10
demo@PDB1> select owner,segment_name,bytes_not_populated,populate_status
2 from v$im_segments;
OWNER SEGMENT_NA BYTES_NOT_POPULATED POPULATE_STAT
---------- ---------- ------------------- -------------
DEMO T 0 COMPLETED
demo@PDB1> col column_name format a20
demo@PDB1> select column_name, column_id
2 from user_tab_cols
3 where table_name ='T'
4 order by column_id;
COLUMN_NAME COLUMN_ID
-------------------- ----------
OWNER 1
OBJECT_TYPE 2
OBJECT_ID 3
OBJECT_DETAILS 4
demo@PDB1> select column_number from v$im_col_cu;
COLUMN_NUMBER
-------------
1
2
3
demo@PDB1> alter table t inmemory( object_details );
Table altered.
demo@PDB1> select count(*) from t;
COUNT(*)
----------
68398
demo@PDB1> select owner,segment_name,bytes_not_populated,populate_status
2 from v$im_segments;
OWNER SEGMENT_NA BYTES_NOT_POPULATED POPULATE_STAT
---------- ---------- ------------------- -------------
DEMO T 0 COMPLETED
demo@PDB1> select column_number from v$im_col_cu;
COLUMN_NUMBER
-------------
1
2
3
demo@PDB1>
March 20, 2019 - 3:56 am UTC
I am checking with the Product Manager, but my understanding is that you need force a re-populate of the column store. So you would need to do:
alter table t NO inmemory;
before then nominating the virtual column to be added to the column store
alter table t inmemory;
alter table t inmemory( object_details );
IM Virual columns in Oracle 18c
Rajeshwaran, Jeyabal, March 21, 2019 - 9:41 am UTC
Thanks for the details.
Even after setting the NO INMEMORY and then enabling inmemory at table and virtual column level and then repopulating the IMCU doesn't bring virtual columns into In-memory.
Kindly advice, the below demo was from 18c database.
demo@PDB1> show parameter inmemory_virtual_columns
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_virtual_columns string MANUAL
demo@PDB1> create table t(
2 object_id number ,
3 owner varchar2(30),
4 object_name varchar2(180),
5 object_type varchar2(30) ,
6 object_details generated always as ( owner ||'-'|| object_name )
7 );
Table created.
demo@PDB1> insert into t(object_id,object_name,object_type,owner)
2 select object_id,object_name,object_type,owner
3 from all_objects ;
68399 rows created.
demo@PDB1> alter table t inmemory;
Table altered.
demo@PDB1> select count(*) from t;
COUNT(*)
----------
68399
demo@PDB1> col segment_name format a10
demo@PDB1> col column_name format a20
demo@PDB1> select segment_name,populate_status from v$im_segments;
SEGMENT_NA POPULATE_STAT
---------- -------------
T COMPLETED
demo@PDB1> select segment_column_id, column_name, inmemory_compression
2 from v$im_column_level;
SEGMENT_COLUMN_ID COLUMN_NAME INMEMORY_COMPRESSION
----------------- -------------------- --------------------------
0 OBJECT_DETAILS UNSPECIFIED
1 OBJECT_ID DEFAULT
2 OWNER DEFAULT
3 OBJECT_NAME DEFAULT
4 OBJECT_TYPE DEFAULT
demo@PDB1> select column_number from v$im_col_cu;
COLUMN_NUMBER
-------------
1
2
3
4
demo@PDB1> alter table t no inmemory;
Table altered.
demo@PDB1> select segment_name,populate_status from v$im_segments;
no rows selected
demo@PDB1> alter table t inmemory;
Table altered.
demo@PDB1> alter table t inmemory( object_details );
Table altered.
demo@PDB1> select count(*) from t;
COUNT(*)
----------
68399
demo@PDB1> select segment_name,populate_status from v$im_segments;
SEGMENT_NA POPULATE_STAT
---------- -------------
T COMPLETED
demo@PDB1> select segment_column_id, column_name, inmemory_compression
2 from v$im_column_level;
SEGMENT_COLUMN_ID COLUMN_NAME INMEMORY_COMPRESSION
----------------- -------------------- --------------------------
0 OBJECT_DETAILS DEFAULT
1 OBJECT_ID DEFAULT
2 OWNER DEFAULT
3 OBJECT_NAME DEFAULT
4 OBJECT_TYPE DEFAULT
demo@PDB1> select column_number from v$im_col_cu;
COLUMN_NUMBER
-------------
1
2
3
4
demo@PDB1>
April 04, 2019 - 4:22 am UTC
You need to do "no inmemory" AFTER
alter table t inmemory( object_details );
and then "inmemory" to bring it in
IM Virual columns in Oracle 18c
Rajeshwaran Jeyabal, April 05, 2019 - 2:31 pm UTC
Team,
As suggested did "no inmemory" after "alter table t inmemory( object_details );" - but still no see the Virtual columns at inmemory.
Kindly advice.
demo@PDB1>
demo@PDB1> create table t
2 as
3 select object_id,
4 owner,object_name,
5 object_type
6 from all_objects ;
Table created.
demo@PDB1> alter table t add object_details generated always as ( owner||'-'|| object_name ) ;
Table altered.
demo@PDB1> alter table t inmemory;
Table altered.
demo@PDB1> select count(*) from t;
COUNT(*)
----------
68399
demo@PDB1> select segment_name, populate_status from v$im_segments;
SEGMENT_NAME POPULATE_STAT
------------------------------ -------------
T COMPLETED
demo@PDB1> select segment_column_id,column_name from v$im_column_level;
SEGMENT_COLUMN_ID COLUMN_NAME
----------------- ---------------
0 OBJECT_DETAILS
1 OBJECT_ID
2 OWNER
3 OBJECT_NAME
4 OBJECT_TYPE
demo@PDB1> select column_number from v$im_col_cu;
COLUMN_NUMBER
-------------
1
2
3
4
demo@PDB1> alter table t inmemory( object_details );
Table altered.
demo@PDB1> alter table t no inmemory;
Table altered.
demo@PDB1> select segment_name, populate_status from v$im_segments;
no rows selected
demo@PDB1> alter table t inmemory;
Table altered.
demo@PDB1> select count(*) from t;
COUNT(*)
----------
68399
demo@PDB1> select segment_name, populate_status from v$im_segments;
SEGMENT_NAME POPULATE_STAT
------------------------------ -------------
T COMPLETED
demo@PDB1> select segment_column_id,column_name from v$im_column_level;
SEGMENT_COLUMN_ID COLUMN_NAME
----------------- ---------------
0 OBJECT_DETAILS
1 OBJECT_ID
2 OWNER
3 OBJECT_NAME
4 OBJECT_TYPE
demo@PDB1> select column_number from v$im_col_cu;
COLUMN_NUMBER
-------------
1
2
3
4
demo@PDB1>
April 09, 2019 - 1:48 am UTC
Virtual columns are not column, they are expressions, so you'd see them in v$imeu_header
eg
SQL> create table t (
2 owner varchar2(30),
3 object_type varchar2(80),
4 object_id number,
5 object_details generated always as ( sqrt(sqrt(sqrt(sqrt(sqrt(sqrt(object_id))))))));
Table created.
SQL>
SQL> insert /*+ APPEND */ into t (owner,object_type,object_id)
2 select owner,object_type,object_id
3 from dba_objects, ( select 1 from dual connect by level <= 20 );
1643320 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> set timing on
SQL> select max(object_details) from t;
MAX(OBJECT_DETAILS)
-------------------
1.19986259
1 row selected.
--
-- no inmem, so slow
--
Elapsed: 00:00:22.36
SQL>
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5qjuzfj6fusv3, child number 0
-------------------------------------
select max(object_details) from t
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1551 (100)| |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
| 2 | TABLE ACCESS FULL| T | 1643K| 34M| 1551 (1)| 00:00:01 |
---------------------------------------------------------------------------
14 rows selected.
Elapsed: 00:00:00.08
SQL> select * from v$im_segments;
no rows selected
Elapsed: 00:00:00.00
SQL> alter table t inmemory;
Table altered.
Elapsed: 00:00:00.00
SQL> select count(*) from t;
COUNT(*)
----------
1643320
1 row selected.
Elapsed: 00:00:00.01
SQL> col owner format a10
SQL> col segment_name format a10
SQL> select owner,segment_name,bytes,bytes_not_populated,populate_status from v$im_segments;
no rows selected
Elapsed: 00:00:00.00
SQL> select owner,segment_name,bytes,bytes_not_populated,populate_status from v$im_segments;
OWNER SEGMENT_NA BYTES BYTES_NOT_POPULATED POPULATE_STAT
---------- ---------- ---------- ------------------- -------------
MCDONAC T 45637632 0 COMPLETED
1 row selected.
Elapsed: 00:00:00.00
SQL> col table_name format a15;
SQL> col column_name format a30;
SQL> select table_name, column_name, INMEMORY_COMPRESSION from v$im_column_level
2 where table_name = 'T';
TABLE_NAME COLUMN_NAME INMEMORY_COMPRESSION
--------------- ------------------------------ --------------------------
T OBJECT_DETAILS UNSPECIFIED
T OWNER DEFAULT
T OBJECT_TYPE DEFAULT
T OBJECT_ID DEFAULT
4 rows selected.
Elapsed: 00:00:00.00
SQL> alter table t inmemory(object_details);
Table altered.
Elapsed: 00:00:00.00
SQL> select owner,segment_name,bytes,bytes_not_populated,populate_status from v$im_segments;
OWNER SEGMENT_NA BYTES BYTES_NOT_POPULATED POPULATE_STAT
---------- ---------- ---------- ------------------- -------------
MCDONAC T 45637632 0 COMPLETED
1 row selected.
Elapsed: 00:00:00.00
SQL> select table_name, column_name, INMEMORY_COMPRESSION from v$im_column_level where table_name = 'T';
TABLE_NAME COLUMN_NAME INMEMORY_COMPRESSION
--------------- ------------------------------ --------------------------
T OBJECT_DETAILS DEFAULT
T OWNER DEFAULT
T OBJECT_TYPE DEFAULT
T OBJECT_ID DEFAULT
4 rows selected.
Elapsed: 00:00:00.00
--
-- no expressions are inmem
--
SQL> select * from v$imeu_header;
no rows selected
Elapsed: 00:00:00.00
--
-- so our query is still slow
--
SQL> select max(object_details) from t;
MAX(OBJECT_DETAILS)
-------------------
1.19986259
1 row selected.
Elapsed: 00:00:22.00
SQL> select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 5qjuzfj6fusv3, child number 0
-------------------------------------
select max(object_details) from t
Plan hash value: 2966233522
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 73 (100)| |
| 1 | SORT AGGREGATE | | 1 | 22 | | |
| 2 | TABLE ACCESS INMEMORY FULL| T | 1643K| 34M| 73 (20)| 00:00:01 |
------------------------------------------------------------------------------------
14 rows selected.
Elapsed: 00:00:00.03
SQL> set timing off
SQL> alter table t no inmemory;
Table altered.
SQL> alter table t inmemory;
Table altered.
SQL> alter table t inmemory(object_details);
Table altered.
SQL> select count(*) from t;
COUNT(*)
----------
1643320
1 row selected.
SQL> col owner format a10
SQL> col segment_name format a10
SQL> select owner,segment_name,bytes,bytes_not_populated,populate_status from v$im_segments;
no rows selected
SQL> /
OWNER SEGMENT_NA BYTES BYTES_NOT_POPULATED POPULATE_STAT
---------- ---------- ---------- ------------------- -------------
MCDONAC T 45637632 0 COMPLETED
1 row selected.
SQL> select count(*) from v$imeu_header;
COUNT(*)
----------
4
1 row selected.
SQL> set timing on
--
-- expressions are now inmem, so our query is quick
--
SQL> select max(object_details) from t;
MAX(OBJECT_DETAILS)
-------------------
1.19986259
1 row selected.
Elapsed: 00:00:00.00
SQL>
Broken link
Gabriel, August 14, 2019 - 4:10 pm UTC
The first link is not available
August 15, 2019 - 2:15 am UTC
THanks - I've updated the question with the new link
ora 2262 while giving default value
Apr, August 07, 2020 - 4:00 am UTC
Hi,
We have a table which is list partitioned by a number type column(Day_Year) . This number column derives its values from another column called updateddt (of date datatype) in the same table. While giving default value to the number type column via
ALTER TABLE test
MODIFY(Day_Year DEFAULT TO_CHAR(updateddt, 'YYYY')||TO_CHAR(updateddt, 'DDD') );
we are getting error ora 2262 ora 904 occurs while type checking column default value expression. What could be the issue ?
We are in Oracle 12c - is this not possible in oracle 12 c ?
August 07, 2020 - 8:22 am UTC
You can't use the values from one column to set the default for another.
You want to make this a virtual column:
create table t (
c1 int, updateddt date
);
alter table t
add (
day_year integer as
( to_number ( to_char(updateddt, 'yyyy')||to_char(updateddt, 'ddd') ) )
);