How is it different from computed colum of SQL Server ?

Anto

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.

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

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.

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

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  /

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

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

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.

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) );

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

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

"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?

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?

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

<!--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 ?

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 ?

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;
/

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 ?

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;

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

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 !?

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 ... .

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

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

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

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

Is the answer

You cannot expect existing Code is valid when using a New Feature

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,

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,

...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,

..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,

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.

Thanks Tom. I really appreciate your inputs.

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.

ops$tkyte%ORA11GR2> select coalesce( 1, 1/0 ) from dual;

COALESCE(1,1/0)
---------------
              1

ops$tkyte%ORA11GR2> 

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 .

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

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?

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

**/

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

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..)

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?

Hi Tom

Following is a link from Eddie Awad's blog

http://joxeankoret.com/blog/2010/05/14/dangers-of-oracle-virtual-columns/

May I have your comments on this, please ...

Thanks and regards

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 ..)

ops$tkyte%ORA11GR2> create table test(
  2     emp_id NUMBER,
  3     emp_name VARCHAR2(100),
  4     employee_name AS (emp_name||''));

Table created.

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> 

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.

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 )

Thanks, got the point.

So, Peter from Germantown, MD should use a view instead.

I found some issues with views with duplicated columns and posted a followup to that on
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:636499119911#5598780500346374575

(don't want to become offtopic here)

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 ?

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

i filed this to support

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)

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.

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

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.

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

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

To correct my previous post, in fact (in 11g) is possible to refer individual record fields within FORALL like this :

 FORALL indx IN rows_in.first .. rows_in.last 
       INSERT INTO my_tab(
           field_1, 
           field_2)
         VALUES (
           rows_in(indx).field_1,
           rows_in(indx).field_2);

My confusion was made because this restriction in the 10g was removed in 11g:

10g: http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/forall_statement.htm#i34324

11g:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/forall_statement.htm#LNPLS01321

My fault.

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.

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

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

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$

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

Hi,

Thanks. Yeah it helps a lot.

Regards,
Snehasish Das.

"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!

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>

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>

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>

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>

The first link is not available

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 ?

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') ) )
  );