英文:
Implementing primary key on a partitioned table in Oracle
问题
我已经在Oracle SQL中创建了一个分区表，其中列PKID是主键，它是从原始数据表中创建我的表的键。
我已经使用3个月的范围对我的表进行了分区。当我向这个表添加新行时，我宁愿不必验证PKID在每个分区上都是唯一的，而只需在数据将存储在的分区上验证。
我认为这将降低插入语句的执行时间。是将主键设置为日期和PKID的组合，还是将主键设置为本地的方式更好？还有其他更好的选项吗？
英文:
I have created a partitioned table in Oracle SQL where column PKID is the primary key, and it is the key from the raw data table from which my table was created.
I have partitioned my table using a range of 3 months. When I am adding new rows to this table, I would prefer not to have to verify that the PKID is unique on every partition, but only on the partition the data will be residing on.
I believe this will lower the execution time on the insert statements. Would it be better to make the primary key a combination of date and PKID, or to make the primary key local? Is there some other option that would be better?
答案1
得分: 1
- 创建全局索引。该索引跨越整个表。当您删除或截断一个分区时，索引变为无效，您需要重新构建它。
- 创建本地索引。在这种情况下，分区键必须是主键的一部分。
英文:
You have two options.
- Create a global index. The index spans over the entire table. When you drop or truncate a partition, then the index becomes invalid and you need to rebuild it.
- Create a local index. In this case, the partition key must be part of the primary key.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论