7 Replies Latest reply: Sep 20, 2013 12:02 AM by Billy~Verreynne RSS

    Faster table creation

    user9338712

      Hai all,

       

       

      10.2.0.5 on Solaris 10

       

       

       

       

      We have the below query which is time consuming.

       

       

      {code}

      create table callst nologging parallel 12 as

      SELECT

      CONTRNO    CONTRNO,

      SUBSCR_TYPE    SUBSCR_TYPE,

      AREA    AREA,

      SUBNO    SUBNO,

      CHARGETYPE    CHARGETYPE,

      BILLCODE1    BILLCODE,

      TRUNC (MIN (TRANSDATE))    TRANSDATE,

      TRUNC (MAX (TRANSDATE))    TRANSDATE_TO,

      BILLTEXT    BILLTEXT,

      SUM(BILLAMOUNT)    BILLAMOUNT,

      FACTOR    FACTOR,

      SYSDATE    UPDDATE,

      TARIFFCLASS1    TARIFFCLASS,

      COUNT(1)    NO_OF_CALLS,

      SUM(DURATION)    DURATION,

      SUM(GROSS_AMOUNT)    GROSS_AMOUNT,

      SUM(ACT_DURATION)    ACT_DURATION,

      AR_BILLTEXT    AR_BILLTEXT,

      CALL_TYPE    CALL_TYPE,

      FACTOR    FACTOR_INT,

      LAST_TRAFFIC_DATE    LAST_TRAFFIC_DATE,

      RATE_TYPE    RATE_TYPE,

      TARIFF_GROUP    TARIFF_GROUP,

      RATE_POS    RATE_POS,

      RATE_PLAN    TARIFF_PROFILE,

      TARIFF_GROUP    ORG_TARIFF_GROUP

      FROM HISTCALLS

      WHERE (CONTRNO , SUBNO) IN (

      SELECT CONTRNO , SUBNO FROM COMPLETE_HEADER_SUBS)

      AND LAST_TRAFFIC_DATE ='21-SEP-2013'

      AND TRANSDATE +0  > TO_DATE ('01-AUG-2013 000000','DD-MON-YYYY HH24MISS')

      GROUP BY

      CONTRNO, SUBSCR_TYPE, AREA, SUBNO, CHARGETYPE, TARIFFCLASS1, BILLCODE1, BILLTEXT, AR_BILLTEXT, LAST_TRAFFIC_DATE, FACTOR, RATE_PLAN, CALL_TYPE, TARIFF_GROUP, RATE_TYPE,RATE_POS;

      {code}

       

       

       

       

      How I can put the above in a loop and insert values and perform commit frequently for faster inserts .

       

       

      Please advise.