We can create the index before creating the primary key.

本文探讨了Oracle数据库中主键与其关联索引之间的关系。通过实例演示了如何创建表并设置主键,解释了为何无法直接删除与主键关联的索引。此外,还介绍了在保留索引的情况下移除主键约束的方法。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:DrawingGridVerticalSpacing>7.8 磅</w:DrawingGridVerticalSpacing> <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery> <w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:SpaceForUL/> <w:BalanceSingleByteDoubleByteWidth/> <w:DoNotLeaveBackslashAlone/> <w:ULTrailSpace/> <w:DoNotExpandShiftReturn/> <w:AdjustLineHeightInTable/> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!-- [if !mso]> <object classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui> </object> <mce:style><!-- st1/:*{behavior:url(#ieooui) } --> <!-- [endif]--><!-- [if gte mso 10]> <mce:style><!-- /* Style Definitions */ table.MsoNormalTable {mso-style-name:普通表格; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} --> <!-- [endif]-->

Our system encourter a big index contention then we found the index is created by the primary key.

then we think about if we can remove the index instead of keeping the primary key if the index is not used by any query.

but we find it is impossiable.

We can create the index before creating the primary key.
As we Know the index will be created at same time when we create the primary key.
But we can create the index maually before creating the primary key.
This is helpful when the index is big.
We can compress ,parallel option etc.

See the example as below.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Nov 30 02:20:26 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP and Data Mining options

SQL> create table t as select * from all_objects;

Table created.

SQL> alter table t add constraint pk_t primary key (OBJECT_ID);

Table altered.

The index is created at same time and also named as pk_t.

SQL> set autotrace trace exp

SQL> select count(*) from t;

Execution Plan

----------------------------------------------------------

-----------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)|

-----------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 (8)|

| 1 | SORT AGGREGATE | | 1 | |

| 2 | INDEX FAST FULL SCAN| PK_T | 36063 | 38 (8)|

-----------------------------------------------------------

SQL> set autotrace off;

SQL> select CONSTRAINT_NAME,INDEX_NAME from dba_constraints where TABLE_NAME='T';

CONSTRAINT_NAME INDEX_NAME
------------------------------ ------------------------------
PK_T PK_T

SQL> drop index PK_T;

drop index PK_T

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

The index and constraint are coupled. You can not drop the index but have the constraint.

But you can drop the constraint while have the index with the following command.

SQL> alter table t drop constraint PK_T keep index;

Table altered.

SQL> set autotrace trace exp

SQL> select count(*) from t;

Execution Plan

----------------------------------------------------------

-----------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)|

-----------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 (8)|

| 1 | SORT AGGREGATE | | 1 | |

| 2 | INDEX FAST FULL SCAN| PK_T | 36063 | 38 (8)|

-----------------------------------------------------------

SQL> alter table t add constraint pk_t2 primary key (OBJECT_ID);

Table altered.

SQL> select count(*) from t;

Execution Plan

----------------------------------------------------------

-----------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)|

-----------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 (8)|

| 1 | SORT AGGREGATE | | 1 | |

| 2 | INDEX FAST FULL SCAN| PK_T | 36063 | 38 (8)|

SQL> set autotrace off;

SQL> select CONSTRAINT_NAME,INDEX_NAME from dba_constraints where TABLE_NAME='T';

CONSTRAINT_NAME INDEX_NAME
------------------------------ ------------------------------
PK_T2 PK_T

SQL> drop index PK_T;

drop index PK_T

*

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter table t drop constraint PK_T;

alter table t drop constraint PK_T

*

ERROR at line 1:

ORA-02443: Cannot drop constraint - nonexistent constraint

SQL> alter table t drop constraint PK_T2;

Table altered.

If we used this command then the index PK_T was dropped with the constrain PK_T2.

That was to say the constraint PK_T2 associated with index PK_T automitically.

SQL> select INDEX_NAME,TABLE_OWNER,INDEX_TYPE from dba_indexes where TABLE_NAME='T';

INDEX_NAME TABLE_OWNER

------------------------------ ------------------------------

INDEX_TYPE

---------------------------

SQL> set autotrace trace exp

SQL> select count(*) from t;

Execution Plan

----------------------------------------------------------

--------------------------------------------------------

| Id | Operation | Name | Rows | Cost (%CPU)|

--------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 226 (2)|

| 1 | SORT AGGREGATE | | 1 | |

| 2 | TABLE ACCESS FULL| T | 36063 | 226 (2)|

--------------------------------------------------------

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值