Use The Index, Luke explains the concept of function based indexes.

What can function based indexes be used for? Do you have any examples?

asked Aug 31 '10 at 11:46

Markus%20Winand's gravatar image

Markus Winand ♦♦
93651120

edited Dec 11 '11 at 16:36


3 Answers:

For queries using upper or other functions in the where clause, that cannot use "normal" B-Tree indexes:

SQL>  create index idx_func on toto (upper(value));

Index created.

SQL> select * from toto where upper(value)='TEST';
..
SQL> @last
    ---------------------------------------------------------
    | Id  | Operation                   | Name     | E-Rows |
    ---------------------------------------------------------
    |   0 | SELECT STATEMENT            |          |        |
    |   1 |  TABLE ACCESS BY INDEX ROWID| TOTO     |      1 |
    |*  2 |   INDEX RANGE SCAN          | IDX_FUNC |      1 |
    ---------------------------------------------------------

answered Nov 11 '10 at 09:01

shirleymanson's gravatar image

shirleymanson
161

Did not try, I'm wrong. This one is working:

create table users (
  id int not null primary key,
  login varchar(60) not null,
  pwhash varchar(60) not null
);
create unique index users_login_uk on users(lower(login));

INSERT into users values (1, 'foo', 'bar');
INSERT into users values (2, 'FOO', 'bar');

answered Feb 09 '11 at 12:15

Neutrino's gravatar image

Neutrino
71115

It should be possible to edit your previous answer. Let me know if that doesn't work. Generally, when somebody comments with an additional question to your response, it's best to edit your response and also add a comment. That way the answers stay clean, but the history is still understandable from the comments :)

But big thanks for participating--really!

(Feb 09 '11 at 12:26) Markus Winand ♦♦

Thanks, did not see the answer button.

(Feb 09 '11 at 12:29) Neutrino

For example, it can be used for enforcing data constraints in your database.

create table users (
  id int not null primary key,
  login varchar(60) not null,
  pwhash varchar(60) not null,
  unique(lower(login))
);

answered Feb 09 '11 at 06:01

Neutrino's gravatar image

Neutrino
71115

edited Feb 09 '11 at 06:06

Markus%20Winand's gravatar image

Markus Winand ♦♦
93651120

Yes, that's possible. But which DB accepts the create statement as you posted it?

(Feb 09 '11 at 11:16) Markus Winand ♦♦
1

Not all DBMS will allow a unique constraint to be defined based on an expression, but most will allow a unique index on them.

(Mar 25 '12 at 15:52) Castorp