AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

关于alter table move

2016-06-02 1288
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 关于alter table movealter table move 主要有两方面的作用。1、用来移动table 到其他表空间。2、用来减少table 中的存储碎片，优化存储空间和性能。
关于alter table move
alter table move 主要有两方面的作用。
1、用来移动table 到其他表空间。
2、用来减少table 中的存储碎片，优化存储空间和性能。
针对第一点。
在system 表空间创建一个表。
SQL> create table t as select * from dba_objects;
Table created.
SQL>  select table_name,tablespace_name
  2   from user_tables
  3   where table_name = 'T';
TABLE_NAME      TABLESPACE_NAME
--------------- ---------------
T               SYSTEM
在其上创建一个索引。
SQL> create index t_idx on t (object_id);
Index created.
SQL> select index_name,status
  2  from user_indexes
  3  where table_name = 'T';
INDEX_NAME                                                   STATUS
------------------------------------------------------------ ---------
T_IDX                                                        VALID
我们知道不应该把业务或者也管理无关的数据存放在system 表空间。 所以我们把t 表移动到users 表空间去吧。
**********************************************************
注：用户在目标表空间应该有足够的表空间配额。
SQL> conn t/test
Connected.
SQL>  create table emp as select * from hr.employees;
Table created.
SQL> alter table emp move tablespace system;
alter table emp move tablespace system
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'
**********************************************************
SQL> alter table t move tablespace users;
Table altered.
现在索引变成无效了。因为索引是通过rowid 来定位记录的，所以当table  迁移到其他表空间，或者进行alter table ...move 清理碎片的时候已经无效了。
SQL> select index_name,status
  2  from user_indexes
  3  where table_name = 'T';
INDEX_NAME                                                   STATUS
------------------------------------------------------------ ----------
T_IDX                                                        UNUSABLE
重建index。
SQL> alter index t_idx rebuild tablespace users;
Index altered.
SQL>  select index_name,tablespace_name,status
  2   from user_indexes
  3   where table_name = 'T';
INDEX_NAME           TABLESPACE_NAME STATUS
-------------------- --------------- ----------------
T_IDX                USERS           VALID
还需要注意的问题：
在alter table...move... 语句的时候将会在目标对象上放置X锁，如果需要移动的对象 比较大，将会花费比较长的时候，所以X锁的时间也会比较长。如果目标表上已经放置 了一个X锁，需要等到X的释放，才能够执行该语句。
***********************************************************************
注：对于IOT 表我们可以使用 alter table ... move ... 的online 子句。这时候目标表 是可用的， 可以在其上执行select,DML 操作。
SQL> create table t_emp(
  2  employee_id number(6),
  3  last_name   varchar2(20),
  4  first_name  varchar2(25),
  5  constraint t_emp_pk primary key(employee_id))
  6  organization index;
Table created.
SQL> insert into t_emp select employee_id,last_name,first_name
  2  from hr.employees;
108 rows created.
SQL> commit;
Commit complete.
SQL> alter table t_emp move tablespace users online;
Table altered.
在执行上述 alter table t_emp move tablespace users online; 语句的时候，在其他会话中 可以正常的访问t_emp 表。
**************************************************************************
只对第二点。
有一些表经常性的执行删除而很少执行插入，这时候表所在的segment 中将会存在较多 的碎片，我们可以使用alter table move 进行整理，这样可以降低高水位线，减少full table scan 读取的block 的数量并且可以提高data buffer cache 的效率，因为缓存的data block 实打实的 存在内容。
SQL> create table t as select object_id,object_name
  2  from dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
下面使用的这个存储过程我是从asktom 下载的procedure code 创建的。
Total Blocks 表示分配给表的总的blocks 数。
Unused Blocks 表示位于高水位线以上的从未使用的数据块个数。
SQL> set serveroutput on
SQL>  exec show_space('T')
Free Blocks.............................0
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................68
Unused Bytes............................557056Last Used Ext FileId....................1
Last Used Ext BlockId...................134528
Last Used Block.........................60
PL/SQL procedure successfully completed.
SQL> delete from t
  2  where object_id < 35000;
34549 rows deleted.
SQL> commit;
Commit complete.
SQL>  exec show_space('T')--delete 并不会释放存储空间，不会降低hwm。
Free Blocks.............................161
Total Blocks............................384
Total Bytes.............................3145728
Unused Blocks...........................68
Unused Bytes............................557056
Last Used Ext FileId....................1
Last Used Ext BlockId...................134528
Last Used Block.........................60
PL/SQL procedure successfully completed.
执行alter table ... move 清理表中的存储碎片。实质上是读取t，然后在t 表 所在的表空间重建表t 。等move 操作完成以后删除原来的表.所以需要额外的
存储空间开销，所以当前表空间的大小至少为表大小的2倍。
 SQL> alter table t move;
Table altered.
SQL> exec show_space('T')
Free Blocks.............................0
Total Blocks............................256
Total Bytes.............................2097152
Unused Blocks...........................101
Unused Bytes............................827392
Last Used Ext FileId....................1
Last Used Ext BlockId...................134656
Last Used Block.........................27
PL/SQL procedure successfully completed.
hwm 降下来了，并且还回收了一部分分配给表的blocks 。
************************************************************************
注：
有一种说法是  alter table ... move 可以降低表的hwm,但是不能够释放已经分配给他的 blocks ，可能这样明明看到Total Blocks 减小了。我们还可以通过查看 user_extents 来确定。
没有执行delete 和alter table ... move 以前的情形。
SQL> select segment_name,bytes from
  2  user_extents
  3  where segment_name = 'T';
