Re: Query : Understand and Re-write

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

My question would be if you don't know what this does or how it works, why do you

1. believe it has a performance problem and
2. think that re-writing it will improve anything?

answers to these two questions would go a long way to answering your original question.

The first thing I would do is

1. re-format the query so that you can see clearly how all the statement components relate to each other
2. Understand what each component does
3. Clearly define/identify what the query is supposed to do
4. Gather stats on how it is actually performing
5. Define what are acceptable performance stats
6. Perform analysis of the full query using the various provided oracle tools to identify possible problem areas
7. Investigate how to modify things to address any performance issues. Note that this may or may not involve re-writing the query. It could also involve other actions, such as adding/removing/modifying indexes, writing functions, updating stats, using temporary tables or other built-in facilities or possibly totally changing the approach bieng used to get the information desired.

Then iterate through steps 6 to 7 until you get the desired outcome. Until you understand the query and the desired results, anything anyone else does will mean little for you as it won't have the necessary context. Note that you will likely get much more specific assistance from this group if you also show what you have done to try and address your grasp of the issue, especially if you can show what you understand and what is still confusing. Including details of oracle version and platform is also very useful as different versions/platforms can provide alternative features and solutions.

-- 
tcross (at) rapttech dot com dot au