idea: opensource the queries in plsqldev?

[Peter H.]

Instead of making the whole PL/SQL Developer open source which, I guess, would ruin your business, would it be possible to make at least all the underlying queries as open source? This way you could focus on developing features in PL/SQL Developer while the community would help you and themselves with all the query-related bugfixing and enhancements. Of course, you guys would remain owners of the queries repository and would be in charge of accepting/rejecting pull requests.

Perhaps even some more enhanced: Making also some other internal string resources open-sourced, for example the bracket-matching regexp's and similar.

 

[Marco Kalter]

I have added this to the list of enhancement requests.

 

[Peter H.]

Example of the advantages:

When opening the schema compare window (in pldev version 13.0.6), the underlying query getting the list of schema objects to be compared is slow (... at least on my DB). I found the query via sessions browser to be

select *
from sys.all_objects o
where owner = :owner
and object_type in ('PACKAGE', 'PACKAGE BODY', 'TYPE', 'TYPE BODY', 'FUNCTION',
                    'PROCEDURE', 'TRIGGER', 'JAVA SOURCE', 'TABLE', 'SEQUENCE', 'VIEW')
and (object_name not like 'BIN$%')
and (subobject_name is null) and (object_type <> 'TYPE' or exists (select null from sys.all_types t where t.owner = o.owner and t.type_name = o.object_name))
order by decode(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

... which, thanks to the semi-join hidden behind an OR-predicate, is taking 21.7 seconds to return results (for a specific DB schema of mine).

The query can be rewritten to an equivalent query of

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

... which returns results in 0.2 seconds (for the same specific DB schema of mine).

If you opensourced or even externalized (e.g., into files in pldev installation folder) these queries, community would make your product run blazing fast while you could focus on adding new features that matter.

Please, think about doing this step forward even for the upcoming 1-2 releases.