Forum Stats

  • 3,751,483 Users
  • 2,250,366 Discussions
  • 7,867,436 Comments

Discussions

如何重构组合主键列的顺序,并重构对应的索引??

957155
957155 Member Posts: 13
edited Aug 22, 2012 5:49AM in 数据库
一个组合列的主键,现在发现列的顺序不对,导致索引的顺序不对,
现在想改变这个顺序,不能动数据,这个有什么方法、
只能删除再重建??

Best Answer

  • Maclean Liu
    Maclean Liu Member Posts: 84
    Accepted Answer
    ODM TEST:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
    PL/SQL Release 10.2.0.5.0 - Production
    CORE    10.2.0.5.0      Production
    TNS for Linux: Version 10.2.0.5.0 - Production
    NLSRTL Version 10.2.0.5.0 - Production
    
    SQL> create table colA(t1 int,t2 int,t3 int);
    
    Table created.
    
    
    SQL> create index ind1 on colA(t1,t2);
    
    Index created.
    
    SQL> create index ind2 on colA(t2,t1) online;
    
    Index created.
    
    SQL> drop index ind1;
    
    Index dropped.
    如以上演示 你可以先以create index online 方式在线创建你认为column order正确的索引,之后将不要的索引drop掉。

    记得收集必要的索引统计信息。


    另外除非 索引的column order影响到了执行计划,否则没必要去纠结这一点。

Answers

  • Maclean Liu
    Maclean Liu Member Posts: 84
    Accepted Answer
    ODM TEST:
    SQL> select * from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
    PL/SQL Release 10.2.0.5.0 - Production
    CORE    10.2.0.5.0      Production
    TNS for Linux: Version 10.2.0.5.0 - Production
    NLSRTL Version 10.2.0.5.0 - Production
    
    SQL> create table colA(t1 int,t2 int,t3 int);
    
    Table created.
    
    
    SQL> create index ind1 on colA(t1,t2);
    
    Index created.
    
    SQL> create index ind2 on colA(t2,t1) online;
    
    Index created.
    
    SQL> drop index ind1;
    
    Index dropped.
    如以上演示 你可以先以create index online 方式在线创建你认为column order正确的索引,之后将不要的索引drop掉。

    记得收集必要的索引统计信息。


    另外除非 索引的column order影响到了执行计划,否则没必要去纠结这一点。
  • 957155
    957155 Member Posts: 13
    那创建主键时的顺序不用管了吧?
  • Maclean Liu
    Maclean Liu Member Posts: 84
    一张表只能有一个主键primary key 和对应的主键索引:

    SQL> alter table colA add primary key(t1 ,t2);
    
    Table altered.
    
    
    SQL> alter table colA add primary key(t2,t1);
    alter table colA add primary key(t2,t1)
                         *
    ERROR at line 1:
    ORA-02260: table can have only one primary key
    
    
    SQL> ! oerr ora 2260
    02260, 00000, "table can have only one primary key"
    // *Cause: Self-evident.
    // *Action: Remove the extra primary key.
    
    
    SQL> alter table colA drop primary key;  
    
    Table altered.
    
    SQL> alter table colA add primary key(t2,t1);
    
    Table altered.
    主键索引的 字段顺序也存在区别
This discussion has been closed.