All Forums Database
m.tahoon 43 posts Joined 09/11
21 Sep 2014
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 ??

 

 

 

 

 

Raja_KT 1246 posts Joined 07/09
21 Sep 2014

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.