Archives of the TeradataForum
Message Posted: Wed, 07 Nov 2012 @ 09:23:35 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|