Persistent computed Oracle Virtual Column

Question

For performance gain, I am looking to make persistence derived column by combination of few fields from same column.

I have explored about automated and virtual column, but didn't give proper solution to me. Found that Oracle Virtual column is similar to the the derived column with the combination/computed of fields in a general view with some additional features, it executes virtual column expression during query execution.

Besides general view, there is option to use Materialized View which will create separate Materialized view segment and separate execution overhead too.

I am trying to figure out to maintain read-only virtual column which will be computed up on inserting/updating fields automatically when commit happens. Can anyone help for this solution in Oracle 11gR2?

--Example:

create table table1(id int, field1 varchar2(30),field2 varchar2(30),field3 varchar2(30),field4 varchar2(30),field5 varchar2(200));
....Direct path load will happen into id, field1, field2, field3 and field4 by ETL process...
update table1 set field5=field1 || '#' || field2 || '#' || field3 || '#' || field4;
commit;

In above example, I wish to populate field5 internally automatically once we perform #1, #2 and #4 without adding major execution time.

For such a simple expression, I don't see any benefit in actually storing this value. Just go with a virtual/computed column and you should be fine. — user330315
– user330315, Commented Jan 15, 2015 at 8:13
Did you actually test the performance impact? It don't think you will notice it. — Patrick Hofman
– Patrick Hofman, Commented Jan 15, 2015 at 8:17
Above update statement takes longer time when you have populated massive volume of data during step#2. I just wish to avoid update execution here. — Suresh Gautam
– Suresh Gautam, Commented Jan 15, 2015 at 9:53
Again: why do you think you need a persisted column? If you just create a regular computed column you do avoid the update execution. Do you have a performance problem when retrieving using a computed column? — user330315
– user330315, Commented Jan 15, 2015 at 10:17
Reasons: 1. Following SQL scripts need to refer concatenated field. 2. Looking to avoid update execution 3. Bypass logging — Suresh Gautam
– Suresh Gautam, Commented Jan 15, 2015 at 10:23

Marmite Bomber · Accepted Answer · 2015-10-06 07:44:42Z

1

We can resolve the above problem by using the below query

 create table table1
 (id int, 
  field1 varchar2(30),
  field2 varchar2(30),
  field3 varchar2(30),
  field4 varchar2(30),
  field5 varchar2(200) GENERATED ALWAYS AS (field1 || '#' || field2 || '#' || field3 || '#' || field4));

edited Oct 6, 2015 at 7:44

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

answered Oct 6, 2015 at 7:11

Pavan

112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marmite Bomber · Accepted Answer · 2015-10-06 07:30:48Z

0

Please check carefully why you need the additional column.

I can speculate, that this is to optimize access using a predicates such as

WHERE field5 like '%#<search key>#%'

to avoid a complicated predicates with lot of ORs

If this is the reason, you may profit from additional table insted of additional concatenated column

create table HLPR
(id number,  /* FK to your table */
 field_no NUMBER,
 field_value  varchar2(30));

The same predicate as above you may formulate now with

 WHERE field_value = '<search key>'

i.e. you have all possibilities of FULL TABLE SCAN or index access to get a performant query.

Sorry if my speculation goes in a wrong direction, only my 0.02c

answered Oct 6, 2015 at 7:30

Marmite Bomber

21.3k4 gold badges31 silver badges59 bronze badges

Collectives™ on Stack Overflow

Persistent computed Oracle Virtual Column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related