Re: How can i tune this sql query ?

Pankaj wrote:
> Hi there,
>
> Can u pls tell me how can i tune this query ?
>
> at any time records in UnitIDTbl and INFOTBL is not more then 50000.
>
> INSERT INTO UnitIDTbl
> SELECT DISTINCT ID,INUMBER,'E-NOTFOUND'
> FROM IMPORTTBL
> WHERE LTRIM(RTRIM(ID)) || LTRIM(RTRIM(TO_CHAR(INUMBER)))
> NOT IN
> (SELECT DISTINCT LTRIM(RTRIM(ID)) || LTRIM(RTRIM(TO_CHAR(INUMBER)))
> FROM INFOTBL )
> AND (IMODE = 'N' OR IMODE = 'I' )
> AND ATYPE != 'R'
Replace RTRIM(LTRIM()) by TRIM(). Even better, make sure your data is stored trimmed. Then you can do

INSERT INTO UnitIDTbl
SELECT DISTINCT ID,INUMBER,'E-NOTFOUND'
FROM IMPORTTBL impo
WHERE NOT EXISTS ( SELECT *
FROM INFOTBL it

WHERE it.id = impo.id AND it.inumber = impo.inumber )
AND impo.IMODE IN ('N','I')
AND impo.ATYPE != 'R'

And index on (IMODE) and and index on (ID, INUMBER) may help, too. But note that due to the exclusion criterion the database is unlikely to use index on atype.

HTH     robert Received on Wed Jan 18 2006 - 11:26:41 CST