This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Dec 28, 2012 9:17 AM by rp0428 Go to original post RSS
  • 15. Re: bulk collect usses with Limit clause
    padders Pro
    Currently Being Moderated
    Also who is Pravesh?
  • 16. Re: bulk collect usses with Limit clause
    siebelD Newbie
    Currently Being Moderated
    Pravesh

    for 1 lakh records ,bulk collect with limit clause 1000 took 22 minsutes (my code)

    and for same number of records when I tried with simple insert and select clause (query which you mentioned) ..it took complete 50 mins.

    Thanks
    Puja
  • 17. Re: bulk collect usses with Limit clause
    BluShadow Guru Moderator
    Currently Being Moderated
    pujakhetan wrote:
    I have not tried yet using append hint ..but query that pravesh mentioned ..means insert with select caluse is running very slaw...

    however indivisually when I tried select query only for 9 lakh records ..It showed result in second only ..that means query permance is good ..
    problem is during insertion in this table ...

    Please correct me if I am missing anything ..
    As Purvesh correctly points out, it's likely you are just getting the first few rows back in a second. Tools like TOAD etc. can retrieve rows just 500 at a time (or whatever they're set to), so that they can give you back data quickly. However, to retrieve ALL the data would take much longer.

    So, when you are querying your 9 lakh records (please don't use the term "Lakh" on the forums, it's not an international term and people don't know how many that it. Use terms like 'thousands' or 'millions' etc.) are you actually getting ALL the data back in 1 second? I very much doubt it. If you were getting it all back in that 1 second, then your insert would not be taking that long to perform, and still... doing the insert as a bulk collect and insert in batches will not be any faster.
  • 18. Re: bulk collect usses with Limit clause
    Purvesh K Guru
    Currently Being Moderated
    pujakhetan wrote:
    Pravesh
    Correction. Its Purvesh. :)
    for 1 lakh records ,bulk collect with limit clause 1000 took 22 minsutes (my code)

    and for same number of records when I tried with simple insert and select clause (query which you mentioned) ..it took complete 50 mins.

    Thanks
    Puja
    I strongly feel, you should be closing this thread and posting a new one for a Tuning request. Reading {message:id=3292438}

    Following the post of Randolf, the more information you provide, the better responses you would get.
  • 19. Re: bulk collect usses with Limit clause
    rp0428 Guru
    Currently Being Moderated
    Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION).
    >
    I am going use bulk collect with limit clause for this scenarioa...now question is what should be value for limit .
    >
    Your biggest problem isn't what to use for LIMIT.

    The biggest problems with your code are

    1. Using associative arrays instead of nested tables
    2. Using VARCHAR2(4000) for every column of your cursor instead of the correct datatype
    3. Using multiple associative arrays instead of ONE nested table
    4. Using the TRUNC function on a table column instead of a range predicate on SYSDATE

    1, 2, 3) Use ONE nested table instead of multiple associative arrays that are all VARCHAR2(4000)

    You are using this
    TYPE t_tab_promo IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
    
    v_seq t_tab_promo;
    v_batch_num t_tab_promo;
    v_name t_tab_promo;
    v_sku_src_num t_tab_promo;
    v_sub_type t_tab_promo;
    v_sku_actual_pos t_tab_promo; 
    
    CURSOR cur_new_promo IS . . .
    . . .
    FETCH cur_new_promo 
    BULK COLLECT INTO v_seq ,
    v_batch_num ,
    v_name ,
    v_sku_src_num ,
    v_sub_type ,
    v_sku_actual_pos LIMIT 1000;
    Instead define the CURSOR first and use this
    CURSOR cur_new_promo IS . . .
    
    TYPE t_cursor_rec IS TABLE OF cur_new_promo%ROWTYPE; -- nested table of cursors rows of the correct datatypes
    v_cursor_rec t_cursor_rec;
    . . .
    FETCH cur_new_promo BULK COLLECT INTO v_cursor_rec LIMIT 1000;
    4) Don't use functions on table columns. That will prevent Oracle from using an index on that column unless it is a functional index.
    AND TRUNC (promo_actual.load_date) = TRUNC (SYSDATE);
    Instead use a range predicate on SYSDATE
    AND promo_actual.load_date >= TRUNC(SYSDATE) 
    AND promo_actual.load_date < TRUNC(SYSDATE + 1)
    Fix those problems first since those implicit conversions due to the multiple associative arrays is your biggest problem.

    If you do need to use multiple nested tables then define them with the proper datatypes based on the cursor.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points