Data dictionary queries issued by PLSQL Dev. causes cartesian join

[Bert Gombos]

When I use Describe Object functionality, PLSQL Developer (9.0.6) issues this:

select t.*, o.*, m.comments from sys.dba_tables t,
sys.dba_tab_comments m,
sys.dba_objects o
where t.owner = bject_owner
and t.table_name = bject_name
and m.owner (+) = bject_owner
and m.table_name (+) = bject_name
and o.owner (+) = bject_owner
and o.object_name (+) = bject_name
and bject_type is not null
and :sub_object is null

This is not good because of lack of joins. It causes merge join cartesian on some big databases.

Please use this:

select t.*, o.*, m.comments from sys.dba_tables t,
sys.dba_tab_comments m,
sys.dba_objects o
where t.owner = bject_owner
and t.table_name = bject_name
and m.owner (+) = t.owner
and m.table_name (+) = t.table_name
and o.owner (+) = t.owner
and o.object_name (+) = t.table_name
and bject_type is not null
and :sub_object is null

Review of all data dictionary queries is recommended.

 

[Marco Kalter]

We'll check it out.

 

[JoshRGM]

Hey there sorry to resurrect this question, but we've having the same exact issue. Is there a solution to this? We've upgraded to 11.2 and just started getting this issue on tables with a large number of partitions.

This is happening in version 10.0.5.1710.

 

[stefanz]

it seems, they do not care, another post regarding the same problemhttps://forums.allroundautomations....bb=showflat&Main=12429&Number=47105#Post47105

 

[Marco Kalter]

We have a fix available for 10.0.5. Please send an e-mail to support@allroundautomations.com to obtain the fix.