We have a table with 100 columns and number of records are around 1.3 million in it. Also having around 40 indexes created on the table.

Subset of the table is as below

create table t (
username varchar2(100),
DOJ date,
recid varchar2(100),
--uniqueUserName varchar2(200),
 CONSTRAINT pkt primary key  (recid)
);


insert into t(UserName,DOJ,RecID) values('abc',sysdate,'asdfghjklzxcbnm');
insert into t(UserName,DOJ,RecID) values('xyz',sysdate,'asdfghjklzx2bnm');
insert into t(UserName,DOJ,RecID) values('abc',sysdate,'asdfghjklzxcb3m');
insert into t(UserName,DOJ,RecID) values('abc',sysdate,'asdfghjklzxcbn4');

select * from t;

We have requirement, there should be unique column which should be based on UserName column of the table. if userName is unique then we in this unique column should be same as userName, but if its not unique then it can be appended with _add<running number> to make it unique. This is done using below SQL

create or replace view v_t as
SELECT t.* ,
  CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY username ORDER BY DOJ DESC)=1
    THEN username
    WHEN ROW_NUMBER() OVER (PARTITION BY username ORDER BY DOJ DESC)>1
    THEN username
      ||'_add'
      ||to_char(ROW_NUMBER() OVER (PARTITION BY username ORDER BY DOJ DESC)-1)
  END AS uniqueUserName 
from t;

select * from v_t;

This View is quit critical for our application,as its heavily used and in most of the SQL it is joined with other table is based on UniqueUserName.
As uniqueUserName is derived using formula also base tabel is quite big all of the queries are running quit slow, wherever we are using this view and joining on UniqueUserName

As a alternate we converted view into materialized view, using same sql. also created all the indexes.
This has improved the performance of all the SQL, but refresh of the view data is a problem as we are using analytical function it can not be refreshed fast, it should be always complete refresh. This full refresh is taking long, almost 30 min. Also when MV refresh happens, all associated queries start performing slow. Also data in the MV is not real time.
As a alternate should we write trigger which will be populating the uniqueUserName column, which we can add in base table i.e. t.

Please recommend what’s the best solution for this scenario. which can be developed easily and easy to maintain.

create table t (
  username         varchar2(100),
  doj              date,
  recid            varchar2(100),
  uniqueusername   varchar2(200) as ( 
    username || '_' || to_char(doj,'yyyymmddhh24miss') 
  ),
  constraint pkt primary key ( recid ),
  unique ( uniqueusername )
);

insert into t(UserName,DOJ,RecID) values('abc',sysdate,'asdfghjklzxcbnm');
insert into t(UserName,DOJ,RecID) values('xyz',sysdate,'asdfghjklzx2bnm');
insert into t(UserName,DOJ,RecID) values('abc',sysdate,'asdfghjklzxcb3m');
insert into t(UserName,DOJ,RecID) values('abc',sysdate,'asdfghjklzxcbn4');

ORA-00001: unique constraint (CHRIS.SYS_C0017328) violated

select * from t;

USERNAME  DOJ                   RECID            UNIQUEUSERNAME      
abc       24-JUL-2017 02:55:40  asdfghjklzxcbnm  abc_20170724025540  
xyz       24-JUL-2017 02:55:41  asdfghjklzx2bnm  xyz_20170724025541  
abc       24-JUL-2017 02:55:41  asdfghjklzxcb3m  abc_20170724025541