Re: Query : Understand and Re-write

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 24 Mar 2010 09:27:04 -0700 (PDT)
Message-ID: <a183e6c8-1494-4aec-89ae-09c41f8a7d2d_at_f8g2000yqn.googlegroups.com>

On Mar 24, 9:02 am, raja <dextersu..._at_gmail.com> wrote:
> 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 if you stick a " create view stupid as " on top of the SQL you can then see it is trying to create the following:

SQL> desc stupid

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 TABLE_CATALOG                                      VARCHAR2
 TABLE_SCHEMA                                       VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 TABLE_TYPE                                         VARCHAR2(18)
 TABLE_GUID                                         VARCHAR2
 DESCRIPTION                                        VARCHAR2
 TABLE_PROPID                                       VARCHAR2
 DATE_CREATED                                       DATE
 DATE_MODIFIED                                      DATE

which looks something like the infomation_schema views you can find in SQL Server though it does not match any of the standard views I peaked at. (The first 4 column names match information_schema.tables but I do not know about the rest) Vendor applications that are intended to run on multiple vendor databases often try to create a common dictionary view of their own that are used in the programs.

The performance on a 9.2.0.6 Oracle system was 2175 rows in under 2 seconds.

HTH -- Mark D Powell -- Received on Wed Mar 24 2010 - 11:27:04 CDT