Select takes long time
Sherif Huseynli, October 15, 2020 - 7:33 am UTC
Thanks for answer,
Imagine i have 60M rows in a table and some columns holds json data. If i want to iterate through entire table with select statement, oracle will load all data on memory (approximately 130GB) then will process on them. I can achieve the same result by querying data with multiple select but it takes long time. Therefore i would like to access data direct from DBF file which will be faster than other variant.
October 15, 2020 - 4:41 pm UTC
Imagine someone updates/deletes some rows in the table you've not read yet while you're partway through reading the data file and the database writes these to the file. You've now read inconsistent data!
Using select protects you against problems like this. The results are consistent to the time the query starts.
If i want to iterate through entire table with select statement, oracle will load all data on memory
Only if you're trying to store the contents of the entire table in variables at the same time. You don't need 130Gb of memory available to query the table.
If you share your queries with us and their execution plans, we can look if there are ways you can run them faster. Going direct to the data files is almost certainly not it!
Don't blame Oracle...
Paul, October 15, 2020 - 4:41 pm UTC
Oracle is the best in that way..
Sherif Huseynli, October 16, 2020 - 7:05 am UTC
If i want to get some data and work on it then Oracle select statement (PL/SQL) is the best way and i don't have any doubt to oracle's performance and data consistency. But in my case you can think my app as data export tool and i want to export all table data as csv (or another format) but before exporting data, app may work on it (it is the point). And data won't change (insert,update,delete) during migration. I have already searched "pointer access" to table in PL/SQL without any select but nothing found. For know i fetch table rows by parts (ex: 100k by 100k) and work on this parts in PL/SQL and then export result. For working on every part i store data (ex 100k rows) on variable with "execute immidate" and "bulk insert" then iterate with loop.
İf we consider that there won't be editing any data during reading then "pointer access" to a table in PL/SQL would be perfect in this case.
If you have any suggestions i would appreciate that.
October 19, 2020 - 2:36 am UTC
Go to the downloads section on our Resources page, and search for "flat"
You'll get a Pro*C data unloader that unloads data very fast