Results 1 to 6 of 6

Thread: Multiple joins

  1. #1
    Join Date
    Jan 2004
    Location
    Detroit, MI
    Posts
    17

    Question Unanswered: Multiple joins

    Is there a heavy performance cost assiciated with executing a SQL statment that joins multiple tables?

    Like this one for example, where I join 4 tables...

    select e.first_name "First Name", e.last_name "Last Name",
    e.email "E-Mail", e.phone_number "Phone Number",
    e.hire_date "Hire Date", j.job_title "Job Title",
    e.salary "Salary", nvl(e.commission_pct,0) "Commision %",
    m.first_name || ' ' || m.last_name "Manager",
    d.department_name "Department"
    from employees e, employees m, jobs j, departments d
    where e.employee_id=100 and
    e.job_id = j.job_id and e.department_id = d.department_id
    and e.manager_id = m.employee_id(+);

    I am curious to know if there is a preferred way to do something like this.

    Thanks in advance.
    Jamin
    Last edited by jamin0; 02-07-04 at 20:37.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > I am curious to know if there is a preferred way to do something like this.
    It should not matter if you are using the CBO & all tables & indexes have current statistics.

  3. #3
    Join Date
    Jan 2004
    Location
    Detroit, MI
    Posts
    17
    Originally posted by anacedent
    > I am curious to know if there is a preferred way to do something like this.
    It should not matter if you are using the CBO & all tables & indexes have current statistics.
    I'm not exactly sure what that meant but I think it means no. Thanks for the quick reply!

    Jamin

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you are using the RBO, the actual SQL can impact performance.
    So, it depends.

  5. #5
    Join Date
    Jan 2004
    Location
    Detroit, MI
    Posts
    17
    Originally posted by anacedent
    If you are using the RBO, the actual SQL can impact performance.
    So, it depends.
    What is the RBO? I am new to SQL so I am not nearly up to date with all the acronyms.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    CBO - Cost Based Optimizer
    RBO - Rule Based Optimizer

Posting Permissions

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