Archives of the TeradataForum

Message Posted: Sun, 12 Aug 2001 @ 09:34:07 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi everybody,

I need some ideas as well. V2R4.0 Release Summary states:

"In V2R4.0, the optimizer considers more opportunities to use a NUSI subtable to satisfy a LIKE expression. You can scan the NUSI instead of the base table to evaluate the LIKE expression."

I have a moderately complex query. The core of it is a join of PRODUCT table (5000 rows) and USAGE table (200M rows) which are joined on product_id (UPI in PRODUCT, NUSI in USAGE). PRODUCT also has product_name field with NUSI. Naturally, statistics has been collected on each and every index and column used in JOINs and WHEREs.

Now, when a user submits the query with a product selected by product_id, NUSI is used to access USAGE table and everything works fine. When, instead, a user selects a product (or a group of products) by 'product_name LIKE ...', FTS on the large table follows. Although the LIKE clause is highly selective (ie. returns just one product out of 5000) I cannot make the optimizer to use NUSI on the large table.

The problem seems to lie in interpreting the statistics. Although LIKE restricts the rows returned from PRODUCT to just one, EXPLAIN guesses that the number of rows will be 246 and FTS would be better than NUSI. How is the statistics used in case of LIKE? Is it possible to somehow force, tease or cheat? I would not like to have a join index on a 200M row table that has to be updated daily. Besides, SQL in this case is generated from a high-level tool, so different surrogate-JOIN-conditions kind of SQL tricks are also not available.

Any ideas?

Best regards,

Margus

	<Prev	Next>		<<First	<Prev	Next>	Last>>