Forum Stats

  • 3,768,996 Users
  • 2,252,894 Discussions
  • 7,874,831 Comments

Discussions

cursor vs bulk collect

francy77
francy77 Member Posts: 260 Bronze Badge

Hi all,

i'm asking which one is better:

select distinct(cli.k_id,cli.n_id) BULK COLLECT INTO in_user_id_list

from clienti cli 

  where cli.n_user_aggiorna = 'ATI'

  and cli.D_FINE_VAL is null


OR


CURSOR cur IS

  SELECT distinct(cli.k_id,cli.n_id)

  FROM

    wiam_clienti    cli,

 where cli.n_user_aggiorna = 'ATI'

  and cli.D_FINE_VAL is null




In the first case I need an array to copy the value retrieved while in the second not!!! In the first case I'm going to make elaboration entirely on SGA while in the second case I can use Disk I/O?? is that true?

«1

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    Not really true that assertion concerning disk I/O. From the cursor you may fetch row by row, thus having context switching between the SQL statement executor and the PL/SQL engine for each fetch, or fetch with bulk collect, which leads to one switching between SQL statement executor and the PL/SQL engine.

    And what do you wish to do with that select distinct? If the returns of that is to be used in another select to get other data, then it would be much better to try to get all the data in one only select, something like the below:

    with tb as (

     SELECT distinct cli.k_id,cli.n_id

      FROM

        wiam_clienti    cli,

     where cli.n_user_aggiorna = 'ATI'

      and cli.D_FINE_VAL is null

    )

    select tb.*, ...

    from tb

    join ...

    where...

    ;

  • User_H3J7U
    User_H3J7U Member Posts: 665 Silver Trophy

    FOR IN CURSOR uses array fetch by 100 rows (plsql_optimize_level>=2) and its performance is comparable to BULK COLLECT (LIMIT 100).

    francy77
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,588 Red Diamond

    ALL SQLs ARE EXECUTED AS CURSORS. Every single time.

    So the 1st code snippet of a PL/SQL bulk collect creates an implicit cursor, executes it, and uses a bulk fetch.

    The 2nd code snippet is an explicit PL/SQL definition of a cursor only. Additional code is needed to execute the cursor and fetch data from it. This fetch can also be a bulk fetch.

    An identical SQL cursor will be created in the Shared Pool, and used.

    Which approach is better DOES NOT depend on the bulk fetch or misguided false opinions of SGA or disk I/O.

    It is about code readability, maintainability, modularisation, reuse-ability - which approach serve the design and technical requirements the best.

    NextNameBluShadowfrancy77
  • francy77
    francy77 Member Posts: 260 Bronze Badge

    Thanks, no i do not have to retrieve other data; I have to update another table (one by one) (I mean every update should make a commit) I have to cycle over one milion of row; So the context switching can be very huge, and the bulk collect is better in terms of velocity??? or not

  • francy77
    francy77 Member Posts: 260 Bronze Badge

    I don 't no the value of plsql_optimize_level; I know i have to process over one milion of record!!! so I need to know if some way is very faster than the other.

  • francy77
    francy77 Member Posts: 260 Bronze Badge

    Indeed the procedure should run one and only one time (aspect of readability, maintainability, modularisation, reuse-ability) are not important at the moment; I only care about time spend this procedure has to process over one milion of records and he time spend is very important because other activity have to start when this one has finished. If you say that one way or the other way in terms of time spend are almost equals, I prefer the one with no bulk collect; thanks

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    I do hope you will not use something like the below:

    for r in (select ...) loop

    update x_table t set ...

    where t.x_column=r.x_column;

    commit;

    end loop;

    Rather then doing so, use the facilities of dbms_parallel_execute, that is if a merge won't do.

    Usually, for this kind of problem - that is when I have to update one table using the results of a select - I use merge. RTM on merge if you haven't used merge until now.

    francy77
  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    I do hope you will not use something like the below:

    for r in (select ...) loop

    update x_table t set ...

    where t.x_column=r.x_column;

    commit;

    end loop;

    Rather then doing so, use the facilities of dbms_parallel_execute, that is if a merge won't do.

    Usually, for this kind of problem - that is when I have to update one table using the results of a select - I use merge. RTM on merge if you haven't used merge until now.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,302 Gold Trophy

    I do hope you will not use something like the below:

    for r in (select ...) loop

    update x_table t set ...

    where t.x_column=r.x_column;

    commit;

    end loop;

    Rather then doing so, use the facilities of dbms_parallel_execute, that is if a merge won't do.

    Usually, for this kind of problem - that is when I have to update one table using the results of a select - I use merge. RTM on merge if you haven't used merge until now.

  • cormaco
    cormaco Member Posts: 1,722 Bronze Crown

    I second BEDE suggestion of using MERGE and have a link where you can see an example of using merge that shows that it is at least 10 times faster than any other method:

    The output shows the straight MERGE statement is an order of magnitude faster than its nearest rival. The update/insert performs almost twice the speed of the insert/update and even out performs the row-by-row MERGE.

    Just comparing the update/insert and the insert/update methods in isolation, we have to remember the comparisons will vary depending on the data in the table. If most of the data will not be present already, the insert/update approach may be better. If most of the data is already present, the update/insert approach will probably be better. If you are not sure, just use merge as it is clearer.

    In addition to the straight MERGE statement being faster, because it is a DML statement it can easily be run in parallel to improve performance further, provided your server can handle the extra load.

    francy77