Forum Stats

  • 3,875,193 Users
  • 2,266,849 Discussions
  • 7,912,104 Comments

Discussions

Categories

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
· Share on TwitterShare on Facebook

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
    · Share on TwitterShare on Facebook
«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

    · Share on TwitterShare on Facebook
  • 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!

    · Share on TwitterShare on Facebook
  • 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 ...

    · Share on TwitterShare on Facebook
  • Mohamed Houri
    Mohamed Houri Member Posts: 1,284 Bronze Trophy
    edited Feb 26, 2014 9:21AM

    And I hope he didn't use a SPM baseline with that index name

    http://hourim.wordpress.com/2014/01/02/sql-plan-management-what-matters-the-index-name-or-the-indexed-columns/

    Best regards

    Mohamed Houri

    Martin Preiss
    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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....

    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook
  • 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
    · Share on TwitterShare on Facebook
  • Girish Sharma
    Girish Sharma Member Posts: 4,980 Bronze Crown

    Thank you Hemant for more clarification.

    Regards

    Girish Sharma

    · Share on TwitterShare on Facebook
This discussion has been closed.