Please ignore the question. Today, I got some time to test Oracle ODBC
19.9 driver and it supports fast_executemany flag:
mgogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19
-t test_tab1 -i /tmp/test_tab.csv
5000000 rows loaded
real 13m13.095s
user 1m3.680s
sys 0m39.647s
mgogala@umajor:~/work/python$ time ./load_csv.py -u scott/tiger@Ora-19
-t test_tab1 -i /tmp/test_tab.csv -m
5000000 rows loaded
real 0m32.056s
user 0m8.776s
sys 0m0.330s
The flag is controlled by the "-m" argument. Without the flag, loading 5
million rows takes more than 13 minutes. With the flag, it's 32 seconds.
The driver is the instant client 19.9 ODBC driver:
[Ora19c]
Description=Oracle ODBC driver for Oracle 19
Driver=/usr/lib/instantclient_19_9/libsqora.so.19.1
Setup=
FileUsage=
CPTimeout=
CPReuse=
That did not work with the instant client 19.8. I'm happy as a clam, I
don't have to write separate script for each database. This is actually
big news. Now, if only MySQL ODBC driver did the same....
On 11/19/20 12:53 AM, Mladen Gogala wrote:
Oracle Corp. has generously provided cx_Oracle Python package for Oracle and an Oracle ODBC driver, free of charge. There is a very new and and a very interesting feature called "fast_executemany" which is known to us Oraclites for the last 2 decades as "array interface". The description of that very new feature is here:--
https://github.com/mkleehammer/pyodbc/wiki/Features-beyond-the-DB-API
Essentially, the whole array is inserted at once, thereby saving many trips over the network. Sounds familiar? Well, there is a little problem: Oracle ODBC driver doesn't support that feature, at least not the one from the instant client 19.8. As this feature is very new, very few drivers support it. Here are some drivers that were tested with "fast_executemany":
https://github.com/mkleehammer/pyodbc/wiki/Driver-support-for-fast_executemany
The description saying that it "crashes Python" sounds particularly amusing, but it isn't. I tried with FreeTDS and got an extremely quick core dump. Here is a question for the friends from Oracle Corp. on this list: Is Oracle Corp. going to support fast_executemany in the ODBC driver provided with the instant client? When can that support be expected, if the answer to the previous question is a positive one? I really don't want to write a separate script to insert into an Oracle database. The MS ODBC no. 17 works. The other drivers usually result in the use of the good, old no. 7.
