Join tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Join tables

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    Is this stat correct ??

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


    Can you explain with an example


    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    It depends upon how many columns from one table are joined with the other columns of another table.

  3. #3
    Join Date
    Nov 2000
    Posts
    416
    Why it depends on that ? Can you be more specific?
    An ounce of prevention is worth a pound of cure

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2000
    Posts
    462
    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.

    Oracle DBA and Developer

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  7. #7
    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]
    ovidius over!

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width