Archives of the TeradataForum
Message Posted: Tue, 19 Aug 2003 @ 09:41:57 GMT
| Subj: | | Re: How to handle LIKE operators while querying Customer tables? |
| |
| From: | | Dieter Noeth |
Anomy Anom wrote:
| | We have a big customer table which some of our Call Center operators access using a Web front end. They usually type in the last
name which gets generated as a SELECT query on the table as | |
| | sel col1, col2, col3 from custtable where last_name like '%EDWARD%'. | |
| | This is a good functionality for them as they get all the records but it is FTS which is costlty in terms of perfomance. Are there
any options to improve performance. | |
| | Users are bound to use the last name for querying this table and always likely to use LIKE operator. | |
A like with '%' as first char will result in any DBMS, but 'EDWARD%' might use an index. Unfortunatly the Teradata optimizer is
rarely using an index for that. Try createing a NUSI and COLL STATS on it, maybe you got luck.
And you could try to replace "LIKE 'EDWARD%'" with "last_name BETWEEN 'EDWARD' AND 'EDWARDZZ'".
Another way would be to maintain an extra indexed column with the first x chars of a name and require the users to type in at least x
chars:
e.g.
column last_name_short (char(4))
WHERE last_name_short = 'EDWA'
and last_name LIKE 'EDWA%'
WHERE last_name_short = SUBSTRING('EDWARD' FROM 1 FOR 4)
and last_name LIKE 'EDWARD%'
Dieter
| 
