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

Very slow statement in _get_privilege #1670

Closed mgerhardy closed 10 months ago

mgerhardy commented 11 months ago

We've commented out a few lines in _get_privilege because it made the execution very slow (approx. 1h). This is due to the fact that it adds more than hundred AND NOT REGEXP_LIKE(UPPER(...),?) to the statement.

$str .= " " . $self->limit_to_objects('GRANT|TABLE|VIEW|FUNCTION|PROCEDURE|SEQUENCE', 'b.GRANTEE|b.TABLE_NAME|b.TABLE_NAME|b.TABLE_NAME|b.TABLE_NAME|b.TABLE_NAME');

Just reporting this so you can decide whether its worth to make this an option maybe.

By omitting the limit_to_objects calls we couldn't find any difference (except the runtime - which was much faster) in the result.

darold commented 11 months ago

You might have lot of objects set to EXCLUDE directive?

mgerhardy commented 11 months ago

Yes, there are a lot of temp tables that we exclude by temp.*

darold commented 11 months ago

So when it is time to export grants you could disable the EXCLUDE configuration directive because it is responsible of the call to method limit_to_objects().

mgerhardy commented 11 months ago

yes of course - we already have our workaround, just wanted to let you know that others who are using lots of temp tables also might suffer from this. and the statement really takes a lot of time in that case.