Re: Query : Understand and Re-write

From: John Hurley <hurleyjohnb_at_yahoo.com>
Date: Wed, 24 Mar 2010 07:45:06 -0700 (PDT)
Message-ID: <d16293f5-5cb9-4bbc-ba1e-3e3d2370d17e_at_t23g2000yqt.googlegroups.com>

On Mar 24, 9:02 am, raja <dextersu..._at_gmail.com> wrote:

snip

> 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.

Well you are selecting NULL 4 times in the query. So you get NULL data back.

Why don't you tell us what you get and why if you modify this and do not select NULL. Received on Wed Mar 24 2010 - 09:45:06 CDT