sql - Optimize delete operation on table with millions of records -

-

I have a table with several columns "Reason" is one of the non-indexed columns, which is the repeated value It is possible. There are two million records in my table. I want to delete rows based on a particular reason, for example "MY REASON", while "MY REASON" has around 15 records.

When I use a standard deleted query as shown below, too much time (approximately 3 or 4 hours). 

  Remove from my_table where reason = 'my reason'; Committed;   
 How can I raise it?  
 This is the execution plan for that:  
  plan hash value: 2164670663 | ID | Operation | Name | Rows | Byte | Cost (% CPU) | Time | -------------------------------------------------- ------------------------------------- | 0 | Delete Statement | | 6190 175K | 101 9 (0) | 10:30:49 | | 1 | Delete | MY_TABLE | | | | | | * 2 | Table access complete. MY_TABLE | 6190 175K | 101 9 (0) | 10:30:49 | -------------------------------------------------- ------------------------------------- Predefined Information (Identification by Operation ID): ---- ----------------------------------------------- 2 - Filters ("REASON" = 'MY REASON')   
 Data for 10rows deletion using the primary key ID:  
  SQL & gt; Set the time; SQL & gt; Set the autotrezes on the data conveying; SQL & gt; Remove from My_table where the id between 194404 and 194414; 11 rows expired: 00: 00: 1 9.33 execution plan -------------------------------- --------- ----------------- Plan hash value: 2403087528 ------------------- --------- ----------------------------------------- --------- --------- | ID | Operation | Name | Rows | Byte | Cost (% CPU) | Time | -------------------------------------------------- ------------------------------------- | 0 | Delete Statement | | 1 | 29 | 1 (0) | 00:00:38 | | 1 | Delete | MY_TABLE | | | | | | * 2 | Index Range Scan | PK_MY_TABLE | 1 | 29 | 1 (0) | 00:00:38 | -------------------------------------------------- ------------------------------------- Predefined Information (Identification by Operation ID): ---- ----------------------------------------------- 2 - access ("Id"> = 194404 and "id" & lt; = 194414) Statistics ------------------------------ - -------------------------- 94 recursive calls 66 becomes DB block 602170 becomes compatible 0 physical reads 8844 Reado size 528 Bytes are sent from SQL * Client 372 Byte SQL * Net received from client 4 to SQL * Client 7/7 times from memory Mortality) Type 0 (disk) 11 rows processed SQL & gt;   
  * The points fixed after adding the child to the table ***  The details have been explored below:  
  Table after adding index for child --------------------------------- SQL & gt; Set the time; SQL & gt; Set the autotrezes on the data conveying; SQL & gt; Remove from MY_TABLE where the ID is between 194241 and 193636; 121 rows have been erased: 00: 00: 01.37 Execution plan ---------------------------------- ------- ----------------- Plan hash value: 2403087528 --------------------- ------- ------------------------------------------- ------- --------- | ID | Operation | Name | Rows | Byte | Cost (% CPU) | Time | -------------------------------------------------- ------------------------------------- | 0 | Delete Statement | | 1 | 29 | 1 (0) | 00:00:38 | | 1 | Delete | MY_TABLE | | | | | | * 2 | Index Range Scan | PK_MY_TABLE | 1 | 29 | 1 (0) | 00:00:38 | -------------------------------------------------- ------------------------------------- Predefined Information (Identification by Operation ID): ---- ----------------------------------------------- 2 - access ("Id"> = 194241 and "id" & lt; = 194361) Statistics ---------------------------- ---- -------------------------- 3 recursive calls becomes 760db block 7 7 physical reads 83968 reedo size 527 byte sql * Net is sent by client * 372 Byte SQL received by client * Net * 5 / net roundtrrips 5 types (client) Re) 0 type (disk) 121 rows processed    
 
 
 On foreign key barriers Check indexed Not only in your  my_table , but in all your databases  
 create missing indexes (one with explicitly temporary names, so that you can easily drop them later )  
 Then try to turn your  delete  back on.

Comments

Post a Comment

Popular posts from this blog

php - PDO bindParam() fatal error -

-
I am trying to learn PDO, now I have written this small code, but it gives me a serious error: Fatal error: Call a member function at a non-object dot () ... $ con = new mysqli ("127.0.0.1", "root", "", "csvdangercheck"); $ Query = $ con- & gt; ("Id,` var1`, `var2`) values ​​(" id,: var1,: var2) ";); $ query- & gt; bypass pattern (': id', $ id); $ query- & gt; BindParam (': var1', $ VAL1); $ Query- & gt; bindParam (': var2', $ val2); $ Query-> Executed (); I Trying to use print_r ($ con-> errorInfo ()) but Fatal error: Undefined method mysqli :: errorInfo ( ) ... can someone tell me what am I missing? Flaffeh said, you have PDO Missing Mysqy, try it: $ con = new PDO ('mysql: host = 127.0.0.1; dbname = csvdangercheck', 'root', '');
Read more

php - How can I cram 6+31 numeric characters into 22 alphanumeric characters? -

-
I have a 6 digit number and a 31-digit number (such as "234536" and "201103231043330478311223582826") that I The API needs to be crawled in the same 22-character alphanumeric field that uses PHP. I tried to convert each one to 32 (using custom code could not handle the base_convert () large number) and to connect with a single-character delimiter, but It only gives me 26 characters This is a restore API, so the letters should be URI-protected. I would like to do a database table cross with two references without reference to another reference value, if possible, any suggestions? Instead use a radix of 62. This will give you 22 characters for the top, 3.35 for the pre and 17.3 for the next. & gt; & Gt; & Gt; Math.log (10 ** 6) /math.log (62) 3.3474826039165504 & gt; & Gt; & Gt; Math.log (10 ** 31) /math.log (62) 17.295326786902177
Read more

logging - How can I log both the Request.InputStream and Response.OutputStream traffic in my ASP.NET MVC3 Application for specific Actions? -

-
For a specific set of tasks, I need to log in to the incoming request inputstream as well as outgoing response. is. I am using an ActionFilterAttribute for this and overriding the OnActionExecuted and OnResultExecuted methods. So I'm starting with this idea ... Public category ActionLoggerAttribute: ActionFilterArrbit {Public Override Zero On-Offed Execution (ActionActioned Contact Filter Filter) {base.OnActionAxecuted (FilterContext); HttpRequestBase request = filterContext.HttpContext.Request; // TODO: Log the request. InputStream} Public Override Zero OnResultExecuted (ResultExecutedContext FilterContext) {base.OnResultExecuted (filterContext); HTTPRPSPointBase Response = FilterContacts Hpptex response; // TODO: Log on Response.OutputStream}} Ideally I'll hook it up with enterprise library logging because I'm already using it for error logging. Am I reaching the input and output stream at the right time? Can I easily use the Enterprise Library to log i...
Read more