Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002

    Unanswered: join table performance

    i have a question about join query performance:

    table A has 600000 records (id, amount)
    table B has 50 records (id, name)

    is there any difference in performance if i word the join query differently:

    query 1:

    select, name, amount
    from a, b
    where amount = 1000
    and =

    query 2:
    select, name, amount
    from a, b
    where =
    and amount = 1000

    would query 2 do a full table join first, then look for records that has amount of 1000?

    and would query 1 do a search for amount 100 first, then join the 2 tables?



  2. #2
    Join Date
    Sep 2003

    Re: join table performance

    The order of statement conditions shouldn't make any difference to the performance of the query.

    However, Oracle's execution plans are capricious in nature and tuning them to your needs is the subject of many books.

    For now, the "explain plan" command will tell you exactly which reads and joins oracle is making (and in which order) as well as giving you a cost value of the performance of your query.

    Most SQL tools have explain plan facilities, but if you are using SQLPlus, this script might help:
    Attached Files Attached Files

  3. #3
    Join Date
    Jul 2003
    the order would make a difference depending on if you have any indexes on either table and if you want to use those indexes.

    so, for best performance, match the order to either the PK or the index order.

    also, it is always helpful to those looking at your code (in a forum or at work) if you add the table-prefix on all columns. This way anyone who looks at the code knows that AMOUNT belongs to table a and not b instead of looking it up.

    Plus, what if they add those columns to the other table? That would bust all your code without the table-prefix.
    Last edited by The_Duck; 11-18-03 at 12:40.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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