search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

ENABLE_BLOB_EXPORT 0 doesn't increase speed #1658

Closed afalszowski closed 11 months ago

afalszowski commented 11 months ago

Hello, I have a problem with the paramater ENABLE_BLOB_EXPORT. I set it to 0 (deactivated blob export) for a table which contains 6.000.000 rows and a blob column. With activated blob export the migration speed is at 70 rows/sec, which means, that migrating the table takes up to 30 hours. To save some time I thought I can disable the blob export (I don't really need the data in this column, because the data can be easily regenerated by the backend) and then the migration speed will be at the level of tables without blob columns, which is at nearly 6000 rows/sec.

Where do I have a mistake? Could someone of you help me? I can provide you more config parameters if you need.

Thank you for your help!

Adrian

darold commented 11 months ago

Hi Adrian, can you limit the export to this table and run ora2pg with the -d option to have debug output. It should show the Oracle query used to export data from this table. If ENABLE_BLOB_EXPORT is disabled the BLOB column should not appears in the target column list. Can you confirm?

afalszowski commented 11 months ago

Hi,

unfortunately the query contains the column: DEGUG: Query sent to Oracle: SELECT [...],"FT_ABSTRACT",[...] FROM "DB_BACKUP"."TABLENAME" a

But I saw that the column is not BLOB, but CLOB. Does it also work for CLOB types?

darold commented 11 months ago

Hi,

No it only works for BLOB but I have just pushed commit f568072 that will help you:

    Add ENABLE_CLOB_EXPORT configuration directive to have the same behavior
    as ENABLE_BLOB_EXPORT but with CLOB. Thanks to afalszowski for the feature
    request.

So after upgrading to latest development code add this directive to your configuration file and set it to 0.

Please confirm that it gives the appropriate behavior.

afalszowski commented 11 months ago

Hi darold, implementation is working perfectly. CLOBs are exluded now and the migration performance is pretty good now. Thank you very much for the quick change!

BR Adrian