query to describe table

[stefanz]

When describing a table (right click, describe), the following statement is executed:
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
:OBJECT_TYPE = TABLE
:OBJECT_OWNER = SZ
:OBJECT_NAME = TESTSZ
:SUB_OBJECT = Null

I do not understand the last two lines, for a table with many partitions/subpartitions this means a cartesian join, many duplicate rows, as this selects all rows from dba_objects, i.e. the table info and all partitions/subpartitions.

why did I notice this? a describe on a table with 30'000 subpartitions was significantly slower than a describe on a non partitioned table.

at least in 11.2 dba_tab_columns contains the object type, so the join to dba_object should not be required. was this different for older releases of Oracle?

 

[Marco Kalter]

Do you mean these 2 lines?

and bject_type is not null
and :sub_object is null

They are bind variables and the expression is either true or false. This does not affect the query performance.

 

[stefanz]

Yes, that is what I thought they were and with above bind variables, they evaluate to true.

IF a table has 30'0000 subpartitions then this query generates 30'0000 identical rows. This will be a performance problem, unless of course, internally only the first row is considered.

 

[chizh]

On our server (exadata) this select executes about 10 minutes.
Fix it by this please:
SELECT t.*, o.*, m.comments
from sys.all_tables t,
sys.all_tab_comments m,
sys.all_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