Hash Index To Eliminate Redistribution Only


Hash Index To Eliminate Redistribution Only

i want to use Hash index to Eliminate Redistribution only not to fetch the column Data.

i'm using the following DDL:

CREATE HASH INDEX  DevDB.AMSH_HshInd , NO FALLBACK (Access_Method_Id)  ON DEVDB.METH BY (Access_Method_Id) ORDER BY HASH (Access_Method_Id) ;

Access_Method_Id is the column i'll use to join DEVDB.METH  with other tables.

but DEVDB.METH   have another PI which is subscription_id.

in my Case teh Hash index only picked when i only select columns [subscription_id, Access_Method_Id] from table DEVDB.METH, which here works as a typical Self-Join  index.

however, i need to have that index used to eliminate Redistribution on Access_Method_Id [not PI] when it's used for any join with other table.

i'm colelcted stats on both the HJI and base table.

do i have any Misunderstanding here ??


Re: Hash Index To Eliminate Redistribution Only

I am of the opinion that you use STJI instead of HJI. It is good that you collect stats too. You join with other tables, so you know the joining in mind. You can run explain and see how it behaves.