Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

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

Answered by: Connor McDonald - Last updated: July 27, 2020 - 3:09 am UTC

Category: Database Administration - Version: 12c

Viewed 100+ 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 we 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.

More to Explore

PL/SQL

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