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