Originally posted by farrokhp
If joining of tables is used frequently, consider Composite Indexes.
Look again at that statement! It is evident that the above is pure nonsence. The following few statements are equaly (non)logical as the one above:

1. If joining of tables is used frequently, consider single column Indexes.
2. If joining of tables is used frequently, consider reverse key Indexes.
4. If joining of tables is used frequently, consider bitmap Indexes.
5. If joining of tables is used frequently, consider compressed composite Indexes.
6. If joining of tables is used frequently, consider not using indexes at all (yes, even this one might be true!).
7. If joining of tables is used frequently, consider using clustered tables.
8. If joining of tables is used frequently, consider using materialized views.

Without knowin more detail about the table structure, their size, the nature of the query, etc etc all of the above (and there could be many more) are equaly (un)logical. But generaly speaking, not any of them makes particular sence per se. If those would be one of the multi-choose question in OCP exam, I would never choose the original one. Probably 7.) or 8.) can be considered slightly more "generaly true" than the others.

The most amusing part is if you compare the original statement with my first one(using composite versus single column index). Why would be the original more logical than mine? For example, if you are joining Scott's EMP and DEPT tables frequently (and suppose there are milions of rows in there) to obtain all the employee data for a particular department, I don't think anything would be more logical but to have DEPTNO columns indexed on both tables for that particular query. Why for example to have index on DEPT+EMPNO or any other combination, it could only cause more I/O to be generated...