| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: case (in)sensitive problem
On Thu, 14 Jan 1999 15:15:15 GMT, "Matjaz Zitek" <matjaz.zitek_at_rcl.si>
wrote:
>I'm new in ORACLE and have a question presented on simple SQL SELECT
>
>SELECT * FROM customer WHERE firstname='John'
>
>How can I get all rows no matter how they are stored in table
>("JOHN","John","John") ?
>
>posible solution :
>- I tried with function UPPER but if I understand, it can't use a index on
>a field (in this case - 'firstname')
>- Store information in UPPER or LOWER
>- provide paralel field which store only UPPER value.
>
>any other solution ?
No other solutions, but the concern in your first possible solution goes away in 8i.
In Oracle8i you will be able to index a table based on a function.
eg.
Here I create an index based on the lower of a column.
SQL> create index emp_lower_idx on emp(lower(ename))
2 /
Index created.
Now I select from the table where the lower(column) = 'some value'.
You see from the plan that Oracle did do an index range scan on the table based on the index emp_lower_idx which is based on a function.
SQL> set autotrace on explain
SQL> select ename, empno, sal from emp where lower(ename) = 'king'
2 /
ENAME EMPNO SAL
KING 7839 5000 Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=40) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1
Bytes=40)
2 1 INDEX (RANGE SCAN) OF 'EMP_LOWER_IDX' (NON-UNIQUE)
(Cost=1 Card=1)
You can also do the same thing for your own function as long as they are deterministic.
Hope this helps.
chris.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |