CODEWITHSUNDEEP
oracle-database
John Doe
John Doe

Reputation: 3243

Is there any major performance issues if I use virtual columns in Oracle?

Is there any major performance issues if I use virtual columns in an Oracle table?

We have a scenario where the db has fields stored as strings. Since other production apps run off those fields we can't easily convert them.

I am tasked with generating reports from the same db. Since I need to be able to filter by dates (which are stored as strings) it was brought to my attention that we could create a virtual date field so that I can query against that.

Has anyone ran into any roadblocks with this approach?

Upvotes: 1

Views: 3381

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132620

A virtual column is defined using an expression that is evaluated when you select from the table. There is no performance hit on inserts/updates on the table.

For example:

create table t1 (
    datestr varchar2(100),
    datedt date generated always as (to_date(datestr,'YYYYMMDD'))
    );

Table created.
SQL> insert into t1 (datestr) values ('20160815');

1 row created.

SQL> insert into t1 (datestr) values ('xxx');

1 row created.

SQL> commit;

Commit complete.

Note that I was able to insert an invalid date value into datestr. Now we can try to select the data:

SQL> select * from t1 where datedt = date '2016-08-15';
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

This could be a problem for you if you can't guarantee all the strings hold valid dates.

As for performance, when you run the above query what you are really running is:

select * from t1 where to_date(datestr,'YYYYMMDD') = date '2016-08-15';

So the query will not be able to use an index on the datestr column (probably), and you may want to add an index on the virtual column. Again, this won't work if any of the strings don't contain valid dates.

Another consideration is potential impact on existing code. Hopefully you won't have any code like insert into t1 values (...); i.e. not specifying the column list. If you do you will get the error:

ORA-54013: INSERT operation disallowed on virtual columns

Upvotes: 3

Related Questions