virtual columns
February 21, 2008 - 11am Central time zone
Reviewer: Anto
How is it different from computed colum of SQL Server ?
Anto
Followup February 21, 2008 - 12pm Central time zone:
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
February 25, 2008 - 2am Central time zone
Reviewer: AMIR RIAZ
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
Followup February 25, 2008 - 8am Central time zone:
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
February 25, 2008 - 10am Central time zone
Reviewer: Anto
storage
February 26, 2008 - 5pm Central time zone
Reviewer: A reader
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?
May 19, 2009 - 8am Central time zone
Reviewer: Dusan from Czech republic
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
Followup May 23, 2009 - 12pm Central time zone:
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 ..
May 27, 2009 - 4am Central time zone
Reviewer: A reader
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
Followup May 27, 2009 - 11am Central time zone:
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://download.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://download.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
June 1, 2009 - 5am Central time zone
Reviewer: Dusan from Czech Rep.
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
March 20, 2010 - 7am Central time zone
Reviewer: eshwar from India
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
Followup March 20, 2010 - 9am Central time zone:
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
March 20, 2010 - 11am Central time zone
Reviewer: A reader
"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?
Followup March 22, 2010 - 8am Central time zone:
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?
October 13, 2010 - 12pm Central time zone
Reviewer: Mukund Nampally from Dallas,USA
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?
USER is an Oracle Function
October 26, 2010 - 11am Central time zone
Reviewer: Ibrahim Bakhsh from KSA
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
Followup October 26, 2010 - 8pm Central time zone:
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
October 26, 2010 - 11pm Central time zone
Reviewer: Ibrahim Bakhsh from KSA
<!--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 ?
Followup October 27, 2010 - 5am Central time zone:
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
October 27, 2010 - 6am Central time zone
Reviewer: Ibrahim Bakhsh
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 ?
Followup October 27, 2010 - 6am Central time zone:
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
October 27, 2010 - 10pm Central time zone
Reviewer: Ibrahim Bakhsh
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;
/
Followup October 28, 2010 - 11am Central time zone:
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
October 28, 2010 - 11pm Central time zone
Reviewer: Ibrahim Bakhsh
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 ?
Followup November 1, 2010 - 5am Central time zone:
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
October 31, 2010 - 5am Central time zone
Reviewer: al0
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
Followup November 1, 2010 - 2pm Central time zone:
well said sir
Fasting Results of Query By Adding Vertual Columns
October 31, 2010 - 8am Central time zone
Reviewer: Ibrahim Bakhsh
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
November 1, 2010 - 6am Central time zone
Reviewer: Oleksandr Alesinskyy
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?
December 29, 2010 - 10am Central time zone
Reviewer: A reader
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
Followup December 30, 2010 - 1pm Central time zone:
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
December 31, 2010 - 8am Central time zone
Reviewer: A reader
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
December 31, 2010 - 8am Central time zone
Reviewer: A reader
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
Followup January 3, 2011 - 8am Central time zone:
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
January 2, 2011 - 12pm Central time zone
Reviewer: Sokrates
Is the answer
You cannot expect existing Code is valid when using a New Feature
Can I?
August 5, 2011 - 5pm Central time zone
Reviewer: A reader
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,
Followup August 6, 2011 - 8am Central time zone:
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.
August 6, 2011 - 8am Central time zone
Reviewer: A reader
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,
August 6, 2011 - 8am Central time zone
Reviewer: A reader
...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,
August 6, 2011 - 9am Central time zone
Reviewer: A reader
..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,
Followup August 6, 2011 - 2pm Central time zone:
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
August 14, 2011 - 11am Central time zone
Reviewer: Kannan from USA
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.
Followup August 14, 2011 - 8pm Central time zone:
#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 )
August 15, 2011 - 7pm Central time zone
Reviewer: Kannan from USA
Thanks Tom. I really appreciate your inputs.
Clarification on virtual column expression
August 15, 2011 - 7pm Central time zone
Reviewer: Kannan from USA
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.
Followup August 16, 2011 - 2am Central time zone:
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
August 19, 2011 - 7pm Central time zone
Reviewer: Kannan from USA
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 .
Followup August 30, 2011 - 1pm Central time zone:
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
August 29, 2011 - 9am Central time zone
Reviewer: Kannan from USA
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
November 7, 2011 - 5pm Central time zone
Reviewer: Martin Rose from Brussels
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?
Followup November 7, 2011 - 7pm Central time zone:
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
January 5, 2012 - 4pm Central time zone
Reviewer: Anand from STL, US
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
**/
Followup January 5, 2012 - 4pm Central time zone:
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
January 5, 2012 - 4pm Central time zone
Reviewer: Anand from STL, US
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
April 16, 2012 - 11am Central time zone
Reviewer: Soumadip from UK
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?
Followup April 16, 2012 - 4pm Central time zone:
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 ...
April 29, 2012 - 11am Central time zone
Reviewer: Parthiban Nagarajan from Coimbatore, India
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
Followup April 30, 2012 - 8am Central time zone:
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.
|