query optimisation question

Starfield · #1 (**permalink**) 08-27-04, 06:33

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.

andrewst · #2 (**permalink**) 08-27-04, 08:02

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