Home » RDBMS Server » Performance Tuning » SQL tuning (oracle 8.0.6)
SQL tuning [message #361095] Tue, 25 November 2008 00:47 Go to next message
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
Re: SQL tuning [message #361536 is a reply to message #361095] Wed, 26 November 2008 20:13 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: SQL tuning [message #376175 is a reply to message #361536] Tue, 16 December 2008 07:24 Go to previous messageGo to next message
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
Re: SQL tuning [message #376308 is a reply to message #376175] Tue, 16 December 2008 21:38 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Post SHRINK Actions : REBUILD INDEX & CBO Stats ?
Next Topic: metric defintion for SYSMETRIC
Goto Forum:
  


Current Time: Sat Dec 03 18:24:16 CST 2016

Total time taken to generate the page: 0.16657 seconds