Database query question

[Brian Gebhard_1]

I have a case sensitive column in a two different tables. When I try to perform a join on those columns I'm using an UPPER command. Once that happens oracle no longer uses the index on that column. I sorta remember reading about performing functions on a column in a query causes oracle to ignore indexes but I can't find where I read it. Can anyone help me find a work around for this problem?

thanks.

[Jean-Luc Oudart]

You can use a function index sucha as :
CREATE INDEX emp_name_index ON emp ( UPPER(ename)) ;

I think Oracle Enterprise Edition is required for this feature.

Rgds,
Jean-Luc

[A. Clay Stephenson]

Probably the most direct approach is to create a second indexed column with triggers to upper() the characters in the second column when those in the original column are altered. You then join on the second column.

A few databases would allow you to use mixed-case data in the column but the index would be allowed to ignore case for comparisons so that you got the best of both worlds --- but this doesn't apply to you.

[Brian Gebhard_1]

Thanks Jean-luc but oracle still ignores the index. It's a basic query, with upper in the query or in the index creation oracle uses the index.

select *
from table1 a, table2 b
where a.unit_cd = '10a'
and a.unit_cd = b.cost_code;

I'll have to keep looking or see if the developers want to add a 2nd column.

[Jean-Luc Oudart]

I have a database that used to run Oracl 734 and we had to have a separate column.
Waste of space !
You probably need a trigger to populate the extra column and probably and index 2 for performance.

Could you force the index with SQL Hints ?

Rgds,
Jean-Luc
PS : nice salad on the head... I suppose the buggy pb at the moment

[Jean-Luc Oudart]

Just a second ,
did you try your query with :


select *
from table1 a, table2 b
where Upper(a.unit_cd) = '10A'
and Upper(a.unit_cd) = Upper(b.cost_code);

with
CREATE INDEX TAB1IDX ON TABLE1 (UPPER(unit_cd));
and
CREATE INDEX TAB2IDX ON TABLE2 (UPPER(cost_code));

What is the output of the explain plan ?

Rgds,
Jean-Luc

[Brian Gebhard_1]

Ok these are the tests I ran:

select *
from table1 a, table2 b
where a.cost_code = b.unit_cd
and b.unit_cd = '10a';

1. UPPER not on any index or in query, oracle users both indexes on both columns

2. Add UPPER to b.unit_cd in query and it uses both indexes, but since I need to upper cost_code this doesn't help. Add UPPER to cost_code with or without UPPER on unit_cd and oracle doesn't user cost_code's index but does user unit_cd's index.

3. Add UPPER to cost_code index. Oracle uses unit_cd's index but not cost_codes index. Add UPPER to the query while UPPER is also in the cost_code index and oracle still only uses unit_cd's index.

4. Add upper to cost_code and unit_cd's indexes and oracle never uses any of the indexes with or without UPPER in the query.

For some reason oracle is just using unit_cd's indexes. I took out the unit_cd = '10a' and oracle still uses the unit_cd index.

Any other suggestions? It just doesn't seem to be working the way it should.

[Jean-Luc Oudart · Accepted solution]

What about using SQL hint ?
select /*+ INDEX(table2[unit_cd_index]) */

JL

[Brian Gebhard_1]

ok, well it appears that I need to create the index with UPPER in it, run the query with UPPER in it and use the index hint. Otherwise it doesn't work. Thank both of you for your help.

[Yogeeraj_1]

hi,
*
if you are using the CBO, also make sure your statistics are up to date.
*
You may wish to re-compute them using:
++++++++++++++++++++++++++++++++++++++
analyze table compute statistics
for table
for all indexed columns
for all indexes;
++++++++++++++++++++++++++++++++++++++
*
Hope this helps!
*
regards
Yogeeraj