Query : Understand and Re-write

From: raja <dextersunil_at_gmail.com>
Date: Wed, 24 Mar 2010 06:02:48 -0700 (PDT)
Message-ID: <01657f85-f67c-4eed-9486-2340054e46f4_at_k5g2000pra.googlegroups.com>

Hi,

I cant understand what they are trying to fetch in the below query.

Can anyone please explain me ?

Also, Can anyone help me to re-write the following query, to understand better and get good performance :

SELECT *
FROM
(SELECT
NULL table_catalog,
decode(owner, 'PUBLIC', NULL, owner) TABLE_SCHEMA, object_name TABLE_NAME,
decode
(
owner, 'SYS',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',


'SYSTEM VIEW',    object_type), 'SYSTEM',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',


'SYSTEM VIEW',    object_type),    'DMSYS',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',


'SYSTEM VIEW',    object_type),    'ORDSYS',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',


'SYSTEM VIEW',    object_type),    'EXFSYS',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',


'SYSTEM VIEW',    object_type),    'WMSYS',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',


'SYSTEM VIEW',    object_type),    'MDSYS',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',


'SYSTEM VIEW',    object_type),    'CTXSYS',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',


'SYSTEM VIEW',    object_type),    'OLAPSYS',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',


'SYSTEM VIEW',    object_type),    'WKSYS',

decode(object_type,    'TABLE',    'SYSTEM TABLE',    'VIEW',

'SYSTEM VIEW', object_type), object_type
) table_type,
NULL table_guid,
NULL description,
NULL table_propid,
created date_created,
last_ddl_time date_modified
FROM
all_objects
WHERE
object_type IN('TABLE', 'VIEW')
UNION
SELECT
NULL table_catalog,
decode(o2.owner, 'PUBLIC', NULL, o2.owner) TABLE_SCHEMA, o2.object_name TABLE_NAME,
o2.object_type table_type,
NULL table_guid,
NULL description,
NULL table_propid,
o2.created date_created,
o2.last_ddl_time date_modified
FROM

all_objects o2,
all_objects o3,
all_synonyms s

WHERE

    o2.object_type = 'SYNONYM'
AND(o3.object_type = 'TABLE' OR o3.object_type = 'VIEW')
AND o2.owner = s.owner
AND o2.object_name = s.synonym_name

AND s.table_owner = o3.owner
AND s.TABLE_NAME = o3.object_name
)
dbschema_tables
WHERE table_type = 'TABLE';

Thanks in Advance.

With Regards,
Raja. Received on Wed Mar 24 2010 - 08:02:48 CDT