Re: Deleting Large Numbers of Rows w/o Filling up my redo logs

From: robert <robert_at_barbarian.thesecondnexus.net>
Date: Mon, 22 Dec 2008 12:25:30 -0600
Message-ID: <giom2r$7gb$1@news.motzarella.org>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Mladen Gogala wrote:
|
| I am not sure that a PK constraint can be enforced by a local index.
|

It is worth finding out. Below is my test script, on 11.1.0.6.

Below that is the output, edited slightly for brevity. Nothing fancy. It ~ creates a partitioned table, on it a partitioned unique index, and then a primary key on the partitioned and indexed column.

We drop a partition then check with a test sql - sure enough the execution plan has not changed and we are still hitting the partitioned index -- no rebuild.

Cool, so it works.

For the OP table, which is a log table of some kind, it would probably work just fine. That is a special case though, because almost every query will probably have a date range predicate so nobody is likely to need or insist on global indexes on other columns...

For the general case though, (and n.b. this is not a popular opinion) I DO NOT endorse the strategy of using partitions to simplify data purging, though I admit I've done it myself on occasion. ALWAYS lived to regret it. There are just too many more important things we should be able to accomplish by wisely partitioning a table than to spend such a valuable resource on data purge.

• --- test script ---- spool partitioned; select version from v$instance; drop table bob purge;

create table bob(x integer not null, v varchar2(2)) partition by range(x) (

~  partition p0 values less than (-1),
~  partition p1 values less than (0),
~  partition p2 values less than (maxvalue));

create unique index bob_x on bob(x) local; alter table bob add constraint xpk_bob primary key (x); insert into bob values (-2,'xx');
insert into bob values (-1,'yy');
insert into bob values(0,'zz');
commit;

• -- check that the index is local select locality from all_part_indexes where index_name='BOB_X';
• -- chcke that our PK is using the local index select index_name from user_constraints where constraint_name='XPK_BOB';
• -- check that our test select is using the index set autotrace on; select * from bob where x=0;
• -- after partition drop, are we still using the index? alter table bob drop partition p0; select * from bob where x=0;

exit
- ----------- end of test script -------

• --------- contents of partitioned.lst ---------------------

VERSION

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

11.1.0.6.0

Table dropped.
Table created.
Index created.
Table altered.
1 row created.
1 row created.
1 row created.

Commit complete.
LOCALI
- ------
LOCAL INDEX_NAME

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

BOB_X ~ X V
- ---------- --
~ 0 zz

Execution Plan

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



| Id  | Operation                          | Name  |Rows| Bytes

|Pstart|Pstop |

-

---

| 0 | SELECT STATEMENT | | 1 | 16 | |

~      |


|   1 |  PARTITION RANGE SINGLE            |       |  1 |    16 |   3 |

~ 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| BOB | 1 | 16 | 3 |
~ 3 |
|* 3 | INDEX UNIQUE SCAN | BOB_X | 1 | | 3 |
~ 3 |

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

Table altered.

~ X V
- ---------- --
~ 0 zz

Execution Plan

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



| Id  | Operation                          | Name  |Rows| Bytes

|Pstart|Pstop |

|   0 | SELECT STATEMENT                   |       |  1 |    16 |     |

~      |


|   1 |  PARTITION RANGE SINGLE            |       |  1 |    16 |   2 |

~ 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| BOB | 1 | 16 | 2 |
~ 2 |
|* 3 | INDEX UNIQUE SCAN | BOB_X | 1 | | 2 |
~ 2 |

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)

iD8DBQFJT9ua6pCtGHbU9PURAseVAJ9A04wMtSIKp1VyUR2b6j3BSp1UkwCfRFja prgw1uca6GCvszOlEOmrd10=
=arcC
-----END PGP SIGNATURE----- Received on Mon Dec 22 2008 - 12:25:30 CST