Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Fun Challenge #2

Re: SQL Fun Challenge #2

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 4 Mar 2004 12:26:48 -0000
Message-ID: <40472088$0$22391$ed9e5944@reading.news.pipex.net> 





"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message

news:404714d3$1$26342$afc38c87_at_news.optusnet.com.au...
> Of course, I wanna see a full 10046 of that 9i SQL...
> :)



You may regret it



PARSING IN CURSOR #1 len=663 dep=0 uid=138 oct=3 lid=138 tim=69725959284
hv=1706899803 ad='2006cdfc'


with age_list as (


 select rownum age


 from all_objects


 where rownum <= 36


),


product_check as (


 select

  age1.age    as youngest,
  age2.age    as middle,
  age3.age    as oldest,
  age1.age + age2.age +age3.age as summed


 from

  age_list age1,
  age_list age2,
  age_list age3




 where


  age2.age   >= age1.age


 and  age3.age    >= age2.age


 and age1.age * age2.age * age3.age = (


   select max(age) from age_list


  )


),


summed_check as (


select


 youngest, middle, oldest, summed


from


 (


 select


  youngest, middle, oldest, summed,


  count(*) over(partition by summed) ct


 from product_check


 )


where ct > 1


)


select


 *


from summed_check


where


 oldest > middle


END OF STMT



PARSE



#1:c=50072,e=382793,p=4,cr=65,cu=2,mis=1,r=0,dep=0,og=4,tim=69725959276



so hardly any elapsed time..



and generates a check that there is no stored outline for this and the
following recursive SQL !



PARSING IN CURSOR #2 len=9421 dep=1 uid=138 oct=2 lid=138 tim=69725966653
hv=4166075977 ad='1e796404'


INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */ INTO



"SYS"."SYS_TEMP_0FD9D6600_12022C89" SELECT /*+ */ ROWNUM FROM  (SELECT /*+


*/ "U"."NAME" "OWNER" FROM "SYS"."OBJ$" "SYS_ALIAS_1","SYS"."USER$" "U"

WHERE ("SYS_ALIAS_1"."TYPE#"<>1 AND "SYS_ALIAS_1"."TYPE#"<>10 OR


"SYS_ALIAS_1"."TYPE#"=1 AND  (SELECT 1 FROM "SYS"."IND$" "I" WHERE


"I"."OBJ#"="SYS_ALIAS_1"."OBJ#" AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR


"I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR


"I"."TYPE#"=9))=1) AND ("SYS_ALIAS_1"."OWNER#"=:B1 OR


"SYS_ALIAS_1"."OWNER#"=1 OR "SYS_ALIAS_1"."TYPE#"<>11 AND

"SYS_ALIAS_1"."TYPE#"<>14 AND "SYS_ALIAS_1"."OBJ#"=ANY (SELECT

"OBJAUTH$"."OBJ#" FROM "SYS"."OBJAUTH$" "OBJAUTH$" WHERE


"OBJAUTH$"."GRANTEE#"=ANY (SELECT "X$KZSRO"."KZSROROL" FROM SYS."X$KZSRO"


"X$KZSRO") AND ("OBJAUTH$"."PRIVILEGE#"=3 OR "OBJAUTH$"."PRIVILEGE#"=6 OR


"OBJAUTH$"."PRIVILEGE#"=7 OR "OBJAUTH$"."PRIVILEGE#"=9 OR


"OBJAUTH$"."PRIVILEGE#"=10 OR "OBJAUTH$"."PRIVILEGE#"=12 OR


"OBJAUTH$"."PRIVILEGE#"=11 OR "OBJAUTH$"."PRIVILEGE#"=16 OR


"OBJAUTH$"."PRIVILEGE#"=17 OR "OBJAUTH$"."PRIVILEGE#"=18)) OR


("SYS_ALIAS_1"."TYPE#"=7 OR "SYS_ALIAS_1"."TYPE#"=8 OR



"SYS_ALIAS_1"."TYPE#"=9 OR "SYS_ALIAS_1"."TYPE#"=28 OR


"SYS_ALIAS_1"."TYPE#"=29 OR "SYS_ALIAS_1"."TYPE#"=30 OR


"SYS_ALIAS_1"."TYPE#"=56) AND  EXISTS (SELECT NULL FROM  (SELECT /*+ */


"GV$ENABLEDPRIVS"."PRIV_NUMBER" "PRIV_NUMBER" FROM  (SELECT /*+ */


"X$KZSPR"."INST_ID" "INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM


SYS."X$KZSPR" "X$KZSPR" WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS"

WHERE "GV$ENABLEDPRIVS"."INST_ID"=:B2) "V$ENABLEDPRIVS" WHERE



"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-144) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-141)) OR "SYS_ALIAS_1"."TYPE#"=12 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B3) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-152)) OR "SYS_ALIAS_1"."TYPE#"=11 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B4) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-141)) OR "SYS_ALIAS_1"."TYPE#"=22 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B5) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-189) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-190) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-191) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-192)) OR ("SYS_ALIAS_1"."TYPE#"=1 OR


"SYS_ALIAS_1"."TYPE#"=2 OR "SYS_ALIAS_1"."TYPE#"=4 OR


"SYS_ALIAS_1"."TYPE#"=5 OR "SYS_ALIAS_1"."TYPE#"=19 OR


"SYS_ALIAS_1"."TYPE#"=20 OR "SYS_ALIAS_1"."TYPE#"=34 OR


"SYS_ALIAS_1"."TYPE#"=35) AND  EXISTS (SELECT NULL FROM  (SELECT /*+ */


"GV$ENABLEDPRIVS"."PRIV_NUMBER" "PRIV_NUMBER" FROM  (SELECT /*+ */


"X$KZSPR"."INST_ID" "INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM


SYS."X$KZSPR" "X$KZSPR" WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS"

WHERE "GV$ENABLEDPRIVS"."INST_ID"=:B6) "V$ENABLEDPRIVS" WHERE



"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-45) OR "V$ENABLEDPRIVS"."PRIV_NUMBER"=(-47)


OR "V$ENABLEDPRIVS"."PRIV_NUMBER"=(-48) OR



"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-49) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-50)) OR "SYS_ALIAS_1"."TYPE#"=6 AND  EXISTS


(SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B7) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-109)) OR "SYS_ALIAS_1"."TYPE#"=13 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B8) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-184) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-181)) OR "SYS_ALIAS_1"."TYPE#"=14 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B9) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-181)) OR "SYS_ALIAS_1"."TYPE#"=23 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B10) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-177) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-178)) OR "SYS_ALIAS_1"."TYPE#"=42 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B11) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-45) OR "V$ENABLEDPRIVS"."PRIV_NUMBER"=(-47)


