Prev: HA Problem with ORA-02019
Next: CPU and memory metrics for databases in Oracle RAC
From: raja on 24 Mar 2010 09:02
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.
From: John Hurley on 24 Mar 2010 10:45
On Mar 24, 9:02 am, raja <dextersu...(a)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.

From: joel garry on 24 Mar 2010 12:20
On Mar 24, 7:45 am, John Hurley <hurleyjo...(a)yahoo.com> wrote:
> On Mar 24, 9:02 am, raja <dextersu...(a)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
--
@home.com is bogus.
http://threatpost.com/en_us/blogs/how-evade-url-filters-not-so-fancy-math-032210
From: Mark D Powell on 24 Mar 2010 12:27
On Mar 24, 9:02 am, raja <dextersu...(a)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 --

From: John Hurley on 24 Mar 2010 14:39
On Mar 24, 12:27 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote:

snip

> 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

Ahhh ... very nice companion to the I'm with stupid t shirt approach
maybe?

> which looks something like the infomation_schema views you can find in
> SQL Server

Who wudda thunk? Nice catch ...

So maybe some kind of emulation layer to run a query on Oracle to
simulate some info that you might get from some cousin app running on
SQL Server?

So it all goes back to Raja: what are you trying to do and why?

What's the best way to make something do less work on your system?
( Don't do it ).
 |  Next  |  Last
Pages: 1 2
Prev: HA Problem with ORA-02019
Next: CPU and memory metrics for databases in Oracle RAC