Thanks for the question, chiengkham.
Asked: September 07, 2024 - 5:06 pm UTC
Last updated: September 17, 2024 - 5:15 am UTC
Version: oracle database 19c
Viewed 1000+ times
You Asked
i want to retrieve data with 1 million row on oracle database, it slow and take time over 1:30 hour.
--> select * from MISADMIN.FBNK_STMT_ENTRY_ALL where BOOKING_DATE = '20240630'
and Connor said...
Two possible causes here (could be one or the other or both)
1) Its hard to *find* the data.
How long does "select count(several_non_indexed_columns_concatenated) from MISADMIN.FBNK_STMT_ENTRY_ALL where BOOKING_DATE = '20240630'" take to run.
(This effectively is running the scanning portion but since its a count, we don't need to send much data across the wire)
2) Its hard to *send* the data
This might be a congested network etc. Increase arraysize and see if that has a significant impact.
If (1) is the main issue, then try
select /* + gather_plan_statistics */ * from MISADMIN.FBNK_STMT_ENTRY_ALL where BOOKING_DATE = '20240630';
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +COST'));
to give an idea of the execution plan and where the time is being lost