登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

梦想之鹰的天空

天高任鸟飞......放飞....心情..........放飞.....梦想

 
 
 

日志

 
 

Oracle与MySQL删除字段时对索引与约束的处理对照  

2008-06-09 10:00:45|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

  不知道有多少人清楚的知道,在Oracle中,如果一个复合索引,假定索引(a,b,c)三个字段,删除了(包括unused)其中一个字段,Oracle会怎么处理这个索引。同样,如果是约束,Oracle又怎么处理?

  用oracle为例子,我又拿mysql做了一个对比,看看mysql是怎么处理这个问题的。我这里不讨论谁好谁差,只是希望大家知道其中的差别与细节而已。

  我们先看Oracle的例子,我们创建一个表,然后在上面创建一个约束,创建一个索引:

  SQL 10G>create table test(a int,b int,c int);

  Table created.

  SQL 10G>alter table test add constraint pk_test primary key (a,b);

  Table altered.

  SQL 10G>create index ind_test on test(b,c);

  Index created.

  然后,我们检查刚才创建的约束与索引

  SQL 10G>select t.constraint_name,c.constraint_type,t.column_name,t.position,c.status,c.validated

  2 from user_cons_columns t,user_constraints c

  3 where c.constraint_name=t.constraint_name

  4 and c.constraint_type != 'C'

  5 and t.table_name = 'TEST'

  6 order by constraint_name,position;

  CONSTRAINT_NAME C COLUMN_NAME POSITION STATUS VALIDATED

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

  PK_TEST P A 1 ENABLED VALIDATED

  PK_TEST P B 2 ENABLED VALIDATED

  SQL 10G>select t.index_name,t.column_name,t.column_position,i.status

  2 from user_ind_columns t,user_indexes i

  3 where t.index_name=i.index_name

  4 and t.table_name = 'TEST'

  5* order by index_name,column_position

  INDEX_NAME COLUMN_NAME COLUMN_POSITION STATUS

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

  IND_TEST B 1 VALID

  IND_TEST C 2 VALID

 现在,我们先删除索引上的字段,其实并没有物理删除,只是设置为unused:


  SQL 10G>ALTER TABLE test SET UNUSED (c);
  Table altered.
  SQL 10G>select t.index_name,t.column_name,t.column_position,i.status
  2 from user_ind_columns t,user_indexes i
  3 where t.index_name=i.index_name
  4 and t.table_name = 'TEST'
  5 order by index_name,column_position;
  no rows selected

  发现了什么,索引也删除了。那我们再删除约束上的字段呢?


  SQL 10G>ALTER TABLE test SET UNUSED (b);
  ALTER TABLE test SET UNUSED (b)
  *
  ERROR at line 1:
  ORA-12991: column is referenced in a multi-column constraint
  SQL 10G>ALTER TABLE test SET UNUSED (b) CASCADE CONSTRAINTS;
  Table altered.
  SQL 10G>select t.constraint_name,c.constraint_type,t.column_name,t.position,c.status,c.validated
  2 from user_cons_columns t,user_constraints c
  3 where c.constraint_name=t.constraint_name
  4 and c.constraint_type != 'C'
  5 and t.table_name = 'TEST'
  6 order by constraint_name,position;
  no rows selected

  我们可以看到,正常的删除会报一个错误,如果我们指定了cascade,将会把对应的约束也删除。

  我们看完了Oracle的处理过程,再看看mysql是这么处理删除索引上字段这个事情的

  mysql> create table test(a int,b int,c int);

  Query OK, 0 rows affected (0.72 sec)

  mysql> alter table test add primary key(a,b);

  Query OK, 0 rows affected (0.27 sec)

  Records: 0 Duplicates: 0 Warnings: 0

  mysql> create index ind_test on test(b,c);

  Query OK, 0 rows affected (0.32 sec)

  Records: 0 Duplicates: 0 Warnings: 0

  我们执行同样的操作,先删除复合索引中的一个字段,然后删除约束中的一个字段。

  mysql> alter table test drop c;

  Query OK, 0 rows affected (0.58 sec)

  Records: 0 Duplicates: 0 Warnings: 0

  mysql> show index from test;

  +-------+------------+----------+--------------+-------------+-----------+

  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |

  +-------+------------+----------+--------------+-------------+-----------+

  | test | 0 | PRIMARY | 1 | a | A |

  | test | 0 | PRIMARY | 2 | b | A |

  | test | 1 | ind_test | 1 | b | A |

  +-------+------------+----------+--------------+-------------+-----------+

  3 rows in set (0.06 sec)

  mysql> alter table test drop b;

  Query OK, 0 rows affected (0.28 sec)

  Records: 0 Duplicates: 0 Warnings: 0

  mysql> show index from test;

  +-------+------------+----------+--------------+-------------+-----------+

  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |

  +-------+------------+----------+--------------+-------------+-----------+

  | test | 0 | PRIMARY | 1 | a | A |

  +-------+------------+----------+--------------+-------------+-----------+

  1 row in set (0.03 sec)

  可以看到,mysql的处理方式是有差别的,mysql仅仅是把字段从索引中拿掉,而不是删除该索引。

  本文的意思,就是想提醒大家,平常在做columns删除的时候,包括unused,一定要小心,是否有复合索引包含了该字段,否则,一不小心把索引删除了,可能将引发大的错误。

  评论这张
 
阅读(363)| 评论(0)

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018