Archives of the TeradataForum

Message Posted: Wed, 05 Aug 2015 @ 08:22:50 GMT

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

Hi,

The comment "I believe for inequality conditions an FTS is always performed" is not correct.

Whether the NUSI is used or not depends on data volumes, essentially what proportion of data blocks in the table have to be read (i.e. how many rows meet the qualification criteria). The higher that number then the more likely it is that the NUSI will not be used. In some circumstances row length can also affect this decision.

Using your scenario from below, it may be that:

"cola = 5" qualifies the majority of rows on the table - the NUSI will almost certainly not be used.

"cola <> 5" qualifies a very small percentage of rows - the NUSI will probably be used.

I can quite accept that a "<>" test for a value will result in more rows being qualified and so a NUSI is less likely to be used but that is all that you can say, it is "less likely". You can make a guess - and your guess may be correct - but without testing it you cannot say for certain.

And of course for the optimiser to consistently make the 'right' decision you need statistics on the NUSI column(s).

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

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