OraFAQ Forum: Performance Tuning » Re-structuring the SQL query

Home » RDBMS Server » Performance Tuning » Re-structuring the SQL query

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Re-structuring the SQL query [message #437507]

Mon, 04 January 2010 16:33

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!

Report message to a moderator

Re: Re-structuring the SQL query [message #437509 is a reply to message #437507]

Mon, 04 January 2010 16:39

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

Report message to a moderator

Re: Re-structuring the SQL query [message #437511 is a reply to message #437509]

Mon, 04 January 2010 17:07

BlackSwan
Messages: 26766
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

rleishman
Messages: 3728
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

Report message to a moderator

Previous Topic:	How to tune this sql?
Next Topic:	to tune an SQL query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 13 14:22:04 CDT 2024