Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

performance of computed column in a view

961848May 3 2016 — edited May 3 2016

Hi,

   I have a view which selects all records from a table and adds an extra computed column into it. For the computed column, I am using a user-defined function. The table has over millions of records and so does the view.

CREATE OR REPLACE VIEW XX_v AS

   SELECT a.col1,

          a.col2,

    TO_NUMBER(my_func(TO_CHAR(col1,'YYYYMMDDHH24MI'))) col3

     FROM XX  a;

I have a process that is suppose to select only changed data every hour from the view and the view is used primarily for this process.

insert into YY as select * from XX_v a where a.col1 >t1 and a.col1 <= t1+1/24;

10000 rows inserted

My question is will the computed column value be computed for all the million rows every time the insert command is run or only for the subset of the view that is actually selected?  Please advise.

Thanks

This post has been answered by BrunoVroman on May 3 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 31 2016
Added on May 3 2016
8 comments
1,349 views