Oracle 9i usage question

From: Raghvendra Sharma <sharma.raghvendra_at_gmail.com>
Date: 3 May 2005 14:03:24 -0700
Message-ID: <c79041e9.0505031303.6221426@posting.google.com>

Hi All,

We recently migrated to 9i from 8i (8.1.7.4)

Lets say T1 and T2 both have a join key column called root_key. In one table the datatype ofr the column is varchar2(50) and in the other one its number(18). The content is just fine.

the application uses heavily the join on these two tables. Till Oracle 8i, this join condition was resolved using appropriate indexes built on these two tables.

However, during our tests also and since last two days on ACCEPT also, the explain plan does not use the index on tables. Instead it goes for a FTS.

When I spoke to a colleague in DBA team, he vaguely pointed to the new optimizer in Oracle 9i which does not allow using indexes if the data type of the two fields in the join condition is not same.

I could not find much evidence prooving this.

Seeking help in this situation. Is this really the case ? If yes, how come things were working in 8i. Is there a remedy (fix) other than fixing the datatype of the columns (which might have other applicative impacts)

Requesting solution/suggestions/pointers..

regards,
raghav.. Received on Tue May 03 2005 - 16:03:24 CDT