SQL Is there a way to use Lower with out a full table scan

Question

I'm currently using lower() function in my SQL.

As per this thread, using lower() on a column will cause a full table scan: Avoid full table scan

Is there a way to prevent this or minimise the impact of the scan? I'm using Oracle 10.

My Sql:

select * from USER u where lower(u.USERNAME) = lower(?)

Answer 1

If you always search using low cases, you can create an index on the case-lowered Username, like I did in this demo.

Here the syntax from my demo:

CREATE TABLE t1
    (username varchar2(4))
;

CREATE INDEX t1_idx ON t1(lower(username));