Re: Query gives incorrect results

Actually after posting this .. I realized that Oracle seems to have truncated the filter subquery after I analyzed the SYS schema.

---snip ---
((with SYS stats)
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 3 - filter("IC"."POS#"=MIN("IC"."POS#"))
> 21 - filter("C"."NAME"=DECODE("AC"."NAME",NULL,"C"."NAME","AC"."NAME"))

--snip--
((without SYS stats))
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("IC"."POS#"= (SELECT /*+ */ MIN("IC"."POS#") FROM "SYS"."ATTRCOL$" "AC","SYS"."IND$"
> "I","SYS"."USER$" "BO","SYS"."USER$" "IO","SYS"."ICOL$" "IC","SYS"."OBJ$" "BASE","SYS"."OBJ$"
> "IDX","SYS"."COL$" "C" WHERE "IC"."BO#"="C"."OBJ#" AND
> "C"."INTCOL#"=DECODE(BITAND("I"."PROPERTY",1024),0,"IC"."INTCOL#","IC"."SPARE2") AND
> "IC"."OBJ#"="IDX"."OBJ#" AND "IDX"."NAME"=:B1 AND "BASE"."NAME"=:B2 AND "BO"."USER#"="BASE"."OWNER#" AND
> "IC"."BO#"="BASE"."OBJ#" AND "IO"."USER#"="IDX"."OWNER#" AND "BO"."NAME"=:B3 AND "IDX"."OBJ#"="I"."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) AND "C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+)) AND
> DECODE("C"."TYPE#",1,DECODE("C"."CHARSETFORM",2,'NVARCHAR2','VARCHAR2'),2,DECODE(TO_CHAR("C"."SCALE"),NULL,D
> ECODE(TO_CHAR("C"."PRECISION#"),NULL,'NUMBER','FLOAT'),'NUMBER'),8,'LONG',9,DECODE("C"."CHARSETFORM",2,'NCHA
> R VARYING','VARCHAR'),12,'DATE',23,'RAW',24,'LONG RAW',58,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME"
> FROM "OBJ$" "O" WHERE "O"."OBJ#"=:B4),"OT"."NAME"),69,'ROWID',96,DECODE("C"."CHARSETFORM",2,'NCHAR','CHAR'),
> 105,'MLSLABEL',106,'MLSLABEL',111,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME" FROM "OBJ$" "O" WHERE
> "O"."OBJ#"=:B5),"OT"."NAME"),112,DECODE("C"."CHARSETFORM",2,'NCLOB','CLOB'),113,'BLOB',114,'BFILE',115,'CFIL
> E',121,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME" FROM "OBJ$" "O" WHERE
> "O"."OBJ#"=:B6),"OT"."NAME"),122,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME" FROM "OBJ$" "O" WHERE
> "O"."OBJ#"=:B7),"OT"."NAME"),123,NVL2("SYS_ALIAS_8"."SYNOBJ#", (SELECT "O"."NAME" FROM "OBJ$" "O" WHERE
> "O"."OBJ#"=:B8),"OT"."NAME"),178,'TIME('||TO_CHAR("C"."SCALE")||')',179,'TIME('||TO_CHAR("C"."SCALE")||')'||
> ' WITH TIME ZONE',180,'TIMESTAMP('||TO_CHAR("C"."SCALE")||')',181,'TIMESTAMP('||TO_CHAR("C"."SCALE")||')'||'
> WITH TIME ZONE',231,'TIMESTAMP('||TO_CHAR("C"."SCALE")||')'||' WITH LOCAL TIME ZONE',182,'INTERVAL
> YEAR('||TO_CHAR("C"."PRECISION#")||') TO MONTH',183,'INTERVAL DAY('||TO_CHAR("C"."PRECISION#")||') TO
> SECOND('||TO_CHAR("C"."SCALE")||')',208,'UROWID','UNDEFINED')='VARCHAR2' AND ("SYS_ALIAS_9"."TYPE#"=3 OR
> "SYS_ALIAS_9"."TYPE#"=4 OR "SYS_ALIAS_9"."TYPE#"=2 AND NOT EXISTS (SELECT 0 FROM "SYS"."TAB$" "T" WHERE
> "T"."OBJ#"=:B9 AND (BITAND("T"."PROPERTY",512)=512 OR BITAND("T"."PROPERTY",8192)=8192))))
> 12 - filter("C"."NAME"=DECODE("AC"."NAME",NULL,"C"."NAME","AC"."NAME"))

... apart from the explain plan being quite different.

am I right?

Anurag Received on Mon Jun 09 2003 - 19:27:32 CDT