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(?)

How about an extra column storing the lowercase version? Or if you never care about case you can turn case sensitivity off. — Ash Burlaczenko, Dec 30 '13 at 22:51
possible duplicate of [Case insensitive searching in Oracle](http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle) — Barmar, Dec 30 '13 at 22:52
@AshBurlaczenko The obvious purpose of the comparison in his question is to perform a case-insensitive match. — Barmar, Dec 30 '13 at 22:56
@Barmar, thanks for the link. This question is based more around different ways to prevent a full table scan knowing how to do a case-insensitive match. While that question is based more around how to do a case-insensitive scan. — JackDev, Dec 30 '13 at 23:09
One answer did make mention of function based index, I'm also interested in other ways aside from that. — JackDev, Dec 30 '13 at 23:15

mucio · Accepted Answer · 2013-12-30T23:18:08.637

6

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));

edited Dec 30 '13 at 23:18

answered Dec 30 '13 at 22:55

mucio

2

It would be better to include the syntax to do that, not just link to it; this answer isn't much use if SQL Fiddle is down (or blocked). A [link to the docs](http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5012.htm#sthref4484) for function-based indexes wouldn't hurt either for some background. – Alex Poole Dec 30 '13 at 23:10
@AlexPoole you are right, I have edited my answer to include the code – mucio Dec 30 '13 at 23:18

1 Answers1