0 - 2 of 2 tags for cross join

hi,
 
i want to join two tables with no columns common in them but a filter. 
SELECT COL1 FROM A JOIN B ON B.COL2 ='H2'
I am left joining this table(having 10 milliion records ) to multiple other tables . Can anybody suggest any optimisation tips on how can this be done (any index, stats ) that can help

Hi guys,
My first post here (I've used other threads to help solve problems but stuck and haven't found a solution so looking for a method or hint).
I have a table that has commitment in it (qty of units coming in against future weeks); imagine like so;