Join tables

[farrokhp]

Is this stat correct ??

If joining of tables is used frequently, consider Composite Indexes.


Can you explain with an example

[tamilselvan]

It depends upon how many columns from one table are joined with the other columns of another table.

[farrokhp]

Why it depends on that ? Can you be more specific?

[jmodic]

Originally posted by farrokhp
Is this stat correct ??

If joining of tables is used frequently, consider Composite Indexes.

Generaly speaking, that statement is a nonsence.

[kmesser]

I will certainly defer to jmodic, but I think I can see some possible logic here. Let us suppose there are multiple tables which are frequently joined on the same columns. Many times it is useful to index predicate (where clause) columns. If the same columns are routinely used to join the tables, then if other normal criteria for indexing apply (selectivity, table size . . . ) then indexing all those columns in one index might not be bad. Data models and performance tuning are, after all, evolving objects. We don't just create them "perfect" and never revisit. As usage patterns change, possibly additional or reduced indexing may then be necessary.

Of course, you should always start with well designed queries to maximize performance. No amount of indexing will save you from inefficient or erroneous query design.

[julian]

Originally posted by jmodic

Originally posted by farrokhp
Is this stat correct ??

If joining of tables is used frequently, consider Composite Indexes.

Generaly speaking, that statement is a nonsence.

Yes it is. Somebody might ask why. When a composite index on the larger table of a join does not include the join condition as the leading part of the index, then that index on the subsequent part of the query must be forced or Oracle will perform a full table scan on all tables.

[ovidius]

9i: if you have some tables that joins in queries very frequent, consider cluster table.



-------------
PS:
Julian, I cannot undestand what have the future plans of Oracle regarding clustered tables with this. I do not think Oracle will take them out. But what do you want they to do with them??? Paint them in pink?
I just said it is a good idea that is functioning properly on 9i. As long as I do not use them on 8i, I do not have the required experience to recommend them for 8i.

[Edited by ovidius on 04-10-2002 at 04:15 AM]

[julian]

Originally posted by ovidius
9i: if you have some tables that joins in queries very frequent, consider cluster table.

Oracle keeps cluster tables only for backward compatability and Oracle will not make any improvements on cluster tables in future releases.

[jmodic]

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...