Forum Stats

  • 3,873,334 Users
  • 2,266,535 Discussions
  • 7,911,512 Comments

Discussions

Is renaming an index an online operation?

Doevelaar
Doevelaar Member Posts: 10
edited Feb 28, 2014 7:30AM in General Database Discussions

Hello all,

I need to rename an index on a busy and large table and I was wondering if this would be an online operation: can users coninue work when I'm doing this? I can't find the answer in the docs.

Kind regards.

Tagged:
Martin PreissHemant K ChitaleGirish SharmaAnand...Sunny kichloo

Best Answer

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    Answer ✓

    "online" means that the command can be issued.  Whether it will be successful depends on whether there is active DML

    Demo :

    Session 1:

    SQL>create table hkc_test_10 (id_column number, data_col varchar2(15));

    Table created.

    SQL>create index hkc_test_10_ndx on hkc_test_10(id_column);

    Index created.

    SQL>insert into hkc_test_10
      2  select rownum, 'Row:' || to_char(rownum)
      3  from dual
      4  connect by level < 1000;

    999 rows created.

    SQL>

    Session 2 when Session 1 hasn't committed :

    AnotherSQLSessn>select index_name from user_indexes where table_name = 'HKC_TEST_10';

    INDEX_NAME
    ------------------------------
    HKC_TEST_10_NDX

    1 row selected.

    AnotherSQLSessn>alter index hkc_test_10_ndx rename to ndx_hkc_test_10;
    alter index hkc_test_10_ndx rename to ndx_hkc_test_10
                *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


    AnotherSQLSessn>

    Hemant K Chitale

    Girish SharmaMartin PreissSunny kichloo
«1

Answers

  • Sergei Krasnoslobodtsev
    Sergei Krasnoslobodtsev Member Posts: 495 Silver Badge
    edited Feb 26, 2014 9:22AM

    Altering Indexes

    Renaming an Index

    To rename an index, issue this statement:
    ALTER INDEX index_name RENAME TO new_name; 

    For example :

    drop table test_tab;
    create table test_tab (x int constraint test_tb_pk primary key );
    select i.INDEX_NAME,i.TABLE_NAME from user_indexes i where i.TABLE_NAME = 'TEST_TAB';
    alter index test_tb_pk rename to test_tab_pk;
    select i.INDEX_NAME,i.TABLE_NAME from user_indexes i where i.TABLE_NAME = 'TEST_TAB';
    
    SQL >
    Table created
    
    INDEX_NAME                     TABLE_NAME
    ------------------------------ ------------------------------
    TEST_TB_PK                     TEST_TAB
    
    Index altered
    
    INDEX_NAME                     TABLE_NAME
    ------------------------------ ------------------------------
    TEST_TAB_PK                    TEST_TAB
    
    
    
    
    
    
    
    

    ORACLE-BASE - Oracle9i Renaming columns and constraints

  • carajandb
    carajandb Member Posts: 260 Bronze Badge

    Simple test with Oracle 11gR2:

    update table set col1 = 'xxx';
    (col1 is indexed and no commit)

    alter index idxcol1 rename to newindex;

    it works so I can assume that rename index is an online operation!

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy

    I don't see a reason why it should not be an online operation: it's just a small change in metadata. But I hope you did not use index hints containing the index name ...

  • Mohamed Houri
    Mohamed Houri Member Posts: 1,280 Bronze Trophy
    edited Feb 26, 2014 9:21AM
  • tvCa-Oracle
    tvCa-Oracle Member Posts: 1,736

    Keep in mind there are 2 levels : when the DBA says it's an online action, it means it can be done while the database is running in normal mode.

    Real online is to be viewed from the application side : does the application continue to work normally - on that specific index - while such an action is going on.

    Hemant K Chitale
  • Aman....
    Aman.... Member Posts: 22,919 Gold Crown

    I don't think that uses would be in a hung state or something if you are going to rename the index .

    Aman....

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond
    Answer ✓

    "online" means that the command can be issued.  Whether it will be successful depends on whether there is active DML

    Demo :

    Session 1:

    SQL>create table hkc_test_10 (id_column number, data_col varchar2(15));

    Table created.

    SQL>create index hkc_test_10_ndx on hkc_test_10(id_column);

    Index created.

    SQL>insert into hkc_test_10
      2  select rownum, 'Row:' || to_char(rownum)
      3  from dual
      4  connect by level < 1000;

    999 rows created.

    SQL>

    Session 2 when Session 1 hasn't committed :

    AnotherSQLSessn>select index_name from user_indexes where table_name = 'HKC_TEST_10';

    INDEX_NAME
    ------------------------------
    HKC_TEST_10_NDX

    1 row selected.

    AnotherSQLSessn>alter index hkc_test_10_ndx rename to ndx_hkc_test_10;
    alter index hkc_test_10_ndx rename to ndx_hkc_test_10
                *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


    AnotherSQLSessn>

    Hemant K Chitale

    Girish SharmaMartin PreissSunny kichloo
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown

    The example showed by Hemant tells us, that if there are active DML or pending transaction on the table then you can not drop/rename the index in another session, you can drop/rename in only that session who have started the transaction.  So, if you gets "Index altered." message in your session, it means there were not any active DML at the time of your drop/rename operation.

    Regards

    Girish Sharma

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

    If you issue an ALTER in the same session as having the active DML, the first thing the ALTER does is that it issues a COMMIT.   So the transaction is committed, resulting in the DML being not active any more.  That is why the ALTER INDEX ... RENAME goes through -- because it causes a COMMIT of the active DML in the same session first.

    *ALWAYS* be wary of the "side-effect" that DML in the same session is committed when you issue DDL.

    Hemant K Chitale

    Girish SharmaMartin PreissAnand...Hemant K Chitale
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown

    Thank you Hemant for more clarification.

    Regards

    Girish Sharma

This discussion has been closed.