I am currently in the middle of a Cognos BI implementation and am having difficulty in tuning a view in order for SQL Server to
select its contents for fulfilling the incremental load in a timely fashion. The data is currently taking +5 hours to move between Oracle and SQL Server (Cognos 'Staging' layer).
The source code for this scenario can be accessed via the following URL:
I would like to create a materialized view that will use
select * from ifsapp.fb_cust_ord_lines as its source, this will need to refresh fast (i.e. quickly) every day at 2:00. I have attempted this and below is the result:
create materialized view ifsapp.fb_bi_cust_ord_lines_mv tablespace "IFSAPP_DATA" build immediate using index pctfree 10 initrans 2 maxtrans 255 storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default) tablespace "IFSAPP_DATA" refresh fast using default local rollback segment as select * from ifsapp.fb_cust_ord_lines ; create index ifsapp.fb_fb_bi_cust_ord_lines_mv_i on ifsapp.fb_bi_cust_ord_lines_mv ( order_no, line_no, rel_no, line_item_no ) pctfree 10 initrans 2 maxtrans 255 tablespace IFSAPP_INDEX_16K ;
I know that this will not be 100% correct (maybe not even 50%) but I would really appreciate some assistance. Please ask if you require any further information