Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 25, 2020 - 6:05 am UTC

Last updated: July 27, 2020 - 3:09 am UTC

Version: 12c

Viewed 1000+ times

You Asked

I want to compare following cursor example. and i want to know which one is better for 10 millions record for processing data.

And how to check internally execution plan and internal process.

declare

  type type_1 is table of user_tab_columns%rowtype;

  cursor cursor_1(p_table_name varchar2) is
    select * from user_tab_columns s where s.table_name = p_table_name;

  v_type_1 type_1;

begin
  open cursor_1('TABLE_NAME_1');
  fetch cursor_1 bulk collect
    into v_type_1;
  close cursor_1;

  for i in v_type_1.first .. v_type_1.last loop
    dbms_output.put_line(' ' || v_type_1(i).column_id ||
                         ' ' || v_type_1(i).column_name ||
                         ' ' || v_type_1(i).data_type);
  end loop;

end;

------------------------------------------------------------
declare

  cursor cursor_2(p_table_name varchar2) is
    select * from user_tab_columns s where s.table_name = p_table_name;

begin

  for i in cursor_2('TABLE_NAME_1') loop
    dbms_output.put_line(' ' || i.column_id || ' ' || i.column_name || ' ' ||
                         i.data_type);
  end loop;

end;
------------------------------------------------------------

declare

  cursor cursor_3(p_table_name varchar2) is
    select * from user_tab_columns s where s.table_name = p_table_name;
  v_type cursor_3%rowtype;

begin

  open cursor_3('TABLE_NAME_1');
  loop
    fetch cursor_3
      into v_type;
    dbms_output.put_line(' ' || v_type.column_id || ' ' ||
                         v_type.column_name || ' ' || v_type.data_type);
    exit when cursor_3%notfound;
  end loop;
  close cursor_3;

end;

and Connor said...

OK, lets call them cursor1,, cursor2 and cursor3

cursor1

fine for small data (say up to 1million records), but you are getting ALL of the data into memory, so if you try bring 10billion rows back...that is probably going to crash your session

cursor2

will likely run just as fast as cursor1, because we fetch in batches of 100, will not have any memory issues, is easy to code

cursor3

fetches 1 row at a time and will be the slowest (by a long way). Fine if you were planning on fetching but might need to prematurely end the loop after a small number of records.

For 99% of your code, cursor2 is probably your go-to option.


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library