rename 表操作后index是否依然可用

来源：互联网发布：淘宝到家是干嘛的编辑：程序博客网时间：2024/06/05 14:38

今天群里有人问起rename 表操作对index的影响。今天简单说一下：

首先ORACLE官方文档对RENAME操作的说明如下：

Use theRENAME statement to rename a table, view, sequence, or private synonym.

Oracle Database automatically transfers integrity constraints, indexes, and grants on the old object to the new object.
Oracle Database invalidates all objects that depend on the renamed object, such as views, synonyms, and stored procedures and functions that refer to a renamed table.

［可以使用rename语句来rename一个表，视图，序列或私有同义词］。

.Oracle数据库自动把旧的对象上的完整性约束，索引，和权限迁移到新的对象上面。

.Oracle数据库上涉及与命名后的对象有关的例如:视图，同义词和存储过程和函数都会失效。

1.使用scott用户创建一个测试表e1和测试索引e1_eno.

SQL> create table e1 as select * from emp;

Table created.

SQL> create index e1_eno on e1(empno);

Index created.

2.分析执行计划。

SQL> explain plan for select * from e1 where empno>7500;

Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1044 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| E1 | 12 | 1044 | 2 (0)| 00:0
0:01 |

|* 2 | INDEX RANGE SCAN | E1_ENO | 12 | | 1 (0)| 00:0
0:01 |

3.查看index情况。

SQL> select index_name,table_owner,table_name,status from user_indexes where index_name='E1_ENO';

INDEX_NAME                     TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME                     STATUS
------------------------------ --------
E1_ENO                         SCOTT
E1                             VALID

4.进行rename操作.

SQL> rename e1 to e2;

Table renamed.

5.查询索引情况：

SQL> select index_name,table_owner,table_name,status from user_indexes where index_name='E1_ENO';

INDEX_NAME                     TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME                     STATUS
------------------------------ --------
E1_ENO                         SCOTT
E2                             VALID
6.按照第二步进行分析，执行计划如下：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1044 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| E2 | 12 | 1044 | 2 (0)| 00:0
0:01 |

|* 2 | INDEX RANGE SCAN | E1_ENO | 12 | | 1 (0)| 00:0

发现更改表名后，索引也同时跟着变动，依旧有效。

7.再次更换为e1看看索引情况：

SQL> rename e2 to e1;

Table renamed.

SQL> select index_name,table_owner,table_name,status from user_indexes where index_name='E1_ENO';

8.根据第2步再次查看执行计划：

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1044 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| E1 | 12 | 1044 | 2 (0)| 00:0
0:01 |

|* 2 | INDEX RANGE SCAN | E1_ENO | 12 | | 1 (0)| 00:0
0:01 |

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

总结：由此可知：rename 表后，相应的索引会随着表的变动同步变动。依旧是有效的。不需要我们手动修改。

我们也可以测试一下rename 表后，同义词是否有效：

1.使用scott给e1表创建一个同义词s_e.

create synonym s_e for e1;

2.查询同义词信息

SQL> select synonym_name,table_owner,table_name from user_synonyms;

SYNONYM_NAME TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
S_E SCOTT
E1
3.重命名

SQL> rename e1 to e2;

Table renamed.
4.查询s_e

SQL> select synonym_name,table_owner,table_name from user_synonyms;

SYNONYM_NAME TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
S_E SCOTT
E1

SQL> select * from s_e;
select * from s_e
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

5.重新更改为e1.

SQL> rename e2 to e1;

Table renamed.

SQL> select synonym_name,table_owner,table_name from user_synonyms;

SYNONYM_NAME TABLE_OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
S_E SCOTT
E1

6.查询s_e

发现ok.

总结：rename表后，同义词不会同步，因此rename表后，同义词失效。

我们再测试一下rename表对视图的影响：

1.在e1表的基础上创建视图v_e1

SQL> create view v_e1 as select empno,ename,sal from e1;

View created.

SQL> select * from v_e1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
2.rename 表

SQL> rename e1 to e2;

Table renamed.
3.验证一下view是否可用。
     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
SQL> select * from v_e1;
select * from v_e1
              *
ERROR at line 1:
ORA-04063: view "SCOTT.V_E1" has errors
4.重新改为e1

SQL> rename e2 to e1;

Table renamed.

SQL> select * from v_e1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
5.再次验证视图的有效性

SQL> rename e2 to e1;

Table renamed.

SQL> select * from v_e1;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250

总结：rename后，view同同义词一样失效。

1 0