Get partitionned table comments

[martbrod]

Hello,

PL/SQL Dev is launching a query to get the table comments and I think there is something wrong with the SQL:
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

The last line should be "and o.SUBOBJECT_NAME is NULL;", no?
Because when I run this query manually for a partitionned table, it tries to get the "comments" value for all the partitions. Which makes no sense.

 

[Marco Kalter]

It seems correct, but we'll check it out. Do you get incorrect results?

 

[martbrod]

No, I don't get incorrect results, the query just takes a really long time and freezes PL/SQL Dev. We have a table with 25355 partitions and it makes no sense to get the comments for all the partitions.

 

[martbrod]

The problem also occurs with the "Code Assistant". When I add the dot "." after a table name and I want to see the list of columns this query is executed and PL/SQL freezes for a bit:

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

But for a partitionned table it makes no sense because the comment (m.comments) will be repeated for each partiotion of the table (in my case 5106 times!)

The link sys.dba_objects brings back the object row for each partition of the table and this is useless in this case.

Thanks.

 

[IvanZ]

Marco, this is indeed an issue. For the tables with a lot of (sub)partitions the query runs slow and it's suboptimal.

 

[IvanZ]

Seems to be fixed in 11b1.