What are the indications from the explain plan of a query that might indicate we should use a JI [ aggregate,sparse,single table or multi table ]?
Different JI has different use. If you have a table and you want to join to another table on some other column (than current PI), then you should create a Single table JI on the column you join. This will avoid the duplication or redistrubution on that table. But you need to see if that column is really used so many times to join.
Similarly of you are aggregating on some columns very frequently, you can create a AJI. Similar logic applies for multitable and sparse JI. few things to note is that, JI takes space to store and see the explain plan for see if optimizer really uses it or not and then take this call. You can read about the JI in TD manuals for details.