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

On 4 fev, 18:13, "Andrus" <kobrule...@hot.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%' );

Hello

if you have non-c locale, you have to use varchar_pattern_ops - like:

create index like_index on lidi (prijmeni varchar_pattern_ops);

Regards
Pavel Stehule

On 18/02/2008, T.J. Adami <adamitj@gmail.com> wrote:
> On 4 fev, 18:13, "Andrus" <kobrule...@hot.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%' );
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

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

ABC is different at every time.

This is first letters of customer name entered by user.
So I cannot add ABC to index.

Andrus.