Deleting Large No. of Rows

Guys,

I need to delete about 1M+ rows of data everyday from a single table, using the 'delete from table where.....' statement will take some time, and the truncate command will delete all the rows in the table.

Can you give me suggestion on how to implement this using the lease possible time. Is there a 'truncate from table where.....' command.


Ed

From my knowledge there is no way to use a truncate "where"

Good luck,
Nirasha

If this is an ongoing issue, you may want to look at partitioned tables. You can truncate a single partition of a table.

PArtition by a day , lot of mantainence,and if u have dependent tables on this table , a lot more work,
Why not write a procedure and delete.

Deleting 1M+ rows whether by procedure or through any other application will consume a considerable amount of time.

Using truncate will delete all rows in the table, and I don't want to do that.

Here is the scenaro.
Daily I am receiving 1M+ of inserts in the table. I just need to keep 1 months worth of data, so I need to do daily delete on that table

Got any other idea?

Thanks,

Ed

Well if you have 8i and are not worrying about recovery
you can use the "NO LOGGING" option. I use this all the
time and it increases delete time substantially
scotta

you can goo for exp imp (if your version is 8*) using the QUERY -option, (select clause used to export a subset of a table)...so you may import the table with the subset of records you want (or may have e test in table in import on the end of it rename to the old name)

Quote:

---

Originally posted by edsperez
Here is the scenaro.
Daily I am receiving 1M+ of inserts in the table. I just need to keep 1 months worth of data, so I need to do daily delete on that table

---

This is the perfect scenario for using partitions, as Jeff allready mentioned.

NOLOGGING doesn't help with delets at all.

As well as NOLOGGING, I use DELETE_COMMIT (while logged in as SYS) with COMMIT interval set at 100,000 (rows) or so. Here's an example:

EXEC DELETE_COMMIT('delete /*+ (tablename index)*/ from tablename where column=constraint',interval number);

This then will perform COMMIT at set interval, which will prevent Rollback segments going loopy, as well as saving some time.

Fiona

It is better to change the design. Add a column, deleted_flag char(1) in the table. Daily update this column with 'Y' for the rows you want to delete. On Sunday / when there is low activity on the database, you delete 7M rows after changing the table to nologging. Please also remember that the indexes associated with the table also need to rebuild.

Gee, the missundertanding of the NOLOGGING option is simply amaising! It keeps poping up all the time in this forum (not only in this thread), showing how people simply don't understand what the NOLOGGING realy does (and what it doesn't).

Seems also that Oracle has been very unfortunate in naming this option. First it was named as "UNRECOVERABLE", but I guess this sounded too scary. So they changed it to NOLOGGING, but now people obviously conclude from this name that DML operations on such a table don't get logged at all. This is so far from the truth!

Setting the NOLOGGING/LOGGING for a table only affects three type of activities:
- Insert phase of the CTAS (Create Table As Select)
- Direct load path of SQL*Loader (DIRECT=Y)
- Direct path INSERTS (with hint /*+ APPEND*/)

It can *in no way* affect the deletes or updates of any kind, it also doesn't affect normal inserts. So if you are altering a table to NOLOGGING just to speed the deletes you are waisting your time.

First, I agree that something needs to change. Partitioning sounds like the perfect solution! If not, then I agree with tamilselvan's solution, but I have a different implementation choice for you.

First of all, going with the flag solution raises some issues. First of all, if this is an OLTP database, then you will probably need to add the new field to the beginning of every index on the table, because it *will* be in every query. If this is a DSS or OLAP database, then we are in better shape, as a single bitmap index will suffice.

Now, on to the implementation. Instead of modifying every query in the app, it would be easier to implement some fine-grained access control. Now, I haven't actually implemented any of these yet, so bear with me if I don't get it perfect. But it seems that all you have to do is:

- Make a function that returns a WHERE predicate:

CREATE OR REPLACE FUNCTION IgnoreDeleteFlag
(
---P_SCHEMA IN VARCHAR2,
---P_OBJECT IN VARCHAR2
)
RETURN
---VARCHAR2
AS
BEGIN
---RETURN---'Deleted_Flag = ''0''';
END;

