Results 1 to 2 of 2

Thread: script problem

  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: script problem

    hie guys,
    i am having some difficulties here....
    my script was running all fine till i added the line which is bold... it takes a long time to run.. is there any other appropriate way ?

    Code:
    INSERT INTO trap_ra_vca_sc_pins_v
        (distributor_id, batch_no, date_generated, date_valid_to, status, date_status_change, denomination, amount,
         card_type, no_of_transactions, date_mod, week_no, end_date)
        ( SELECT prod_id, batchnr, date_generated, date_valid_to, status, date_lstate, value, cnt1*value AS result, card_type,
          cnt1, current_date, week_no, end_date
    FROM
        ( SELECT DISTINCT prod_id, batchnr, TRUNC(date_generated) AS date_generated, TRUNC(date_valid_to) AS date_valid_to,
          status, TRUNC(date_lstate) AS date_lstate, value,
          card_type, quantity,
        ( SELECT COUNT(*)
          FROM   trap_stage_vca_sc_pins_v a
          WHERE  a.prod_id=b.prod_id
          AND    a.batchnr=b.batchnr
          AND    trunc(a.date_lstate)=trunc(b.date_lstate)    ) AS cnt1,
        ( SELECT TRUNC(sysdate) FROM dual
        ) AS current_date,
        ( SELECT to_char(sysdate,'WW') FROM dual
        ) AS week_no,
        ( to_date('31/12/9999','dd/MM/RRRR')
        ) AS end_date
    FROM trap_stage_vca_sc_pins_v b)
    WHERE (batchnr, date_valid_to) NOT IN (
    SELECT /* ALL_ROWS*/ batch_no, date_generated FROM trap_ra_vca_sc_pins_v)
    );
    commit;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    The usage of the trunc() function will most probably cause a full table scan. You should check that in the execution plan.

    If that is the case and you can afford an additional index trap_stage_vca_sc_pins_v, then you can create a function based index to speed up the query:

    CREATE INDEX idx_lstate ON trap_stage_vca_sc_pins_v (trunc(date_lstate))

    Thomas

Posting Permissions

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