Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle Database Delete Delete million or more common method of heap table data     - Erlang concurrency and foundation (Programming)

- Python KNN algorithm of actual realization (Programming)

- CentOS 7.0 Automatic installation CD-ROM production Comments (Linux)

- How to install Docker and basic usage on Ubuntu 15.04 (Server)

- Red Hat Linux security settings document (Linux)

- Java reflection Introduction (Programming)

- Use Visual Studio 2015 to develop Android program (Programming)

- Ubuntu 14.04 compile, install, configure, the latest development version GoldenDict (Linux)

- JavaScript Advanced Programming notes event capture and event bubbling (Programming)

- Install Xshell on Mac OS X (Linux)

- Oracle 11g upgrade PSU detailed steps (Database)

- CentOS Nginx achieve 3 virtual machine load balancing (Server)

- Json Applications of FastJson (Programming)

- C # how to generate a folder or file automatically rename (Programming)

- Shell Script: Bulk add users, and set the random character password (Programming)

- ORA-00600 error solve one case (Database)

- Detailed PHP code optimization [section] (explain reasons) (Programming)

- MySQL is configured to access external networks under Ubuntu (Database)

- MySQL Parameter Tuning Best Practices (Database)

- C ++ precision performance test function (Programming)

 
         
  Oracle Database Delete Delete million or more common method of heap table data
     
  Add Date : 2018-11-21      
         
         
         
  Demand: Oracle Database delete delete heap table ten million common history.

Remove direct impact:

1. may be due to lack of space leading to the final table undo delete to fail;

2. Possible cause undo tablespace overuse, affecting other users normal operation.

Improvement program: Each row will delete 1k submitted once. (This put a big thing split into a number of small things)

NOTE: The following methods to delete all of the records prior to 2014, for example, depending on your situation changes, to prevent misuse.

method 1

declare
    . Cursor [del_cursor] is select a *, a.rowid row_id from [table_name] a order by a.rowid;
begin
    for v_cusor in [del_cursor] loop
          if v_cusor. [time_stamp] < to_date ( '2014-01-01', 'yyyy-mm-dd') then
              delete from [table_name] where rowid = v_cusor.row_id;
          end if;
          if mod ([del_cursor]% rowcount, 1000) = 0 then
              commit;
          end if;
    end loop;
    commit;
end;

Method 1 variable Explanation:

[Del_cursor] cursor name

[Table_name] you want to delete the data table

Time Field Name [time_stamp] Your filter on table

Method 2

declare
maxrows number default 1000;
delete_ct number default 0;
begin
select count (1) / maxrows into delete_ct from [table_name] where [time_stamp] < to_date ( '2014-01-01', 'yyyy-mm-dd');
for i in 1..TRUNC (delete_ct) +1
loop
delete [table_name] where [time_stamp] < to_date ( '2014-01-01', 'yyyy-mm-dd') and rownum < = maxrows;
commit;
end loop;
end;

Method 2 variable Explanation:

[Table_name] you want to delete the data table

Time Field Name [time_stamp] Your filter on table

Note

The core idea of the two methods is a big thing to split into several small things Either way, it is recommended to test in the corresponding test environment and then consider whether you can use in the actual production.

Incidentally, such a large table should be considered whether under modified ingredients zone tables.
     
         
         
         
  More:      
 
- Oracle rebuild index script (Database)
- How to use the Vault secure password storage and API key (Linux)
- Linux Nginx installation and configuration instructions (Server)
- systemd run levels and service management command Introduction (Linux)
- MongoDB version 3.2 WiredTiger storage engine performance tests (Database)
- Red Hat Linux mount U disk (Linux)
- MNIST presentation and database conversion (Database)
- CentOS 6.5 boot automatically mount the hard drive (Linux)
- Detailed usage history command (Linux)
- How to remove the files inside the privacy of data on Linux (Linux)
- How to install Kernel 4.0.2 on CentOS 7 (Linux)
- JBPM6 Tutorial - taught you how to install JBPM (Linux)
- How to use Linux to download music from Grooveshark (Linux)
- Getting Started with Linux system to learn: how to install autossh (Linux)
- Java garbage collection (Programming)
- TOAST function in PostgreSQL (Database)
- Linux server disk expansion and Oracle tablespace file migration operations (Database)
- Linux platform NTOP Installation and Configuration (Linux)
- Physical structure and process disk IO (Linux)
- Compile and install GCC 4.8.1 + GDB 7.6.1 + Eclipse in CentOS 6.4 in (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.