Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

Message Posted: Wed, 07 Nov 2012 @ 09:23:35 GMT


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


Subj:   Re: Query on NUSI
 
From:   Pluebell, Fred

Re: "hashed NUSI"

For a standard NUSI

     CREATE INDEX (DEPTNO) ON EMPLOYEE;

Each index entry logical row has a DEPTNO followed by a list of ROWIDs. The index entry and the ROWIDs are all AMP-local. To find Employees in DeptNo 123, you have to do an all-AMPs scan of the index and then use the ROWIDs locally to access the base table rows on the same AMP.

Alternatively, a simple JI

     CREATE JOIN INDEX MyJI as SELECT (DEPTNO), (ROWID) FROM EMPLOYEE PRIMARY INDEX (DEPTNO);

The logical rows again have DEPTNO followed by a list of ROWIDs, like the NUSI; but now those logical rows are hashed based on DEPTNO and the ROWIDs can point to any AMP. To find Employees in DeptNo 123, you would do a PI access to the one AMP with the JI row(s) for DeptNo 123, and then send / redistribute the ROWIDs to the AMPs where the base table rows reside.



     
  <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: 28 Jun 2020