Home » SQL & PL/SQL » SQL & PL/SQL » delete query taking lot of time (oracle 10g)
delete query taking lot of time [message #404877] Mon, 25 May 2009 02:26 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
hi,
this is my oracle delete query.it is taking lot of time.is there any other alternative way to write this query for performance
--Qry1
DELETE EMRAuditTrial
WHERE (USER_LOGIN IN ('superadmin','ezemrx','ezemrx')
OR USER_LOGIN      IN
  (SELECT DISTINCT USER_LOGIN FROM EMRUSERROLELOCATION WHERE LOCATION_ID = 1512
  ) )
AND EVENT_ID IN
  (SELECT event_id FROM EMRAuditEventLkup WHERE EVENT_TYPE IN (0,2)
  )
AND MODULE_ID IN
  (SELECT MODULE_ID FROM EMRAuditModuleLkup WHERE MODULE_TYPE IN (0,2)
  )
AND MODULE_ID IN (1059,1190,1106,1077,1186,1062,1154,1075,1076,1064,1060,1073,1070,1068,1041,1079,1080,1082
,1081,1078,1044,1043,1042,1072,1145,1146,1046,1045,1050,1052,1048,1047,1051,1049,1066,1085,1083,1084,1179
,1087,1086,1191,1089,1074,1021,1061,1101,1092,1187,1065,1188,1069,1135,1134,1153,1028,1057,1111,1141,1113
,1112,1114,1115,1116,1117,1139,1128,1126,1144,1118,1119,1125,1142,1140,1123,1122,1124,1143,1120,1121,1127
,1110,1109,1025,1031,1023,1024,1030,1026,1032,1132,1137,1138,1056,1029,1019,1027,1129,1130,1100,1022,1058
,1105,1192,1071,1020,1103,1063,1067,1185)
--Qry2



[Mod-Edit: Frank reduced the line-length]

[Updated on: Mon, 25 May 2009 02:39] by Moderator

Report message to a moderator

Re: delete query taking lot of time [message #404878 is a reply to message #404877] Mon, 25 May 2009 02:30 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

How do we know whether your query is taking lot of time, do you have any evidences to suggest
Re: delete query taking lot of time [message #404879 is a reply to message #404877] Mon, 25 May 2009 02:40 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
rajasekhar857 wrote on Mon, 25 May 2009 09:26
hi,
this is my oracle delete query.it is taking lot of time.is there any other alternative way to write this query for performance
--Qry1
DELETE EMRAuditTrial
WHERE (USER_LOGIN IN ('superadmin','ezemrx','ezemrx')
OR USER_LOGIN      IN
  (SELECT DISTINCT USER_LOGIN FROM EMRUSERROLELOCATION WHERE LOCATION_ID = 1512
  ) )
AND EVENT_ID IN
  (SELECT event_id FROM EMRAuditEventLkup WHERE EVENT_TYPE IN (0,2)
  )
AND MODULE_ID IN
  (SELECT MODULE_ID FROM EMRAuditModuleLkup WHERE MODULE_TYPE IN (0,2)
  )
AND MODULE_ID IN ( ... list of numbers ... )



Not much info to go on:
- No number of records in each table
- The (non-)existance of indices on certain table/columns

So here is an answer that can help, or not help at all Wink

AND MODULE_ID IN
  (SELECT MODULE_ID FROM EMRAuditModuleLkup WHERE MODULE_TYPE IN (0,2)
  )
AND MODULE_ID IN ( ... list of numbers ... )



Could imho be rewritten as:

AND MODULE_ID IN
  (
  SELECT MODULE_ID
  FROM EMRAuditModuleLkup
  WHERE MODULE_TYPE IN (0,2)
  AND MODULE_ID IN ( ... list of numbers ... )
  )


But no guarantee if it would speed up your statement.

EDIT: got rid of that list of numbers

[Updated on: Mon, 25 May 2009 02:41]

Report message to a moderator

Re: delete query taking lot of time [message #404881 is a reply to message #404877] Mon, 25 May 2009 02:41 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
IN does not need DISTINCT in a sub-query.
That's about the only thing I can tell you about your query.
If you decide not to tell us the structure and distribution of your data, the number of records involved, the execution plan or whatever, how do you expect us to help you?
Previous Topic: I want to get source of all procedures of database.
Next Topic: Executing subprocedure
Goto Forum:
  


Current Time: Sat Dec 03 18:08:19 CST 2016

Total time taken to generate the page: 0.04124 seconds