All,
I've run into the following queries "hanging" when ran on a database with the
optimizer_mode set to FIRST_ROWS. If the optimizer_mode is CHOOSE, no
problems. When set to FIRST_ROWS both queries show never-ending wait events
for "direct path read". I killed the sessions before they finished after
waiting for almost an hour for the queries to complete.
I re-wrote the 1st query against dba_tables to use EXISTS (also shown below)
and that seemed to work fine. But I'm not sure why or how to re-write the 2nd
query to also be able to work....I've tried a couple of things with no luck.
Any ideas?
Thanks for the help,
Karen Morton
select dba_tab_columns.table_name, dba_tab_columns.column_name,
dba_tab_columns.column_id, dba_tab_columns.data_length,
dba_tab_columns.data_type, dba_tab_columns.nullable,
dba_tab_columns.data_precision
from dba_tables, dba_tab_columns
where dba_tables.owner = 'XYZDBA'
and dba_tables.table_name = dba_tab_columns.table_name
order by dba_tab_columns.table_name, dba_tab_columns.column_id ;
select dba_indexes.table_name, dba_indexes.index_name,
dba_indexes.uniqueness, dba_ind_columns.column_name,
dba_ind_columns.column_position
from dba_indexes, dba_ind_columns
where dba_indexes.owner = 'XYZDBA'
and dba_indexes.index_name = dba_ind_columns.index_name
order by dba_indexes.table_name, dba_indexes.index_name,
dba_ind_columns.column_position ;
-- Rewritten dba_tables query that works
select dba_tab_columns.table_name,
dba_tab_columns.column_name,
dba_tab_columns.column_id,
dba_tab_columns.data_length,
dba_tab_columns.data_type,
dba_tab_columns.nullable,
dba_tab_columns.data_precision
from dba_tab_columns
where EXISTS (SELECT *
FROM dba_tables
WHERE owner = 'XYZDBA'
AND table_name = dba_tab_columns.table_name)
order by dba_tab_columns.table_name, dba_tab_columns.column_id ;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Karen Morton
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
