Toad World® Forums

query


#1

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


#2

Hi!
Try Use this hint:

/*+ use_hash(r c) use_hash(c cc) use_hash(o c) */

Oracle joins dictionaries using nested loops, its very slow in this situation, force hash or merge join…
Of course when you have fresh system stats optimiser should find fastest way without hints.
Regards Piter


#3

:smiley: it was helpful! Suppose query runs faster…


#4

Hi , Peter

Thank you very much for your input.The query runs faster with the hint .we look forward to have more and more knowledge sharing in future to help us in our daily work .

Thanks & Regards
sekhar