Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sun, 20 Dec 2015 @ 07:53:50 GMT


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


Subj:   Re: Defining NUSI on PI
 
From:   Newell, Ray

You are only going to define a NUSI on a NUPI of a partitioned table. The reason you would do this is to avoid an all partition probe when the query only qualifies based on the NUPI column and does not also qualify on the partitioning column. In this case the optimizer knows all the rows are on a single AMP, but it does not know which partition. So all partitions must be probed. If you have thousands of partitions this can take some time. So to avoid the all partition probe you can optionally specify a NUSI on the NUPI column. The rowid stored in the NUSI subtable will contain the partition number. since the optimizer knows that the NUSI is on the NUPI it will be a single AMP NUSI access. Also be aware that the NUSI will only be used if the estimated number of rows is less than the number of partitions. If the estimated number of rows is greater than the number of partitions, the NUSI will not be used since all partitions will probably have to be probed anyways.



     
  <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