Results 1 to 7 of 7

Thread: cursor problem

  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    14

    Post Unanswered: cursor problem

    My code is

    create or replace procedure p1 is
    CURSOR C1 IS
    SELECT DISTINCT col1,col2,col3 from t1 where col4=val1;
    lc_doc CLOB;
    amount BINARY_INTEGER;
    offset INTEGER;
    begin
    for rec in c1
    loop
    lc_header:=val;
    dbms_lob.createtemporary(lc_doc,true);
    DBMS_LOB.WRITE(lc_doc, amount, offset, lc_header);
    DECLARE
    CURSOR C2 IS
    SELECT DISTINCT col5,col6,col7 from t1 where col1=rec.col1val;
    BEGIN
    FOR REC2 IN C2
    LOOP
    DBMS_LOB.WRITEAPPEND(lc_doc,LENGTH(someval), someval);
    EXIT WHEN C2%NOTFOUND;
    END LOOP;
    END;
    insert into tab2 values (col1,col2,col3) values (someval1,lc_doc,someval3);
    dbms_lob.freetemporary(lc_doc);
    .........
    EXIT WHEN C1%NOTFOUND;
    END LOOP;
    END;

    i have 1000 rows in tab1 for c1 and each of it has 1500 enteries for c2.I find that just to load these values it takes more than an hour.
    Is there any better soln?or am i doing somethin wrong?
    please help
    Last edited by manojkv; 11-21-03 at 05:15.

  2. #2
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Hi,
    For the first cursor..... Create an index on those 3 columns for the underlaying table.

    The second cursor, u r declaring it inside the loop...
    instead u declare it outside once as PARAMETERISED Cursor!!
    Its like:
    CURSOR C2(Pcol1val) IS
    SELECT DISTINCT col5,col6,col7 from t1 where col1=Pcol1val;

    Then you just open the cursor inside the loop like
    OPEN C1(rec.col1val);

    this will improve the performance i think.

    Regards
    Shelva

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    14
    hi Shelva,
    I read that an index scan range would be costlier than the full table scan.so would indexing cost me on the performance?pls advice.

    Regds
    Manojkv

    Originally posted by shelva
    Hi,
    For the first cursor..... Create an index on those 3 columns for the underlaying table.

    The second cursor, u r declaring it inside the loop...
    instead u declare it outside once as PARAMETERISED Cursor!!
    Its like:
    CURSOR C2(Pcol1val) IS
    SELECT DISTINCT col5,col6,col7 from t1 where col1=Pcol1val;

    Then you just open the cursor inside the loop like
    OPEN C1(rec.col1val);

    this will improve the performance i think.

    Regards
    Shelva

  4. #4
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Yah in some cases yes.

    With reference to your query (SELECT DISTINCT ****) it would be benifitial.
    Also try to use PARALLEL QUERY and i think it would make it faster!!

  5. #5
    Join Date
    Nov 2003
    Location
    India
    Posts
    14
    Hi Shelva,
    I cannot send a parallel query.Its the same table i'm getting the records from.

    I have 1000 recs with different col1 vals.For each row of the col1 vals i have 1500 rows with different col5 vals.The table is in unnormalized form.So i have to use one cursor inside another cursor.I have to append these values to a clob variable for each of col1 values.Then i do further manipulation.

    Is there any better way to do this?
    Pls advice

    Thx&Regds
    Manojkv

  6. #6
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Well Manoj,
    Parallel query is for fetching the data from Same table..

    its like this

    SELECT /*+ PARALLEL(scott_emp, 5) */ ename
    FROM scott.emp scott_emp;

    Just read the documentation ... it would be more helpful.

    Regards

  7. #7
    Join Date
    Nov 2003
    Location
    India
    Posts
    14
    Hi Shelva,
    I found that the cursors were not eating up the time it was the CLOB that i used.Everytime i created a temperory CLOB and was appending it on & on.Is there any better way for doing this?

    pls advice.

    Regds
    Manoj

Posting Permissions

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