Re: Use index for upper(customername) like 'ABC%'

On 4 fev, 18:13, "Andrus" <kobrule(dot)(dot)(dot)(at)hot(dot)ee> wrote:
> I need to perform case insensitive search by first some characters (ABC) of
> name like
>
> SELECT ...
> FROM customer
> WHERE upper(customername) like 'ABC%'
>
> My database cluster locale is non-C
> Database encoding is UTF-8
>
> Which index I must create so that this query can use it to speed up data
> retrieval ?
>
> Andrus.

CREATE INDEX ie_cust_upper_name ON customer ( UPPER( customername ) );

If you want to perform better performance for "like 'ABC%'" statement,
you can define it on WHERE clause:

CREATE INDEX ie_cust_upper_name ON customer ( UPPER( customername ) )
WHERE ( UPPER(customername) LIKE 'ABC%' );