cancel
Showing results for 
Search instead for 
Did you mean: 

Database query question

SOLVED
Go to solution

Database query question

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.
9 REPLIES
Jean-Luc Oudart
Honored Contributor

Re: Database query question

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
fiat lux
A. Clay Stephenson
Acclaimed Contributor

Re: Database query question

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.
If it ain't broke, I can fix that.

Re: Database query question

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
Honored Contributor

Re: Database query question

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
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Database query question

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
fiat lux

Re: Database query question

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
Honored Contributor
Solution

Re: Database query question

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

JL
fiat lux

Re: Database query question

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
Honored Contributor

Re: Database query question

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)