-
P.S.: can't do bulk insert since rows are created in those tables non-stop. If I do bulk insert I'll lose rows that are created when create a new table --> move the desired records --> delete old table --> rename new tables
-
Originally Posted by crysu
1. No
2. Between 70 and 90%
This is a text book case where you may want to consider deleting data by doing inserts instead of doing deletes.
Here is the scenario...
You want to delete 70%+ of the rows sitting on table_a
1- Rename table_a as table_b
2- Create table_a as select * from table_b where v_column_name > sysdate - v_retention_days
3- Build indexes
4- Gather fresh stats.
5- After business approval drop table_b
This is faster, generates less redo and ensures tables and indexes are not fragmented at the end of the process.
Can you afford a maintenance window to do it?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by crysu
P.S.: can't do bulk insert since rows are created in those tables non-stop. If I do bulk insert I'll lose rows that are created when create a new table --> move the desired records --> delete old table --> rename new tables
Okay ... when everything fails you have to rely on "delete" to "delete" rows.
Please find below a template I foiund years ago in Metalink, feel free to customize as needed.
Code:
------------------------------------------------------
-- Create procedure
--
-- Remember to replace empno=' || empno
-- by your own purge condition
CREATE OR REPLACE PROCEDURE delete_tab (tablename IN VARCHAR2,
empno IN NUMBER ,
nrows IN NUMBER ) IS
sSQL1 VARCHAR2(2000);
sSQL2 VARCHAR2(2000);
nCount NUMBER;
BEGIN
nCount := 0;
sSQL1:='delete from '|| tablename ||
' where ROWNUM < ' || nrows || ' and empno=' || empno;
sSQL2:='select count(ROWID) from ' || tablename ||
' where empno= ' || empno;
LOOP
EXECUTE IMMEDIATE sSQL1;
EXECUTE IMMEDIATE sSQL2 INTO nCount;
DBMS_OUTPUT.PUT_LINE('Existing records: ' || to_char(ncount) );
commit;
EXIT WHEN nCount = 0;
END LOOP;
END delete_tab;
/
------------------------------------------------------
-- Execute above created procedure
--
-- In this example we are asking
-- to delete from table BIG_EMP,
-- all rows belonging to empno=7369
-- in batches of 5000 rows
SQL> execute delete_tab('big_emp',7369,5000)
Existing records: 60537
Existing records: 55538
Existing records: 50539
Existing records: 45540
Existing records: 40541
Existing records: 35542
Existing records: 30543
Existing records: 25544
Existing records: 20545
Existing records: 15546
Existing records: 10547
Existing records: 5548
Existing records: 549
Existing records: 0
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
This is a text book case where you may want to consider deleting data by doing inserts instead of doing deletes.
Here is the scenario...
You want to delete 70%+ of the rows sitting on table_a
1- Rename table_a as table_b
2- Create table_a as select * from table_b where v_column_name > sysdate - v_retention_days
3- Build indexes
4- Gather fresh stats.
5- After business approval drop table_b
This is faster, generates less redo and ensures tables and indexes are not fragmented at the end of the process.
Can you afford a maintenance window to do it?
This will be the perfect case, but it's about a production database that can't be stopped or to interrupt activity to allow me a maintenance window.
I know that recreating the table with only the data that has to remain it is faster and more reliable but I'm afraid of losing rows.
-
Check previous post sync issue
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
Check previous post sync issue
I'll give it a try.
Thanks for sharing this ... and also thanks for your time
Best regards
-
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
Glad to help.
I tested the script and seems to do the job excellent until now.
Thanks again
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|