re: ordering tables changes cost/plan in 9.2.0.6

Hi Cosmin,

I used the script below to reproduce the bug (#4486132). Except for the last query, no query should return any rows. If you change the order of creation of the indexes the queries produce the correct (i.e. no) results.
It was corrected in 9.2.0.7. We got a backport to 9.2.0.6.

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok) ===

DROP TABLE test
/

CREATE TABLE test
(c1 VARCHAR2(3))
/

CREATE INDEX i1 ON TEST (UPPER(c1))
/

CREATE INDEX i2 ON TEST (c1)
/

INSERT INTO TEST(c1)
VALUES('Aaa')
/

INSERT INTO TEST(c1)
VALUES('Bbb')
/

INSERT INTO TEST(c1)
VALUES('Bbb')
/

INSERT INTO TEST(c1)
VALUES('Ccc')
/

INSERT INTO TEST(c1)
VALUES('Ccc')
/

INSERT INTO TEST(c1)
VALUES('Ccc')
/

EXEC dbms_stats.gather_table_stats(USER, 'TEST');

SPOOL bugtest.lst
REM SET AUTOTRACE ON SELECT * FROM test
WHERE
  UPPER(c1) = UPPER('Aaa')
AND
  (UPPER(c1) = UPPER('Bbb')
   OR
   UPPER(c1) = UPPER('Ccc')
  )

l
/

SELECT * FROM test
WHERE
  UPPER(c1) = UPPER('Aaa')
AND
  (UPPER(c1) = UPPER('Ccc')
   OR
   UPPER(c1) = UPPER('Bbb')
  )

l
/

SELECT * FROM test
WHERE
  (UPPER(c1) = UPPER('Bbb')
   OR
   UPPER(c1) = UPPER('Ccc')
  )
AND
  UPPER(c1) = UPPER('Aaa')

l
/

SELECT * FROM test
WHERE
  (UPPER(c1) = UPPER('Ccc')
   OR
   UPPER(c1) = UPPER('Bbb')
  )
AND
  UPPER(c1) = UPPER('Aaa')

l
/

SELECT * FROM test
WHERE
  (c1 = 'Ccc'
   OR
   c1 = 'Bbb'
  )
AND
  c1 = 'Aaa'

l
/

SELECT * FROM test
WHERE
  c1 = 'Aaa'
AND
  (c1 = 'Ccc'
   OR
   c1 = 'Bbb'
  )

l
/

SELECT C1, COUNT(*) FROM test
GROUP BY c1

l
/

SPOOL OFF On Mon, 2006-10-30 at 08:32 -0800, cosmin ioan wrote:
> umm...decodes, substrings, functions, 17+ tables, views, ...ummm.....
> Oracle Applications.... need I say more? ;-)
>
> yes Carel-Jan, that would be great if you could send me the relevant
> info. I'll tackle this on multiple fronts....I've been seeing a lot of
> bugs lately in 9.2.0.6 :-)
>
> it was nice seeing you in Denmark! ;-)
> cheers,
> Cosmin
>
>
> Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl> wrote:
>
> Are there function based indexes included?
>
> I ran into a bug appr. a year ago where even the resultset of
> a query
> could change(!) if:
> A) A function based index on (UPPER(cola)) was available
> B) The FBI was created before the index on (cola) was created
> C) The where clause contained predicates on UPPER(cola)
>
> This bug was reproducable from 8i till 10gR1.
>
> It was fixed, and we got a backport to the 9i version at use
> by then.
>
> If you like I can send you the script to reproduce the bug.
> I've no
> details at hand where I am now. I can give more details later
> today if
> anyone is interested.
>
> Regards, Carel-Jan
>
> ===
> If you think education is expensive, try ignorance. (Derek
> Bok)
> ===
>
>
>
> > hello all,
> > I was under the impression that one would not have to worry
> about the
> > table order in an OPTIMIZER_MODE=CHOOSE type query. I was
> recently
> > presented with a query whereby the cost did not change that
> much, but
> > the plan did change drastically by just changing the tables'
> order in
> > the "FROM" clause.
> >
> > What can I infer from this? Is this "phenomenon" pretty
> widespread?
> > thx,
> > Cos
> >
>
>

--
http://www.freelists.org/webpage/oracle-l