OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » SQL tuning (oracle 8.0.6)

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

SQL tuning [message #361095]

Tue, 25 November 2008 00:47

mhdmehraj
Messages: 3
Registered: November 2008

Junior Member

DELETE FROM sal_head
WHERE cm_number NOT IN (SELECT DISTINCT cm_number
FROM sal_detl)

iam using oracle 8.0.6

no of rows in sal_head=2.5lac
no of rows in sal_detl=8.5 lac
still i have index on sal_head.cm_number and sal_detl.cm_number..
..
iam getting the wait event db_file_scattered _read,,,

i have attached the expalin plan with this ,,,

please suggest me to encounter this issue..
regards,
mehraj hussain

Attachment: explainplan.JPG
(Size: 126.45KB, Downloaded 794 times)

Report message to a moderator

Re: SQL tuning [message #361536 is a reply to message #361095]

Wed, 26 November 2008 20:13

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

DELETE 
FROM sal_head
WHERE nvl(cm_number,-1) NOT IN (
    SELECT nvl(cm_number,-1)
    FROM sal_detl
)

Alternatively, make cm_number non-nullable in both tables.

Ross Leishman

Report message to a moderator

Re: SQL tuning [message #376175 is a reply to message #361536]

Tue, 16 December 2008 07:24

sibgat
Messages: 45
Registered: November 2006
Location: Chennai

Member

How Using a nvl(cm_number,-1) when used for replacing a null column with -1, will enhance the performance, (if so)

Thanks
Sibgat

Report message to a moderator

Re: SQL tuning [message #376308 is a reply to message #376175]

Tue, 16 December 2008 21:38

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

NOT IN can be resolved with an ANTI-JOIN, but not if either side of the join can be NULL. If either side is nullable, then Oracle MUST use a nested filter to process the subquery.

The NVL() enables an anti-join, which can then be un-nested.

Ross Leishman

Report message to a moderator

Previous Topic:	Post SHRINK Actions : REBUILD INDEX & CBO Stats ?
Next Topic:	metric defintion for SYSMETRIC

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 07:12:29 CST 2024