I'm running Oracle 8.1.7 on Windows 2000 Server.

I need to write one PLSQL procedure to housekeep a table, on daily basis.

There are 2 methods i can think of:
1. Use DBMS_SQL
...............
stmt_char := 'DELETE FROM TABLE_A WHERE TRUNC(DT) <= TRUNC(SYSDATE) - 3'; -- always keep 3 days
................
................
<use DBMS_SQL to start deleting, committing every 1000 records>

2. Use cursor
.............
CURSOR lv_delete_cur IS
SELECT column_A FROM TABLE_A -- where column_A is primary key
WHERE TRUNC(dt) <= TRUNC(SYSDATE) - 3;
.............
.............
<Loop cursor lv_delete_cur and keep a counter to commit every 1000 records>
..............

My questions are:
(a) Between approach 1 and 2, which is faster/better performance?
(b) In terms of performance, is there any difference if i modify to commit every 500 or 5000 records?
(c) Actually, currently i m using approach 1, which is considerably very slow!!!
[needs 5 - 7 hours to delete approx. 250000 records from table consisting approx. 800000 records.]
 

You can go for Dynamic SQL also and probably it helps you to improve the performance.

The performance of native dynamic SQL in PL/SQL is comparable to the performance of static SQL because the PL/SQL interpreter has built-in support for native dynamic SQL. Therefore, the performance of programs that use native dynamic SQL is much better than that of programs that use the DBMS_SQL package. Typically, native dynamic SQL statements perform 1.5 to 3 times better than equivalent statements that use the DBMS_SQL package. Of course, your performance gains may vary depending on your application.

The DBMS_SQL package is based on a procedural API and, as a result, incurs high procedure call and data copy overhead. For example, every time you bind a variable, the DBMS_SQL package copies the PL/SQL bind variable into its space for later use during execution. Similarly, every time you execute a fetch, first the data is copied into the space managed by the DBMS_SQL package and then the fetched data is copied, one column at a time, into the appropriate PL/SQL variables, resulting in substantial overhead resulting from data copying. In contrast, native dynamic SQL bundles the statement preparation, binding, and execution steps into a single operation, which minimizes the data copying and procedure call overhead and improves performance.

for e.g
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE my_table;';
END;
/

Please let me know if any issues
 

I want to share a few ideas..
first of all you have said:

I need to write one PLSQL procedure to housekeep a table, on daily basis

here ,the question is why PLSQL procedure...as Tom Kyte would say often,we should use sql instead of pl/sql whereever possible...now here i think you can definately use simple sql statement like this.

DELETE FROM TABLE_A WHERE TRUNC(DT) <= TRUNC(SYSDATE) - 3

the thing to watch here is whether any indexes are being used or not....is there an index on the column DT?
pl post the execution plan of the query.
they might not get used i guess and you may need function based index as you are using the function trunc on the column DT.
alternately ,just for experiment try the following sql statement (pl. rollback the results ,as this is just experiment)
(pl. index the DT column if not already indexed)
DELETE FROM TABLE_A WHERE DT <= SYSDATE - 4

see if there is some performance gain.

--------------------------------------------------------------------------------------------------------------------------------
Basically I do agree with your saying, but if i use dynamic sql, how do i perform commit for every 1000 records (deleted), like wat i have explained in my first post?
--------------------------------------------------------------------------------------------------------------------------------
Perhaps i have tried most with and without TRUNC function, but the performance seems almost the same. Fyi, the DT column is indexed. But i guess u should not use:

DELETE FROM TABLE_A WHERE DT <= SYSDATE - 4

bcoz it is not accurate. If you really want to get rid off the TRUNC, i supposed you should use:

lv_sysdate_date := TRUNC(SYSDATE);

DELETE FROM TABLE_A WHERE DT >= lv_sysdate_date - 3 AND DT < lv_sysdate_date - 4;

Please correct me, if i am wrong.

And to your question why am i using PL/SQL instead of SQL statement. This is bcoz:
(a) Actually the "3" as in "TRUNC(dt) <= TRUNC(SYSDATE) - 3", i need to query from another table. It is not hardcoded.
(b) I need to commit after 1000 records have been deleted, due to rollback segment issue.

--------------------------------------------------------------------------------------------------------------------------------

Can u pl. post the execution plan of the query? what i would like to see is ,whether the index is being used or not..in fact i think that the index may not be used if this is not a function based index..so could u pl. post the execution plan of the query.for commiting,if i understand correctly,it really does not matter whether you commit after 1000 records or 10000 records..so don't expect any big gain in performance from that area...

Thats what i feel...key factor is ensuring & verifying that index is being used.also try NOT to use the index and see performance..and explain plan and post it and try TO USE the index and see performace and post the explain plan..i would like to compare the two plans..(PL. ROLLBACK if u are just experimenting)

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips