Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    20

    Unanswered: query optimisation question

    I have a quick question regarding a query I'm working on. I was wondering why the first one runs much quicker than the second as I can't understand it myself.

    Query 1:

    SELECT pi.jjobno,
    pi.jpi06,
    pi.jq01,
    pi.jq02,
    pi.jq03,
    pi.jq04,
    pi.jq05,
    pi.jq06,
    pi.jq07,
    pi.jq08
    FROM jpost_insp pi
    WHERE pi.jinspected_date IS NOT NULL
    AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
    FROM jpost_insp
    GROUP BY jjobno)
    AND pi.jjobno = _job_no
    AND ROWNUM = 1


    Query 2:


    SELECT pi.jjobno,
    pi.jpi06,
    pi.jq01,
    pi.jq02,
    pi.jq03,
    pi.jq04,
    pi.jq05,
    pi.jq06,
    pi.jq07,
    pi.jq08
    FROM jpost_insp pi
    WHERE pi.jinspected_date IS NOT NULL
    AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
    FROM jpost_insp
    WHERE pi.jjobno = _job_no
    GROUP BY jjobno)
    AND pi.jjobno = _job_no
    AND ROWNUM = 1


    The only difference is that in query 2 I have included a where clause in the subquery. The field jjobno is an indexed field so surely that by specifying an exact jjobno in an index field this would be quicker than specifying nothing? Could someone explain this to me? I'm using oracle version 7.3. Thanks in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You have (accidentally I presume) correlated the subquery to the main query in the second version by referring to "pi.jjobno". Alias "pi" is defined in the main query. Perhaps you mean to do this:
    Code:
     SELECT pi.jjobno,
    pi.jpi06,
    pi.jq01,
    pi.jq02,
    pi.jq03,
    pi.jq04,
    pi.jq05,
    pi.jq06,
    pi.jq07,
    pi.jq08
    FROM jpost_insp pi
    WHERE pi.jinspected_date IS NOT NULL
    AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
    FROM jpost_insp pi2
    WHERE pi2.jjobno = :p_job_no
    GROUP BY jjobno)
    AND pi.jjobno = :p_job_no
    AND ROWNUM = 1

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Of course, the hint needs changing now!

  4. #4
    Join Date
    Jun 2003
    Posts
    20
    Good spot I missed that completely. Why would I have to change the hint?

    thanks,

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, either change the hint to use alias pi2, or change the table alias from pi2 to jpost_insp. They have to be the same in both places!

Posting Permissions

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