Processing 18million records Oracle

I’ve an oracle database with a little over 18m records. I need to fetch these records and run them against a business logic. What will be the best approach - Can a genstage/flow help in this context.
I plan creating a process that will fetch a count of all the records, then start about 20 processes that will fetch records concurrently using limit/row_number then process the records.

3 Likes

If you have a specific question feel free to follow up but generally, yes, this sounds like a fantastic application of Flow in particular. Broadway would be best suited but there’s certainly not an oracle db Producer for that yet so that seems premature. And by the time you come up with your own producer you could probably implement a chain of Flow commands to do what you want.

In terms of interfacing with Oracle, I’m assuming folks are still either using some community erlang libraries or using some kind of Postgres wrapper for Oracle.

FYI:

2 Likes

I played with both oracle_fdw and jamdb a few months ago. IMHO, jamdb is easier to use. It’s not a full Oracle driver implementation, but the basic functionality is all there.

Some of us would really appreciate if you let us know about the results of this activity.

Good luck

EDIT: Even when it just mentions Erlang in its description, jamdb works fine with Elixir as well.

3 Likes

Flow is kind of new to me, came across it recently when going through elixir stuff on youtube. I will read further and give it a shot. With Flow; do I have to create several Flow processes each with it own set of data to work on. How will the partition function work in this case.

I will do a POC with the two and see how it goes. I will keep you posted with my findings.

1 Like

Excellent. Thank you.

Also, there’s another Erlang/Elixir driver for Oracle:

I didn’t try it because it hasn’t been updated in two years, and it’s based on ODBC which I thought was going to be slower than the other two alternatives.

Cheers

1 Like

See if this look interesting oranif
An ecto wrapper is also possible. Please let me know if that would be useful.
TIA

3 Likes

Welcome on-board, I will check the library out. Thanks.

Hi, and welcome to the forum.

Are you saying you can provide an Ecto adapter for Oracle? Some of us have been waiting for something like that for months or even years. As I said above, jamdb is the closest we have.

1 Like

Thanks,

I am new to elixir and learning. If ecto adapter is in demand I will give it a try and my learning exercise.

Oranif is pure erlang NIF wrapper of Oracle ODPI-C thin OCI wrapper. This deprecates erloci (I wrote a while ago) the old port driver to access directly OCI.

Oranif has has out performed in throughput of insert/select/update of erloci and Erlang ODBC quite significantly so far.

So if you are looking for performance and compiling NIFs in deps isn’t an issue then oranif should help.

I haven’t performance compare against jamdb or odbc though yet!

2 Likes

Thank you. Other than ETS I haven’t really used Erlang modules directly at all, but I’ll give oranif a try.

I don’t know if you already aware of this two resources:

Article series on latest MySQL Ecto adapter:

http://blog.plataformatec.com.br/2018/11/building-a-new-mysql-adapter-for-ecto-part-i-hello-world/

There’s a talk at this year Elixir conference as well, and:

Also, I’m not experienced in Elixir enough to help you with the code, but let me know if you need help testing.

2 Likes

I looked into postgres elixir driver and postgres ecto adapter together with ecto_sql.
If my understanding is correct so far, I need to first wrap oranif in elixir which will integrate eventually ecto Oracle adapter.
So if I manage to write a functional a elixir wrapper driver in the mean time I will let you know and you can try that out instead oranif Erlang calls.
Will much appreciate a code review and test when the driver is ready to use.
TIA

2 Likes