CODEWITHSUNDEEP
sqloracle-databaseoracle11gr2
Suresh Gautam
Suresh Gautam

Reputation: 893

Persistent computed Oracle Virtual Column

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:

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

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

Upvotes: 0

Views: 1751

Answers (2)

Pavan
Pavan

Reputation: 11

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

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21073

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

Upvotes: 0

Related Questions