SEO

#1 (**permalink**) 02-24-2008, 01:09 PM

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..

#2 (**permalink**) 02-24-2008, 01:09 PM

Raghvendra Sharma wrote:
> 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.

What are the tables structures. Primary keys? foreign keys?

> 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.

What indexes (on which columns)?

> 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.

could you post the SQL you are testing? maybe the explain plan too?

Regards,
Koki

#3 (**permalink**) 02-24-2008, 01:10 PM

Investigate the use of a function-based index.
This feature is available in 9i Release 2 on Both Enterprise and
Standard Edition.
There are numerous articles discussing this, I won't duplicate that
here.
Here is one such site:

http://www.jlcomp.demon.co.uk/faq/ca...ive_query.html

-bdbafh

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Oracle 9i usage question