oracle 改变主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7959297/
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
Altering primary key
提问by x.509
I have seen 2 approaches to alter a primary key. the approach 1, is something in which i delete the primary key (it deletes corresponding index as well) and then create the primary key with a new index with in it i.e.
我见过两种改变主键的方法。方法 1，是我删除主键（它也删除相应的索引），然后创建带有新索引的主键，即
alter table TABLE_NAME drop constraint PK_TABLE_NAME drop index;
alter table TABLE_NAME
add constraint PK_TABLE_NAME PRIMARY KEY ("COL1")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE;
COMMIT;
/
The second approach is doing all steps indiviually i.e.
第二种方法是单独执行所有步骤，即
alter table TABLE_NAME drop constraint PK_TABLE_NAME;
drop index PK_TABLE_NAME;
CREATE UNIQUE INDEX PK_TABLE_NAME ON TABLE_NAME
(COL1)
LOGGING
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
alter table TABLE_NAME add constraint PK_TABLE_NAME PRIMARY KEY ("COL1") USING INDEX PK_TABLE_NAME;
COMMIT;
/
So now my questions are
所以现在我的问题是
- are these 2 approaches has anything different at the backend except for the fact that the steps are splitted up?
- I see that we can mention column name wihtin quotes i.e. "COL1"or without quotes i.e. COL1. Are these 2 approaches would make any difference?
- Consider these steps are being executed on a table which has TRILLION OF DATA, do any of these has any performance gain on other?
- 除了步骤被拆分的事实之外，这两种方法在后端有什么不同吗？
- 我看到我们可以提到带引号的列名，即"COL1"或不带引号，即COL1。这两种方法会有什么不同吗？
- 考虑到这些步骤是在具有 TRILLION OF DATA 的表上执行的，这些步骤中的任何一个是否有任何性能提升？
回答by Jon Heller
With the DDL exactly as you have it, as far as I know there is no difference between the two. (This is based on comparing select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME') from dual;, some simple performance tests, and my previous experience dealing with these two approaches.)
与您拥有的 DDL 完全一样，据我所知，两者之间没有区别。（这是基于比较select dbms_metadata.get_ddl('TABLE', 'TABLE_NAME') from dual;、一些简单的性能测试以及我以前处理这两种方法的经验。）
But if you have trillions of rows or bytes, then you should probably use parallelism, and then there is a significant difference. The first approach does not allow you to create the index in parallel (ORA-03001: unimplemented feature), but the second method does.
Even if you do not want the index to be parallel, you should probably create it in parallel and then change it to noparallel with a command like ALTER INDEX <index> NOPARALLEL;
但是，如果您有数万亿行或字节，那么您可能应该使用并行性，然后就会有显着差异。第一种方法不允许您并行创建索引 ( ORA-03001: unimplemented feature)，但第二种方法可以。即使您不希望索引是并行的，您也应该并行创建它，然后使用类似的命令将其更改为 noparallelALTER INDEX <index> NOPARALLEL;
As for the double quotes, they make no difference when the column is all upper-case. But if you use mixed case then it means that the name is case sensitive and you must always use quotation marks to refer to the name. That is really annoying, so I usually remove quotation marks to prevent accidentally creating a case-sensitive name.
至于双引号，当列全部为大写时，它们没有区别。但是，如果您使用大小写混合，则意味着名称区分大小写，并且您必须始终使用引号来引用名称。这真的很烦人，所以我通常删除引号以防止意外创建区分大小写的名称。
A few other notes. You may want to consider using NOLOGGING. And there is no need for the COMMIT;, DDL will automatically cause a commit.
其他一些注意事项。您可能需要考虑使用 NOLOGGING。并且不需要COMMIT;，DDL 会自动导致提交。
