Hi all

I'm trying to get a query to run a bit quicker and I know it can, but
don't know how to let the optimizer know that.

Now I will try and include some detail.... It looks like I posted before
I was ready.

The SQL I have is

Query 1:
WITH ORDS AS (SELECT DISTINCT dec (GLPO) glpo ,glpdct FROM F0911SB L
WHERE GLMCU=' PJIC700815' AND GLICUT ='O' )
SELECT PRDOC,PRDCT, PRDOCO,PRDCTO,PRLITM,PRVRMK,
TEMPLA.JTODATE(PRDGL) GLDATE,
CAST((PRQTYS/1000) AS DECIMAL(11,3) ) QTYREC ,
CAST((PRAREC/100) AS DECIMAL(11,2) ) AMOUNT ,
R.*
FROM ORDS JOIN F43121LU R ON (R.PRMATC='1' AND R.PRDOCO=GLPO AND
R.PRDCTO=GLPDCT) ;

This take about 30 seconds to return maybe 50 rows.

If I separately run these statements, they take less than 1 second each

Query2:

SELECT DISTINCT dec (GLPO),glpdct FROM F0911SB L WHERE GLICUT ='O' AND
GLMCU=' PJIC700815';

then using the results to select upon...


Query 3:
SELECT PRDOC,PRDCT, PRDOCO,PRDCTO,PRLITM, PRVRMK, TEMPLA.JTODATE(PRDGL)
GLDATE, CAST((PRQTYS/1000) AS DECIMAL(11,3) ) QTYREC ,
CAST((PRAREC/100) AS DECIMAL(11,2) ) AMOUNT , R.* FROM F43121 R
WHERE PRMATC ='1' and ((prdoco = 8000661 and prdcto='OS') or (prdoco
= 8000976 and prdcto='OI') or (prdoco = 8001132 and prdcto='OS') or
(prdoco = 8001150 and prdcto='OS') or (prdoco = 8001151 and
prdcto='OS') or (prdoco = 8001285 and prdcto='OS') or (prdoco =
8001294 and prdcto='OS') or (prdoco = 8001429 and prdcto='OP'));

Looking at visual explain; running this last query, the optimizer uses
an index scan with an access path that uses the fields prmatc,prdcto and
prdoco. Running query 1 uses a table scan and a nested loop - it doesn't
use the access path. I know that it would be better doing so.

Is there any tricks I can use to convince it?

Thanks,

--
Andy Templeman