Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sherif.

Asked: October 14, 2020 - 8:31 am UTC

Answered by: Connor McDonald - Last updated: October 19, 2020 - 2:36 am UTC

Category: SQL Developer - Version: Oracle 10g

Viewed 100+ times

You Asked

Hi,

i want to iterate all rows in table and do some operations on them, but i don't want to get data with some select statement. In order to do that i tried to export data as .dbf and read it with Java and do some stuff. But it seems Oracle .dbf format differs than xBase ( https://www.clicketyclick.dk/databases/xbase/format/ ) .dbf format. Where can i get Oracle .dbf data file structure details. Or can you give me more suitable techniques and advice in this purpose.

Thank you.

and we said...

Its a proprietary format.

With time, patience, and a lot of effort you could probably work it out...but you really want to use SQL to get anything from the database.

Why would you want to read the DBF files directly?

and you rated our response

  (3 ratings)

Reviews

Select takes long time

October 15, 2020 - 7:33 am UTC

Reviewer: Sherif Huseynli from Azerbaijan

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.
Chris Saxon

Followup  

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...

October 15, 2020 - 4:41 pm UTC

Reviewer: Paul from Canada

What is YOUR app going to do with 130GB of Json data?
60 Million rows isn't that much in this day and age.
Oracle provides the ability to query Json data directly (if you are configured to do so) so you can probably let Oracle just return the rows you need as opposed to all 60 Million of them.

https://blogs.oracle.com/sql/how-to-store-query-and-create-json-documents-in-oracle-database

What is the business requirement?



Oracle is the best in that way..

October 16, 2020 - 7:05 am UTC

Reviewer: Sherif Huseynli from Azerbaijan

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.


Connor McDonald

Followup  

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

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database