导读 | 移动一张表实际上是一个重组过程,数据库会将原来的数据复制到新的地方。但是如果你发现这个表在移动后性能下降了,可能是你的索引没有重建。本文将指导您找到依赖索引并重建它们。 |
移动一张表实际上是一个重组过程,数据库会将原来的数据复制到新的地方。但是如果你发现这个表在移动后性能下降了,可能是你的索引没有重建。本文将指导您找到依赖索引并重建它们。
SQL> select tablespace_name from dba_tables where owner = 'HR' and table_name = 'EMPLOYEES'; TABLESPACE_NAME ------------------------------ EXAMPLE SQL> alter table hr.employees move tablespace users; Table altered. SQL> select tablespace_name from dba_tables where owner = 'HR' and table_name = 'EMPLOYEES'; TABLESPACE_NAME ------------------------------ USERS
SQL> column index_name format a30; SQL> column tablespace_name format a30; SQL> column status format a10; SQL> select index_name, tablespace_name, status from dba_indexes where owner = 'HR' and table_name = 'EMPLOYEES'; INDEX_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ---------- EMP_JOB_IX EXAMPLE UNUSABLE EMP_DEPARTMENT_IX EXAMPLE UNUSABLE EMP_MANAGER_IX EXAMPLE UNUSABLE EMP_NAME_IX EXAMPLE UNUSABLE EMP_EMAIL_UK EXAMPLE UNUSABLE EMP_EMP_ID_PK EXAMPLE UNUSABLE 6 rows selected.
如您所见,所有依赖索引都是UNUSABLE。这意味着,数据库不会自动重建它们。你必须自己做。
SQL> select 'alter index ' || owner || '.' ||index_name || ' rebuild tablespace users;' as SQL_TO_BE_EXECUTED from dba_indexes where owner = 'HR' and table_name = 'EMPLOYEES'; SQL_TO_BE_EXECUTED -------------------------------------------------------------------------------- alter index EMP_JOB_IX rebuild tablespace users; alter index EMP_DEPARTMENT_IX rebuild tablespace users; alter index EMP_MANAGER_IX rebuild tablespace users; alter index EMP_NAME_IX rebuild tablespace users; alter index EMP_EMAIL_UK rebuild tablespace users; alter index EMP_EMP_ID_PK rebuild tablespace users; 6 rows selected.
SQL> select 'alter index ' || owner || '.' ||index_name || ' rebuild tablespace ' || tablespace_name || ';' as SQL_TO_BE_EXECUTED from dba_indexes where owner = 'HR' and table_name = 'EMPLOYEES'; SQL_TO_BE_EXECUTED -------------------------------------------------------------------------------- alter index HR.EMP_DEPARTMENT_IX rebuild tablespace EXAMPLE; alter index HR.EMP_NAME_IX rebuild tablespace EXAMPLE; alter index HR.EMP_MANAGER_IX rebuild tablespace EXAMPLE; alter index HR.EMP_EMP_ID_PK rebuild tablespace EXAMPLE; alter index HR.EMP_EMAIL_UK rebuild tablespace EXAMPLE; alter index HR.EMP_JOB_IX rebuild tablespace EXAMPLE; 6 rows selected.
请注意,我们在新表空间USERS中重建索引。也就是说,对于索引,REBUILD相当于表中的MOVE
SQL> select index_name, tablespace_name, status from dba_indexes where owner = 'HR' and table_name = 'EMPLOYEES'; INDEX_NAME TABLESPACE_NAME STATUS ------------------------------ ------------------------------ ---------- EMP_JOB_IX USERS VALID EMP_DEPARTMENT_IX USERS VALID EMP_MANAGER_IX USERS VALID EMP_NAME_IX USERS VALID EMP_EMAIL_UK USERS VALID EMP_EMP_ID_PK USERS VALID 6 rows selected.
所有索引都变为VALID,表明所重建的索引有效。
原文来自:https://www.toutiao.com/article/7101609228544426528/?log_from=4dae10729fa06_1653527364683
本文地址:https://www.linuxprobe.com/after-the-oracle-table-moves.html编辑:J+1,审核员:逄增宝
Linux命令大全:https://www.linuxcool.com/
Linux系统大全:https://www.linuxdown.com/
红帽认证RHCE考试心得:https://www.rhce.net/