Re: Query : Understand and Re-write

From: joel garry <joel-garry_at_home.com>
Date: Wed, 24 Mar 2010 09:20:56 -0700 (PDT)
Message-ID: <bffee978-cd82-4988-bc59-ad8ac6763d86_at_u15g2000prd.googlegroups.com>



On Mar 24, 7:45 am, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> 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.

The null is just the first column, the rest of the columns do print out some bizarre combination of tables, synonyms and views, when they were created and modified - but doesn't print any table type other than table. Seems quick enough, just printing out lots of data. I don't know what it is trying to do, but I suspect it was intended to figure out tables, views, and synonyms, but doesn't. Perhaps Raja could give more context?

jg

--
_at_home.com is bogus.
http://threatpost.com/en_us/blogs/how-evade-url-filters-not-so-fancy-math-032210
Received on Wed Mar 24 2010 - 11:20:56 CDT

Original text of this message