schema compare (still) slow in pldev 14

[Peter H.]

Dear pldev team,

I reported a slowness of the query for getting the list of schema objects for schema comparison already for pldev version 13. (Seehttps://forums.allroundautomations.com/ubb/ubbthreads.php?ubb=showflat&Number=60851#Post60893) I tried pldev 14.0.1.1965 (32bit), but the performance issue remains.

Could you, please, fix the query by removing the OR'd semi-/anti-join(s) in favour of left outer join(s), e.g., as in

select --+ use_hash(O,T)
    O.*
from sys.all_objects O
    left join sys.all_types T
        on T.owner = :owner
        and T.type_name = decode(O.object_type, 'TYPE', O.object_name)
where O.owner = :owner
    and O.object_type in ('PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'JAVA SOURCE', 'TABLE', 'SEQUENCE', 'VIEW')
    and O.object_name not like 'BIN$%'
    and O.subobject_name is null
    and ( O.object_type <> 'TYPE' or T.type_name is not null )
order by
    decode(O.object_type,
        'TABLE', 0,
        'SEQUENCE', 1,
        'VIEW', 2,
        'FUNCTION', 3,
        'PROCEDURE', 4,
        'PACKAGE', 5,
        'PACKAGE BODY', 6,
        'TYPE', 7,
        'TYPE BODY', 8,
        'TRIGGER', 9,
        'JAVA SOURCE', 10,
        100
    ),
    object_type, object_name

The schema compare tool retrieves the list of schema objects from my (small sized) DB schema in cca 25 seconds, whereas the proposed query with left outer join does the same in 0.2 seconds.

 

[Marco Kalter]

I have added this to the list of enhancement requests.