<!-- [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)|
--------------------------------------------------------
分享到:
相关推荐
完整E文版Show.Stopper.The.Breakneck.Race.To.Create.WindowsNt.And.The.Next.Generation.At.Microsoft.pdf
In this module, the readers can quickly gain confidence in understanding and expanding their visualization, creation knowledge, and quickly create interesting, interactive data visualizations to ...
MySQL里Create Index 能否创建主键 Primary Key? 答案: 不能,必须用 Alter table 创建。 MySQL一个索引列最大允许的有效长度,不是列的所有数据都被索引的MyISAM 是 1000字节 InnoDB 是 767 字节 注意这里是字节。...
If you create an index using a large key, fewer entries will fit on a page, so more pages (and possibly more levels) will be needed for the index. On a qualified select, update, or delete, the ...
By the end of the book, you will have created a fully functional game that can be played in any compatible browser, or on any mobile device that supports HTML5. Topics include: Dealing with ...
Title: Game Design Workshop: A Playcentric Approach to Creating Innovative Games, 3rd Edition Author: Tracy Fullerton Length: 535 pages Edition: 3 Language: English Publisher: A K Peters/CRC Press ...
could not create the java virtual machine 解决办法,已亲自实践,放心使用
1. For each table, indicate the Primary Key(s) (PKs). If there is no PK, state so. Fully justify your answer. 2. For each table, indicate the Alternate Key(s) (AKs). If there is no AK, state so. ...
mysql中索引是非常重要的知识点,相比其他的知识点,索引更难掌握,并且mysql中的索引种类也有很多,比如primary key 、unique key 与index等等,本文章向大家介绍mysql中key 、primary key 、unique key 与index...
Create engaging apps with fragments to provide a rich user interface that dynamically adapts to the individual characteristics of your customers' tablets and smartphones About This Book From an ...
You’ll master the Yocto Project’s toolbox hands-on, by working through the entire development lifecycle with a variety of real-life examples that you can incorporate into your own projects. ...
It will help you more quickly climb the Android learning curve, so you can create the “killer app” you’ve dreamed of…or perhaps just a quick-and-dirty application for you and your friends. ...
VMware安装失败 “Failed to create the requested registry key Key,亲测成功解决问题
Plan for Implementation of the SQA Plan . . . . . . . . . . . . . 19 Step 5. Execute the SQA Plan. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 Quality Standards. . . . . . . . ...
Let’s start creating this animation and set it so that we can change the direction and speed of the clouds. 1 Confirm sample assets with WebEditor 2 Sample 3 Creating cloud textures 4 Creating a ...
In his book The Inmates Are Running the Asylum Alan Cooper calls for revolution - we need technology to work in the same way average people think - we need to restore the sanity. He offers a ...
Did you know that you can not only change what is on UltraEdit's toolbars, you can also change the icon used, as well as create your own custom toolbars and tools? File tabs Understand how file tabs ...
The overall context of Creating Maintainable APIs is to classify the topics into four main areas: classes and interfaces, HTTP REST APIs, messaging APIs, and message payloads (XML, JSON and JSON API ...