Home » RDBMS Server » Performance Tuning » Re-structuring the SQL query
Re-structuring the SQL query [message #437507] Mon, 04 January 2010 16:33 Go to next message
decci_7
Messages: 68
Registered: March 2006
Member
Hi,

Is there any way to re-structure the SQL query to enhance the performance ... instead of using IN/NOT IN clauses, it'll use something like JOINS or NOT EXISTS clause?

I tried changing NOTIN clause to NOT EXISTS but the cost shows same for both. I was wondering if there was a better way to write the query. Any help will be much appreciated.

SELECT DISTINCT TSKIDENT.IDCODE, TSKIDENT.FIELDNBR, TSKIDENT.FIELDVALUE
FROM TSKIDENT, IDTYPE
WHERE IDTYPE.IDCODE = TSKIDENT.IDCODE
AND TSKIDENT.TSKID = 't090316000z'
AND idtype.iddesc IN
                    (SELECT idtype.iddesc
                    FROM idtype
                    WHERE idtype.casealert = 'Y'
                    AND idtype.idcode NOT IN
                                      (SELECT dptid.idcode
                                       FROM dptid, tskreq, dpt
                                       WHERE tskreq.tskid = 't090316000z'
                                       AND tskreq.dptcode = dpt.dptcode
                                       AND tskreq.dptcode = dptid.dptcode
                                       AND dptid.casealert = 'N')
                    UNION ALL
                    SELECT  idtype.iddesc
                    FROM    dptid,
                            dpt,
                            idtype,
                            tskreq
                    WHERE dptid.casealert = 'Y'
                     AND dptid.dptcode = dpt.dptcode
                     AND tskreq.tskid = 't090316000z'
                     AND tskreq.dptcode = dpt.dptcode
                     AND dptid.idcode = idtype.idcode)

Thanks!
Re: Re-structuring the SQL query [message #437509 is a reply to message #437507] Mon, 04 January 2010 16:39 Go to previous messageGo to next message
decci_7
Messages: 68
Registered: March 2006
Member
SELECT * from v$version

Quote:
BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Re: Re-structuring the SQL query [message #437511 is a reply to message #437509] Mon, 04 January 2010 17:07 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

>SELECT dptid.idcode
>FROM dptid, tskreq, dpt
>WHERE tskreq.tskid = 't090316000z'
>AND tskreq.dptcode = dpt.dptcode
>AND tskreq.dptcode = dptid.dptcode
>AND dptid.casealert = 'N')

Since the only table providing data to SELECT is DPTID, it should be the only table in FROM clause as below.

SELECT dptid.idcode                                     
FROM dptid
WHERE dptid.casealert = 'N'
 AND  dptid.dptcode IN (SELECT tskreq.dptcode 
                        FROM   tskreq
                        WHERE  tskreq.tskid = 't090316000z'
                         AND   tskreq.dptcode IN (SELECT dptcode
                                                  FROM   dpt
                                                 )
                       )


Does the rewritten SQL perform better?
post EXPLAIN PLAN for both SQL above.

[Updated on: Mon, 04 January 2010 17:20]

Report message to a moderator

Re: Re-structuring the SQL query [message #438026 is a reply to message #437511] Wed, 06 January 2010 19:47 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you handle NULLs around the NOT IN sub-query, the CBO should be able to transform it into an anti-join.
...
AND NVL(idtype.idcode, chr(1)) NOT IN
       (SELECT NVL(dptid.idcode, chr(1))
...


Ross Leishman
Previous Topic: How to tune this sql?
Next Topic: to tune an SQL query
Goto Forum:
  


Current Time: Mon Dec 05 21:22:55 CST 2016

Total time taken to generate the page: 0.11541 seconds