- Then, create a policy that simply links that function to the table:

DBMS_RLS.ADD_POLICY
---(
------OBJECT_SCHEMA => 'MY_SCHEMA',
------OBJECT_NAME => 'BIG_TABLE',
------POLICY_NAME => 'WHATEVER',
------FUNCTION_SCHEMA => 'MY_SCHEMA',
------POLICY_FUNCTION => 'IgnoreDeleteFlag',
------STATEMENT_TYPES => 'SELECT, UPDATE' ,
------UPDATE_CHECK => TRUE
---);

Now, every single SELECT against that table will automatically have AND Deleted_Flg = '0' appended to it.

The drawbacks are that this 'addition' to the statement is made at the back-end and *completely* hidden from the outside world, including the SGA, explain plans, etc. This is why it is important to understand the implications beforehand, as you can't double-check them after implementation. Again, however, if this is a DSS database, the risk would be mitigated greatly by the bitmap index.

The advantage is that it is easy as pie to implement.

HTH,

- Chris

BTW - as mentioned before, don't forget to rebuild your indexes after the delete.


[Edited by chrisrlong on 04-05-2001 at 03:51 PM]

In response to JModic's observation. First, as always, he is absolutely correct. However, I would add another entry to his list of non-logged operations:

Parallel inserts, but they require:

- ALTER SESSION ENABLE/FORCE PARALLEL DML
- table PARALLEL attribute or statement PARALLEL hint (an APPEND hint is optional)

...to become Direct-Load inserts

- Chris

What I think u could do is write a procedure which will select
all the rows from the table and insert into a temp table with the insert into * select * from a excluding the rows you delete statement,then truncate the table and reload using the temp table. Hope this helps.

First of all, I'd like to thank you all Guys for sharing your thoughts. Right now I am thinking that the best way to do this is by partitioning (31 partition for 31 days). This application will be 24X7 operation, so Sunday will not be a low activity day, My question now is can we really truncate table by partition, I haven’t done this before. Another thing is can we create a partition in this manner.

create table message( acct_no number(5) unique,
sales_amount number(8) not null,
acct_date date not null)
partition by range(acct_date)
(partition Day1 values to_char(‘acct_date,’DD’)=(1) tablespace day1,
partition Day2 values to_char(‘acct_date,’DD’)=(2) tablespace day2,
partition Day3 values to_char(‘acct_date,’DD’)=(3) tablespace day3,
partition Day4 values to_char(‘acct_date,’DD’)=(4) tablespace day4,
.
.
.
partition Day31 values to_char(‘acct_date,’DD’)=(31) tablespace day34,
);

Keep the ideas flowing……

Thanx,

Ed

I believe you can not give function like to_char(‘acct_date,’DD’) in the partition caluse.

What possibly you could do is, create another column (say Day_Col number(2)) and put the day part into this column whenever rows are added to this table (May be by use of trigger).


Truncating the partition may be cumborsome, when you have global unpartitioned indexes, foreign keys etc.

Just a thought. Some others may have some elegent solutions.

we have the same issue in our 24x7 system and ended up into those solutions:

In most cases we can have runtime tables that are small tables used by application to perform buiseness and after transaction is done, records are closed and moved(deleted and inserted) to main table.

main table is a partitioned table with partitions ending with _YYYYMMDD string (eg. TABL1_20010506 for 06-05-2001 partition).

Partitiones are created and truncated by small utility package, that has procedures to add/truncate partitiones of equal datetime intervals (given as days, weeks or quaters)

If the buisenes can be arranged such that records are processed and closed(i.e. moved to main tables) and reports can run on closed records(i.e. main tables)only then for 24x7 system it is quite a good design as maintaning small tables takes seconds or less than to maintain large tables containg milions and more of recors.

Some issues regarding partitioned table:
as was pointed out, global indexes gets unusable.

performance of local index should be lower over global index(if partition prunning can not be used by a query).

if partitioned table has a snapshot then there has to be used some tricks to truncate partition and preserve snapshot log.

and of course Oracle is still debuging partitioned tables from some really nasty bugs, so it depends what version you use.