Oracle SQL：从表中选择数据和分区名称并截断分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17329384/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL: Selecting data and partition name from table and truncating partitions
提问by woemler
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 ROWIDor some other identifier?
1) 是否可以根据数据所在的分区ROWID或其他标识符，使用 select 语句检索数据所在分区的名称？
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?
2) 是否可以截断表的单个分区，而不删除其他分区中存储的数据？
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.
我有一个超过 10 亿行的表，按类别进行哈希分区。只有少数类别的数据存在问题，因此重新创建整个表没有意义，但是从表中删除数据，即使所有约束都处于非活动状态，也需要很长时间。
回答by wolφi
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.
感谢您对rowid的提示，我找到了解决方案。如果您有 rowid，则应该可以确定该行所属的对象。
A minimal example with 4 hash partitions:
具有 4 个哈希分区的最小示例：
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_OBJECTShas then the object_name (= the name of the table) and the subobject_name (= the name of the partition):
现在，每一行都有一个ROWID. 您可以通过 找到对象编号DBMS_ROWID.ROWID_OBJECT。字典表USER_OBJECTS具有 object_name（= 表的名称）和 subobject_name（= 分区的名称）：
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
回答by haki
1) no. you cannot do that, you will have to query all_tab_partitionsto find out the partition for a ceratain value.
1) 没有。您不能这样做，您将不得不查询all_tab_partitions以找出特定值的分区。
2) alter table x truncate partition y
2) alter table x truncate partition y
回答by Jon Heller
Instead of finding the partition name, use the value in the PARTITION FORsyntax:
使用PARTITION FOR语法中的值，而不是查找分区名称：
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 INDEXESin the DDL.
尽管此操作不会影响其他分区中的数据，但它可能会使您的索引不可用。要么重建相关索引，要么UPDATE INDEXES在 DDL 中使用。
