title: Improve view performance
body: <p>I need to improve my view performance, right now the SQL that makes the view is:</p>
  
  <pre><code>select tr.account_number , tr.actual_collection_trx_date ,s.customer_key
  from   fct_collections_trx tr,
         stg_scd_customers_key s
  where  tr.account_number = s.account_number
  and    trunc(tr.actual_collection_trx_date) between s.start_date and s.end_date;
  </code></pre>
  
  <p>Table fct_collections_trx  has 170k+-(changes every day) records.</p>
  
  <p>Table stg_scd_customers_key has 430mil records.</p>
  
  <p>Table fct_collections_trx  have indexes as following:
    (SINGLE INDEX OF ALL OF THEM) (ACCOUNT_NUMBER, SUB_ACCOUNT_NUMBER, ACTUAL_COLLECTION_TRX_DATE, COLLECTION_TRX_DATE, COLLECTION_ACTION_CODE)(UNIQUE) and ENTRY_SCHEMA_DATE(NORMAL). DDL:</p>
  
  <pre><code>alter table stg_admin.FCT_COLLECTIONS_TRX
  add primary key (ACCOUNT_NUMBER, SUB_ACCOUNT_NUMBER, ACTUAL_COLLECTION_TRX_DATE, COLLECTION_TRX_DATE, COLLECTION_ACTION_CODE)
    using index 
    tablespace STG_COLLECTION_DATA
    pctfree 10
    initrans 2
    maxtrans 255
    storage
     (
      initial 80K
      next 1M
      minextents 1
      maxextents unlimited
    );
  </code></pre>
  
  <p>Table structure:</p>
  
  <pre><code>create table stg_admin.FCT_COLLECTIONS_TRX
  (
    account_number              NUMBER(10) not null,
    sub_account_number          NUMBER(5) not null,
    actual_collection_trx_date  DATE not null,
    customer_key                NUMBER(10),
    sub_account_key             NUMBER(10),
    schema_key                  VARCHAR2(10) not null,
    collection_group_code       CHAR(3),
    collection_action_code      CHAR(3) not null,
    action_order                NUMBER,
    bucket                      NUMBER(5),
    collection_trx_date         DATE not null,
    days_into_cycle             NUMBER(5),
    logical_delete_date         DATE,
    balance                     NUMBER(10,2),
    abbrev                      CHAR(8),
    customer_status             CHAR(2),
    sub_account_status          CHAR(2),
    entry_schema_date           DATE,
    next_collection_action_code CHAR(3),
    next_collectin_trx_date     DATE,
    reject_key                  NUMBER(10) not null,
    dwh_update_date             DATE,
    delta_type                  VARCHAR2(1)
  )
  </code></pre>
  
  <p>Table stg_scd_customers_key have indexes : (SINGLE INDEX OF ALL OF THEM)
  (ACCOUNT_NUMBER, START_DATE, END_DATE). DDL :</p>
  
  <pre><code>create unique index stg_admin.STG_SCD_CUST_KEY_PKP on stg_admin.STG_SCD_CUSTOMERS_KEY (ACCOUNT_NUMBER, START_DATE, END_DATE);
  </code></pre>
  
  <p>This table is also partitioned:</p>
  
  <pre><code>partition by range (END_DATE)
  (
    partition SCD_CUSTOMERS_20081103 values less than (TO_DATE(' 2008-11-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
      tablespace FCT_CUSTOMER_SERVICES_DATA
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 8M
        next 1M
        minextents 1
        maxextents unlimited
      )
  </code></pre>
  
  <p>Table structure:</p>
  
  <pre><code>create table stg_admin.STG_SCD_CUSTOMERS_KEY
  (
    customer_key   NUMBER(18) not null,
    account_number NUMBER(10) not null,
    start_date     DATE not null,
    end_date       DATE not null,
    curr_ind       NUMBER(1) not null
  )
  </code></pre>
  
  <p>I Can't add filter on the big table(need all range of dates) and i can't use materialized view. This query runs for about 20-40 minutes, I have to make it faster..
  I've already tried to drop the trunc, makes no different.</p>
  
  <p>Any suggestions?</p>
  
  <p>Explain plan:</p>
  
  <p><a href="https://i.stack.imgur.com/jwdw2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/jwdw2.png" alt="This is the explain plan for the query"></a></p>