Oracle SQL:从表中选择数据和分区名称并截断分区 [英] Oracle SQL: Selecting data and partition name from table and truncating partitions

问题描述

这是一个分为两部分的问题:

This is a two part question:

1)是否可以根据其ROWID或其他一些标识符使用select语句检索数据所在的分区的名称?

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?

例如

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

2)是否可以在不删除存储在其他分区中的数据的情况下截断表的单个分区?

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.

推荐答案

感谢您对 rowid 的提示，我找到了一个解决方案.如果您有rowid，则应该可以确定该行所属的对象.

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.

带有4个哈希分区的最小示例:

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;

现在，每行都有一个ROWID.您可以通过DBMS_ROWID.ROWID_OBJECT找出对象编号.字典表USER_OBJECTS然后具有object_name(=表的名称)和subobject_name(=分区的名称):

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

这篇关于Oracle SQL:从表中选择数据和分区名称并截断分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！