On 4 fev, 18:13, "Andrus" <[EMAIL PROTECTED]> 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
