This view is part of the upgrade script provided by the Vendor (duh): I've been DBA'ing for 7 years in DB2 and Oracle and messing around with a SYS view is obviously not done- .
I was looking for some proper advise as Ive never seen anything like it before. Sam ----- Original Message ----- Date: Sunday, July 27, 2003 11:39 am > People should not play with the SYS schema, period. No ifs, no buts, > it just isn't done. You are on your own, pal. If I were your employer, > you would have hit the road by now. Whatever application that Maximo > thing is, creating objects owned by SYS is simply not acceptable. > It's guys like you that give database administrators a bad name. > > > On 2003.07.27 01:29, [EMAIL PROTECTED] wrote: > > Unix Solaris 8 and Oracle 8.1.7.4 > > > > As part of an upgrade to the Maximo application I run a join on 2 > > tables: > > > > select count(*) from sys.syskeys s, maxsysindexes m where > > s.ixname=m.name; > > sys.syskeys is 705 rows and maxsysindexes is 443 rows. > > when I trace the statement I find it doing a hash join and it > estimates> 434 blocks and it does each block in 30 secs. > > > > Prior to running the query I create the sys.syskeys view as detailed > > below: > > The only way I get round the problem is to recreate the > > maxsysindexes table: And of course query then takes 1 second. I > > have disabled hash join and the query just goes down another path > > taking just as long. > > > > Any thoughts as this only happened once the first upgrade in 10 > > schemas, now its happening all the time. > > > > --schema owner > > > > DROP VIEW SYS.SYSKEYS; > > > > CREATE VIEW SYS.SYSKEYS > > (IXCREATOR, IXNAME, COLNAME, COLNO, COLSEQ, > > ORDERING, FUNCTION) > > AS > > SELECT IO.NAME, IDX.NAME, C.NAME, C.COL#, IC.POS#, 'A', '' > > FROM SYS.COL$ C, SYS.OBJ$ IDX, > > SYS.OBJ$ BASE, SYS.ICOL$ IC, > > SYS.USER$ IO, SYS.USER$ BO > > WHERE IO.NAME = 'ADWEA' AND BASE.OBJ# = C.OBJ# > > AND IC.COL# = C.COL# > > AND IC.BO# = BASE.OBJ# > > AND IO.USER# = IDX.OWNER# > > AND BO.USER# = BASE.OWNER# > > AND IC.OBJ# = IDX.OBJ# > > AND (IDX.OWNER# = UID OR > > BASE.OWNER# = UID > > OR > > BASE.OBJ# IN ( SELECT OBJ# > > from sys.objauth$ > > where grantee# in ( select kzsrorol > > from x$kzsro > > ) > > ) > > ) > > ; > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > San Diego, California -- Mailing list and web hosting > services> --------------------------------------------------------- > ------------ > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE- L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > > -- > Mladen Gogala > Oracle DBA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mladen Gogala > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > ------------------------------------------------------------------- > -- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
