functional index "real world" uses

Поиск
Список
Период
Сортировка
Искать

functional index "real world" uses

От:
David Garamond <lists@zara.6.isreserved.com>
Дата:
I want to know how functional indexes are used "in the real world". Here 
are the common uses:

* non-unique index on the first parts of a longish text field 
(SUBSTRING(field)) to save disk space, while still allowing faster 
searches than a sequential scan.

* indexing on LOWER(field)/UPPER(field) to allow case-insensitive 
searches or case-insensitive unique constraint.

In what other scenario are you using functional index? Searching the 
archives, I found someone indexing a date as 'YYYYMMDD', probably to 
allow searching using LIKE 'YYYY%' or LIKE 'YYYYMM%'. I've also thought 
creating a REVERSE_DOT(field) function for IP addresses and hostnames to 
allow wildcard searches (*.yahoo.com) using 'REVERSE_DOT(f) LIKE 
'com.yahoo.%'. Has anyone done something like this?

-- 
dave
FAQ