Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: NoPI tables as Staging tables
From:   Dave Wellman


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).



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

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023