When to resegment/broadcast?

If at all possible, I highly recommend it. Keep in mind if you are joining multiple tables in a query, you might not be able to eliminate every single resegment/broadcast. Do your best to do so, but at the very least get rid of the ones with the highest Cost in the explain plan, and unsegment all smaller tables (rule of thumb i follow is under 1 million rows)

Joe's recommendation is spot-on. Replicating (unsegmenting) smaller tables (< 1M rows is a good rule of thumb) can have great benefits in eliminating things like resegment and broadcast. If you find that you have hundreds, or even thousands of very small tables that are segmented, I actually have a script that I wrote that will automate that process - it will replicate all small, segmented projections. Keep in mind, that it's irreversible. PM me (is that a thing here?) or just post that you want the script, and I can reach out with the details.

Thanks Joe and Curtis..i was reading one of the old post and response was to ignore if anyway plan says Merge Join...But i agree we should as long as we can. Also it is difficult to get always Merge join based on user input and the volume of the projection. Please share your automate script if possible.