Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

https://github.com/pwl91/source_code

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

share|improve this question
    
A materialized view that is just a select * over one table is no different from just applying the index to the table directly. For this to be any benefit you either need to be joining tables, calculating aggregates (sum, count, etc.) or applying a where clause to your table. –  Chris Saxon Jun 19 '13 at 20:00
    
Separating tables and indexes into different tablespaces, I see? –  David Aldridge Jun 19 '13 at 23:09
    
@ChrisSaxon I believe that's supposed to be a view. –  David Aldridge Jun 19 '13 at 23:16
1  
@pwl What exactly is your problem? –  David Aldridge Jun 19 '13 at 23:17
    
I would like to create a materialized view that will use select * from ifsapp.fb_cust_ord_lines as its source Your code references fb_customer_order_lines, not fb_cust_ord_lines. You have objects with confusingly similar names. –  David Aldridge Jun 19 '13 at 23:17
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.