Re: Query : Understand and Re-write

From: raja <>
Date: Thu, 25 Mar 2010 00:43:43 -0700 (PDT)
Message-ID: <>

Thanks for all your immediate responses.

AWR Report showed this query in one of the top list. I was not able to understand this query. So though posting here would give me immediate ideas to move forward.

ok. from my observation.
there is a UNION ( with 2 queries ), then there are having filter condition as TABLE.
1st query : trying to take list of table and view using ALL_OBJECTS 2nd query : trying to take list of SYNONYMS, TABLES, VIEWS using ALL_OBJECTS, ALL_SYNONYMS ( with unnecessary joining ALL_OBJECTS twice and then with ALL_SYNONYMS )

Finally taking the output, filtering to take table names alone.

To rewrite the query : I felt why shouldnt we just use ALL_OBJECTS to take the list of table_names alone !!!
Comments : Collecting gather stats is waste, as the tables/views are all related to METADATA ( system related )

Am i right ?

Please help.

With Regards,
Raja. Received on Thu Mar 25 2010 - 02:43:43 CDT

Original text of this message