Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006

    Unanswered: which is the most likekly cause for slow execution of the statement

    hello every 1
    can any one tell me ?

    the most likekly cause for slow execution of the statement.

    here are the table and qeury

    Table one
    module(module_id int, department char(15));

    Table two
    student(student_id int, name char(15), gender char(5));

    Table three
    Create table module_student(module_id int, student_id int, date_attended char(5));


    Select distinc s.student_id
    From module m, module_student ms, student s
    And s.gender=’M’
    And m.department = ‘Computer science’;

    many thanx

  2. #2
    Join Date
    May 2004
    Dominican Republic
    Here's one: Insufficient/mediocre hardware for the kind/amount of information you're asking in your querys.

  3. #3
    Join Date
    Aug 2004
    1st point:

    You are missing a join between student and module_student

    2nd point:

    The joined columns are already indexed so nothing to do here.

    3rd point:

    My guess is that the table with the most rows is student.

    4th point:

    We don't know which indexes are created, so I'll bet only primary key indexes exist. In this case indexing gender alone with a B-Tree index would not be interesting since gender has two distinct values, which is not selective. It MAY be interesting IF you mainly query on one gender (eg 'M') which would be quite rare (say 5% of the whole dataset) and you gather histogram stats on this column... Very special case, isn't it ? For such columns, bitmap indexes may be useful but you need Enterprise Edition. Department could be a good column to index but it depends on the number of departments (too few values and forget) and the number of modules (too few and Oracle will do a Full Table Scan anyway).

    I bet it's homework .

    With all that you should find out which clause will be the more likely to slow down your query. So ?

    HTH & regards,

    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Sep 2002
    Provided Answers: 1
    Re rbaraer's point 1, this (omitting join conditions) is a common novice mistake. Here are 2 ways to make it less likely:

    1) Use the ANSI JOIN syntax:

    Select distincT s.student_id
    From module m
    JOIN module_student ms ON
    JOIN student s ON ???
    Where s.gender=’M’
    And m.department = ‘Computer science’;

    It won't let you miss it out unless you explicitly ask for a CROSS JOIN.

    2) Check your joins are all present and correct by sketching a simple diagram with boxes for the tables and lines for the joins

    (The red line shows teh join that should exist but does not.)

Posting Permissions

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