Hi,
Oracle version: 10 g.
I have a long running sql statement(table has 140 million rows). I am fetching using filter condition abut 17 million from that. And the query uses the proper indexes but to retrieve the rows it takes a very long time. The structure of the bock is as below:
header_tab- 70 million rows.
detail_tab- 140 million rows.
{code}
declare
cursor c1 is
select col1 from header_tab ht where exists(select 1 from detail_tab dt where ht.primaykey=dt.col1 and dt.col2='ddd' and dt.col3='fff');
begin
open c1;
loop
fetch c1 bulk collect into collection limit 50000;
do some processing with these records.
exit when c1%not found;
end loop;
end;
I am executing this code from sqlplus. After executing when i see the session info the sql"select col1 from header_tab ht where exists(select 1 from detail_tab dt where ht.primaykey=dt.col1 and dt.col2='ddd' and dt.col3='fff');" is getting executed but after 3 hours or so the slplus is still stuck but when i query for the session it is not there. Any idea why the session is lost before the whole block is executed?
{code}
Thanks
Roy