Archives of the TeradataForum

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

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

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