slow in inserting to Temp table.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: slow in inserting to Temp table.

  1. #1
    Join Date
    Apr 2003
    Posts
    19

    slow in inserting to Temp table.

    hi
    I have the following query, whcih is very fast enough in select the data but the same qry if i put to isert into a temp table it is taking lot of time.the total no of records is 87000 from select statement.
    pl help me in this query.

    index are there on purchase_log table.


    INSERT INTO temp_transd1(bt_option,bt_trno,bt_seqno,bt_processdt,bt_units,bt_agent,bt_purred,bt_acno)
    SELECT pl.fund_option,pl.txn_no,SEQ_NO,pl.batch_run_date,pl.units_allotted,pl.distributor_id,
    pl.txn_source,pl.account_number
    FROM purchase_log pl
    WHERE pl.distributor_id =Trim(o_brokcode)
    AND pl.auth_date <=i_trdate
    AND pl.txn_status <> 'R' ;



    thnks
    sk_naidu
    sknaidu

  2. #2
    Join Date
    Sep 2000
    Location
    Calcutta / Ahmedabad, India
    Posts
    137
    check the table storage clauses.
    what is your initial, next extent size, freelists ,parallelism and initrans set to
    Suvashish

  3. #3
    Join Date
    Apr 2003
    Posts
    19
    hi suvashish
    the table is a global tempporary table and thescript s below.
    CREATE GLOBAL TEMPORARY TABLE TEMP_TRANSD1
    (
    BT_OPTION VARCHAR2(6),
    BT_BRANCH VARCHAR2(6),
    BT_TRNO VARCHAR2(15),
    BT_PROCESSDT DATE,
    BT_UNITS NUMBER(20,3),
    BT_AGENT VARCHAR2(16),
    BT_PURRED VARCHAR2(8),
    BT_ACNO VARCHAR2(16),
    BT_SEQNO NUMBER(4)
    )
    ON COMMIT PRESERVE ROWS;


    thnks
    sknaidu

  4. #4
    Join Date
    Apr 2003
    Posts
    19
    hi suvashish

    here are the details.

    PCT_FREE 10
    PCT_USED 40
    INI_TRANS 1
    MAX_TRANS 255
    FREELIST1
    FREELIST_GROUP 1
    LOGGING NO
    BACKED_UP N

    thanks
    sknaidu

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    use APPEND hint with nologging
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Apr 2003
    Posts
    19
    abhaysk


    IT DIDN'T WORK.
    THANKS
    sknaidu

  7. #7
    Join Date
    Sep 2000
    Location
    Calcutta / Ahmedabad, India
    Posts
    137
    try increasing the freelists and initrans to the number of CPU.
    Suvashish

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width