Re: Query : Understand and Re-write

From: raja <dextersunil_at_gmail.com>
Date: Thu, 25 Mar 2010 00:43:43 -0700 (PDT)
Message-ID: <1321fb22-e93c-4215-b302-ae5391369523_at_g1g2000pre.googlegroups.com>



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