OR "V$ENABLEDPRIVS"."PRIV_NUMBER"=(-48) OR



"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-49) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-50)) OR "SYS_ALIAS_1"."TYPE#"=32 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B12) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-205) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-206) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-207) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-208)) OR "SYS_ALIAS_1"."TYPE#"=33 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B13) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-200) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-201) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-202) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-203) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-204)) OR "SYS_ALIAS_1"."TYPE#"=44 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B14) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-222) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-223)) OR "SYS_ALIAS_1"."TYPE#"=48 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B15) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=12) OR "SYS_ALIAS_1"."TYPE#"=46 AND  EXISTS


(SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B16) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-251) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-252) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-253) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-254)) OR "SYS_ALIAS_1"."TYPE#"=59 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B17) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-65) OR "V$ENABLEDPRIVS"."PRIV_NUMBER"=(-66)


OR "V$ENABLEDPRIVS"."PRIV_NUMBER"=(-67) OR



"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-68)) OR "SYS_ALIAS_1"."TYPE#"=62 AND


EXISTS (SELECT NULL FROM  (SELECT /*+ */ "GV$ENABLEDPRIVS"."PRIV_NUMBER"



"PRIV_NUMBER" FROM  (SELECT /*+ */ "X$KZSPR"."INST_ID"


"INST_ID",(-"X$KZSPR"."INDX") "PRIV_NUMBER" FROM SYS."X$KZSPR" "X$KZSPR"


WHERE "X$KZSPR"."KZSPRPRV"=1) "GV$ENABLEDPRIVS" WHERE



"GV$ENABLEDPRIVS"."INST_ID"=:B18) "V$ENABLEDPRIVS" WHERE


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-246) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-247) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-248) OR


"V$ENABLEDPRIVS"."PRIV_NUMBER"=(-249))) AND


"SYS_ALIAS_1"."OWNER#"="U"."USER#" AND "SYS_ALIAS_1"."LINKNAME" IS NULL AND


"SYS_ALIAS_1"."NAME"<>'_default_auditing_options_' AND

"SYS_ALIAS_1"."NAME"<>'_NEXT_OBJECT') "ALL_OBJECTS" WHERE ROWNUM<=36




well you did ask. 9204 on windows.
Received on Thu Mar 04 2004 - 06:26:48 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US