Oracle中创建主键，可以有几种方式。
SQL> create table t_pk_01 (id number, constraint pk_id_01 primary key(id));Table created.
讯享网
创建主键约束的同时，他会自动创建一个唯一索引，
讯享网SQL> select table_name, constraint_name, constraint_type from user_constraints where table_name=’T_PK_01’;TABLE_NAME CONSTRAINT_NAME C—————————— —————————— -T_PK_01 PK_ID_01 PSQL> select table_name, index_name, uniqueness from user_indexes where table_name=’T_PK_01’;TABLE_NAME INDEX_NAME UNIQUENES—————————— —————————— ———T_PK_01 PK_ID_01 UNIQUE
SQL> create table t_pk_02 (id number);Table created.SQL> alter table t_pk_02 add constraint pk_id_02 primary key (id);Table altered.
我们从10046来看下alter table到底做了什么，
讯享网alter session set events ‘10046 trace name context forever, level 12’;Session altered.SQL> alter session set tracefile_identifier=‘bisal’;Session altered.SQL> alter table t_pk_02 add constraint pk_id_02 primary key (id);Table altered.SQL> alter session set events ‘10046 trace name context off’;Session altered.SQL> select distinct(m.sid),p.pid,p.tracefile from v\(mystat</span> m,v<span class="code-snippet__variable">\)session s,v\(process</span> p <span class="code-snippet__built_in">where</span> m.sid=s.sid and s.paddr=p.addr;</span></code><code><span class="code-snippet_outer"> SID PID TRACEFILE</span></code><code><span class="code-snippet_outer">---------- ----------- -------------------------------------------------------------------------------</span></code><code><span class="code-snippet_outer"> 189 22 /u01/app/oracle/diag/rdbms/dcm/DCM/trace/DCM_ora_18653_bisal.trc</span></code></pre></section><p><br /></p><p>从trace我们能看到，对T_PK_02加了share模式锁，指定nowait，先创建的约束，然后创建了唯一索引，</p><section class="code-snippet__fix code-snippet__js"><ul class="code-snippet__line-index code-snippet__js"><li></li><li></li><li></li><li></li><li></li><li></li><li></li><li></li><li></li><li></li></ul><pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer">...</span></code><code><span class="code-snippet_outer"><span class="code-snippet__keyword">LOCK</span> <span class="code-snippet__keyword">TABLE</span> <span class="code-snippet__string">"T_PK_02"</span> <span class="code-snippet__keyword">IN</span> <span class="code-snippet__keyword">SHARE</span> <span class="code-snippet__keyword">MODE</span> <span class="code-snippet__keyword">NOWAIT</span></span></code><code><span class="code-snippet_outer">...</span></code><code><span class="code-snippet_outer"><span class="code-snippet__keyword">alter</span> <span class="code-snippet__keyword">table</span> t_pk_02 <span class="code-snippet__keyword">add</span> c</span></code><code><span class="code-snippet_outer">...</span></code><code><span class="code-snippet_outer"><span class="code-snippet__keyword">update</span> con\) ……insert into con\( ...</span></code><code><span class="code-snippet_outer">...</span></code><code><span class="code-snippet_outer"><span class="code-snippet__keyword">CREATE</span> <span class="code-snippet__keyword">UNIQUE</span> <span class="code-snippet__keyword">INDEX</span> <span class="code-snippet__string">"BISAL"</span>.<span class="code-snippet__string">"PK_ID_02"</span> <span class="code-snippet__keyword">on</span> <span class="code-snippet__string">"BISAL"</span>.<span class="code-snippet__string">"T_PK_02"</span>(<span class="code-snippet__string">"ID"</span>) NOPARALLEL</span></code></pre></section><p><br /></p><p>第三种，分开创建主键约束和主键索引。</p><p><br /></p><p>主要有两个场景。</p><p><br /></p><p>(1) 当使用CTAS创建表时，</p><section class="code-snippet__fix code-snippet__js"><ul class="code-snippet__line-index code-snippet__js"><li></li><li></li></ul><pre class="code-snippet__js" data-lang="cs"><code><span class="code-snippet_outer">SQL> create table t_pk_03 <span class="code-snippet__keyword">as</span> <span class="code-snippet__keyword">select</span> * <span class="code-snippet__keyword">from</span> t_pk_01;</span></code><code><span class="code-snippet_outer">Table created.</span></code></pre></section><p><br /></p><p>主键约束并未带过来，</p><section class="code-snippet__fix code-snippet__js"><ul class="code-snippet__line-index code-snippet__js"><li></li><li></li><li></li><li></li><li></li></ul><pre class="code-snippet__js" data-lang="cs"><code><span class="code-snippet_outer">SQL> <span class="code-snippet__keyword">select</span> table_name, constraint_name, constraint_type <span class="code-snippet__keyword">from</span> user_constraints <span class="code-snippet__keyword">where</span> table_name=<span class="code-snippet__string">'T_PK_03'</span>;</span></code><code><span class="code-snippet_outer">no rows selected</span></code><code><span class="code-snippet_outer"><br /></span></code><code><span class="code-snippet_outer">SQL> <span class="code-snippet__keyword">select</span> table_name, index_name, uniqueness <span class="code-snippet__keyword">from</span> user_indexes <span class="code-snippet__keyword">where</span> table_name=<span class="code-snippet__string">'T_PK_03'</span>;</span></code><code><span class="code-snippet_outer">no rows selected</span></code></pre></section><p><br /></p><p>此时如果表中存在很多的数据，直接使用方法2，可能会带来两个问题，</p><p>1. 创建唯一索引的用时。</p><p>2. 唯一索引允许包含空值，因为主键约束不允许空值，还需要判断字段是否为空的用时。</p><p><br /></p><p>对(1)，从trace，我们可以看到，默认创建唯一索引的时候，并未指定online，因此用时取决于数据量。<br /></p><p><br /></p><p>对(2)，如果字段设置NOT NULL，应该不需要判断，如果没设置，则需要判断字段中是否含空值，还是取决于表的数据量。</p><p><br /></p><p>因此，可以选择先在线创建唯一索引，再增加主键约束，从trace能看到，此时增加主键约束的过程中，不会再操作索引，<br /></p><section class="code-snippet__fix code-snippet__js"><ul class="code-snippet__line-index code-snippet__js"><li></li><li></li><li></li><li></li><li></li></ul><pre class="code-snippet__js" data-lang="cs"><code><span class="code-snippet_outer">SQL> <span class="code-snippet__function">create unique index pk_id_03 <span class="code-snippet__keyword">on</span> <span class="code-snippet__title">t_pk_03</span>(<span class="code-snippet__params">id</span>) online</span>;</span></code><code><span class="code-snippet_outer">Index created.</span></code><code><span class="code-snippet_outer"><br /></span></code><code><span class="code-snippet_outer">SQL> <span class="code-snippet__function">alter table t_pk_03 <span class="code-snippet__keyword">add</span> constraint pk_id_03 primary <span class="code-snippet__title">key</span> (<span class="code-snippet__params">id</span>)</span>;</span></code><code><span class="code-snippet_outer">Table altered.</span></code></pre></section><p><br /></p><p>(2) 往往在生产环境，数据表空间和索引表空间是分开的，如果采用第一种和第二种的方式，主键索引会创建在执行用户的默认表空间，很可能是数据表空间，因此分开创建，还可以在创建索引的时候，指定tablespace，明确索引表空间，<br /></p><section class="code-snippet__fix code-snippet__js"><ul class="code-snippet__line-index code-snippet__js"><li></li><li></li></ul><pre class="code-snippet__js" data-lang="cs"><code><span class="code-snippet_outer">SQL> <span class="code-snippet__function">create unique index pk_id_03 <span class="code-snippet__keyword">on</span> <span class="code-snippet__title">t_pk_03</span>(<span class="code-snippet__params">id</span>) tablespace xxx</span>;</span></code><code><span class="code-snippet_outer">Index created.</span></code></pre></section><p><br /></p><p><br /></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;"><strong style="max-width: 100%;font-family: -apple-system-font, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;text-align: left;box-sizing: border-box !important;overflow-wrap: break-word !important;"><span style="max-width: 100%;color: rgb(0, 82, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">近期更新的文章：</span></strong></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《小白学习MySQL - 数据库软件和初始化安装》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《小白学习MySQL - 闲聊聊》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《Redis和Sentinel的安装部署和配置》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《“火线”和“零线”》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《通过索引提升SQL性能案例一则》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《如何手动添加jar包到maven本地库？》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《1元股权转让的一点思考》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《如何打造一个经常宕机的业务系统？》<br style="max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;" /></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《Linux恢复误删文件的操作》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《Linux的scp指令使用场景》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《Oracle处理IN的几种方式》<br style="max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;" /></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《如何搭建一支拖垮公司的技术团队？》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《IP地址解析的规则》<br style="max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;" /></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《MySQL的skip-grant-tables》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《国产数据库不平凡的一年》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《Oracle要求顺序的top数据检索问题》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《日常工作中碰到的几个技术问题》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《了解一下sqlhc》<br style="max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;" /></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《Oracle的MD5函数介绍》<br style="max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;" /></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《Oracle 19c的examples静默安装》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《sqlplus登录缓慢的解决》<br style="max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;" /></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《VMWare 11安装RedHat Linux 7过程中碰到的坑》<br style="max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;" /></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《COST值相同？是真是假？》<br style="max-width: 100%;box-sizing: border-box !important;overflow-wrap: break-word !important;" /></p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《Oracle 11g的examples静默安装》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《同名的同义词和视图解惑》</p><p style="max-width: 100%;min-height: 1em;font-family: -apple-system, BlinkMacSystemFont, "Helvetica Neue", "PingFang SC", "Hiragino Sans GB", "Microsoft YaHei UI", "Microsoft YaHei", Arial, sans-serif;letter-spacing: 0.544px;white-space: normal;background-color: rgb(255, 255, 255);box-sizing: border-box !important;overflow-wrap: break-word !important;">《v\)和v_$的一些玄机》文章分类和索引：《公众号700篇文章分类和索引》
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请联系我们，一经查实，本站将立刻删除。
如需转载请保留出处：https://51itzy.com/kjqy/155992.html