Hi , can any body suggest me the better way to tune the following query.It is running pretty slow on 9i database with resonable objects.
SELECT SUBSTR(o.object_type, 0, 1) AS table_type,
c.table_name,
c.column_name,
c.data_type_owner,
c.data_type,
CASE WHEN c.data_precision IS NULL THEN c.char_length
ELSE c.data_precision END AS precision,
c.data_scale,
c.nullable,
c.data_default,
c.character_set_name,
r.scope_table_owner,
r.scope_table_name,
cc.comments
FROM sys.DBA_tab_columns c
INNER JOIN sys.DBA_objects o
ON o.object_name = c.table_name
AND o.owner = c.owner
LEFT OUTER JOIN sys.DBA_refs r
ON r.owner = c.owner
AND r.table_name = c.table_name
AND r.column_name = c.column_name
LEFT OUTER JOIN sys.DBA_col_comments cc
ON cc.owner = c.owner
AND cc.table_name = c.table_name
AND cc.column_name = c.column_name
WHERE c.owner = ‘CSOSOURCE’
AND o.object_type IN (‘TABLE’, ‘CLUSTER’, ‘VIEW’, ‘MATERIALIZED VIEW’)
and c.table_name not like ‘SNAP$%’
and c.table_name not like 'MVIEW$%’
and c.table_name != ‘LOCAL_LEDGER’
and c.table_name not like ‘MLOG$%’
and c.table_name not like 'RUPD$%’
and c.table_name not like ‘BIN$%’
and not exists (select ‘x’
from sys.DBA_snapshots ds
where ds.owner = c.owner
and ds.name = c.table_name
and o.object_type = ‘TABLE’)
ORDER BY c.table_name, c.column_id
regards
sekhar
it was helpful! Suppose query runs faster…