SEGMENT_NAME              BYTES
-------------------- ----------
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
SEGMENT_NAME              BYTES
-------------------- ----------
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                       1048576
T                       1048576
18 rows selected.
执行delete 和alter table ... move 以后的情形。
SQL> select segment_name,bytes from
  2  user_extents
  3  where segment_name = 'T';
SEGMENT_NAME              BYTES
-------------------- ----------
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
SEGMENT_NAME              BYTES
-------------------- ----------
T                         65536
T                         65536
T                         65536
T                         65536
T                         65536
T                       1048576
17 rows selected.

文章标签：
SQL
索引
存储
Perl
.NET
物联网
缓存
开发框架
苍雪明南
目录
相关文章
龙腾九州
|
5月前
|
SQL Oracle 关系型数据库
ALTER TABLE
【7月更文挑战第19天】ALTER TABLE 。
龙腾九州
64 5
米果粒
|
5月前
|
存储
DROP TABLE 语句
【7月更文挑战第20天】DROP TABLE 语句。
米果粒
84 2
小魔贵
|
7月前
|
存储 SQL 关系型数据库
CREATE TABLE语句
在MySQL中，使用CREATE TABLE语句来创建表。你需要指定表名和列的定义，包括列名、数据类型以及约束等，结合实际存储和上一课学习的数据类型选取合适的。创建一个book_types表
小魔贵
221 0
史一试
|
JavaScript 前端开发 数据可视化
vxe-table
vxe-table
史一试
758 0
vxe-table
vohelon
|
SQL 数据库
CREATE TABLE 语句
CREATE TABLE 语句
vohelon
133 1
vohelon
|
SQL 数据库
CREATE TABLE
CREATE TABLE
vohelon
163 0
xaubllxwtvaqiu
ALTER TABLE
本文主要介绍如何对表相关的Sequence类型进行修改。
xaubllxwtvaqiu
87 0
memos1
|
Web App开发 关系型数据库
Truncate/Delete/Drop table的特点和区别
之前一直对Truncate/Delete/Drop认识的不是很清晰，所以特意的翻了一下MySQL5.7 Reference Manual，准备系统的了解一下，这里是一些翻译，外加一点自己的认知。
memos1
1242 0
兰春
|
关系型数据库 MySQL
truncate table 和 drop table 的一点坑
网上一搜这个关键字，得到的结果大多都是delete、truncate、drop之间的区别 但是今天我们要讲的内容，是我们在生产环境中遇到的真实案例 互联网公司一般对大表，都会采用分区表或者物理分表吧，这里主要描述的是分表的删除过程中的问题 案例一 环境MySQL5.
兰春
4067 0
whatcat
|
索引 关系型数据库 PostgreSQL
ALTER TABLE & ALTER TYPES
摘要： 主要的内容是alter, alter table, alter type, length, postgresql 9.2, rewrite, reindex，转换函数不需要重写表和索引
whatcat
1517 0

热门文章

最新文章

  • 1
    Hadoop迁移MaxCompute神器之DataX-On-Hadoop使用指南
  • 2
    Nginx的启动、停止与重启
  • 3
    Hive之数据倾斜的原因和解决方法
  • 4
    开发人员各级岗位胜任力模型
  • 5
    Node.js 中流操作实践
  • 6
    传奇龙版地图查看器
  • 7
    调整DC的网络对网络是否会有影响?
  • 8
    How to maintain Oracle10g Recyclebin?
  • 9
    你知道自己感染了恶意软件吗？
  • 10
    Groovy 快速入门
  • 1
    Python学习的自我理解和想法（9）
    90
  • 2
    Kimi 上线视觉思考模型，K1 系列强化学习模型正式开放，无需借助外部 OCR 处理图像与文本进行思考并回答
    103
  • 3
    CosyVoice 2.0：阿里开源升级版语音生成大模型，支持多语言和跨语言语音合成，提升发音和音色等的准确性
    84
  • 4
    Megrez-3B-Omni：无问芯穹开源最强端侧全模态模型，支持理解图像、音频和文本三种模态数据
    52
  • 5
    FreeScale：无需微调即可提升模型的图像生成能力，生成 8K 分辨率的高质量图像
    39
  • 6
    Freestyler：微软联合西工大和香港大学推出说唱音乐生成模型，支持控制生成的音色、风格和节奏等
    38
  • 7
    《C 语言与统计假设检验：洞察人工智能模型性能差异》
    83
  • 8
    《C 语言赋能：物联网环境下人工智能应用的能耗优化之道》
    24
  • 9
    《C 语言助力神经网络剪枝：优化模型的卓越之道》
    25
  • 10
    《C 语言复数运算：赋能人工智能信号处理新境界》
    20

    • 相关电子书

    更多
  • Dynamic DDL Adding Structure t
  • 低代码开发师（初级）实战教程
  • 阿里巴巴DevOps 最佳实践手册
    • 下一篇
    fs.oss.accessKeyId和fs.oss.accessKeySecret。