CODEWITHSUNDEEP
sqldatabaseoracle-databasepartitioning
woemler
woemler

Reputation: 7169

Oracle SQL: Selecting data and partition name from table and truncating partitions

This is a two part question:

1) Is it possible to retrieve the name of the partition that data lives in using a select statement, based on its ROWID or some other identifier?

eg.

SELECT DATA_ID, CATEGORY, VALUE, **PARTITION_NAME**
FROM MYTABLE
WHERE CATEGORY = 'ABC'

2) Is it possible to truncate a single partition of a table, without deleting the data stored in the other partitions?

I have a table with over a billion rows, hash partitioned by category. Only a handful of the categories have problems with their data, so it does not make sense to recreate the entire table, but deleting data from the table, even with all constraints inactive, is taking far too long.

Upvotes: 4

Views: 20255

Answers (3)

wolφi
wolφi

Reputation: 8361

Thanks to your hint about the rowid, I found a solution. If you have the rowid, it should be possible to determine the object the row belongs to.

A minimal example with 4 hash partitions:

CREATE TABLE pt (i NUMBER)
 PARTITION BY HASH (i) (PARTITION pt1, PARTITION pt2, PARTITION pt3, PARTITION pt4);

INSERT INTO pt SELECT ROWNUM FROM all_objects WHERE ROWNUM < 20;

Now, each row has a ROWID. You can find out the object number via DBMS_ROWID.ROWID_OBJECT. The dictionary table USER_OBJECTS has then the object_name (= the name of the table) and the subobject_name (= the name of the partition):

SELECT i, 
       ROWID AS row_id, 
       dbms_rowid.rowid_object(ROWID) AS object_no,
       (SELECT subobject_name 
          FROM user_objects 
         WHERE object_id = dbms_rowid.rowid_object(pt.ROWID)) AS partition_name
  FROM pt
 ORDER BY 3;

I   ROW_ID              OBJECT_NO PARTITION_NAME
6   AAALrYAAEAAAATRAAA  47832   PT1
11  AAALrYAAEAAAATRAAB  47832   PT1
13  AAALrYAAEAAAATRAAC  47832   PT1
9   AAALrZAAEAAAATZAAA  47833   PT2
10  AAALrZAAEAAAATZAAB  47833   PT2
12  AAALrZAAEAAAATZAAC  47833   PT2
17  AAALrZAAEAAAATZAAD  47833   PT2
19  AAALrZAAEAAAATZAAE  47833   PT2
2   AAALraAAEAAAAThAAA  47834   PT3
5   AAALraAAEAAAAThAAB  47834   PT3
18  AAALraAAEAAAAThAAD  47834   PT3
8   AAALraAAEAAAAThAAC  47834   PT3
1   AAALrbAAEAAAATpAAA  47835   PT4
3   AAALrbAAEAAAATpAAB  47835   PT4
4   AAALrbAAEAAAATpAAC  47835   PT4
7   AAALrbAAEAAAATpAAD  47835   PT4

Upvotes: 8

Jon Heller
Jon Heller

Reputation: 36808

Instead of finding the partition name, use the value in the PARTITION FOR syntax:

ALTER TABLE MYTABLE TRUNCATE PARTITION FOR ('ABC');

Although this operation will not affect the data in other partitions it may make your indexes UNUSABLE. Either rebuild the relevant indexes or use UPDATE INDEXES in the DDL.

Upvotes: 2

haki
haki

Reputation: 9759

1) no. you cannot do that, you will have to query all_tab_partitions to find out the partition for a ceratain value.

2) alter table x truncate partition y

Upvotes: 3

Related Questions