- 浏览: 241875 次
- 性别:
- 来自: 杭州
文章分类
最新评论
-
nodonkey:
貌似还是不行,再等等吧,amfphp要出2.0了
amfphp1.9与php5.3.X版本不兼容 -
live711:
请问amfphp与php5.3.X搭配能用了吗?
amfphp1.9与php5.3.X版本不兼容 -
zhousheng193:
非常感谢!
安装flash player debug版本遇到的一些问题 -
sp42:
谢谢提示,我遇到的也是不能加密,用MAC地址代替之。
DI-624+A路由器韧体升级解决经常掉线的问题(转) -
心似海:
不错,要挖去了,哈哈
深入sql之merge into
转自:http://hi.baidu.com/kywinder/blog/item/f96e62d4aa69c82306088b6f.html
案例:
同事将一关键表中删了多余的300w条数据后,程序就变的异常缓慢。分析得出,应该是表空间碎片过多,旧的索引效率过低。
执行下面两句话:
alter table ycsbt_qyygxx_jb move;
alter index R_SBXX_YCSBD_FK rebuild online;
效果非常明显。
deltete不会释放表空间,但是可以重用,也就是插入可以填补空洞,当然现实应用中确实是存在经常删除很少插入的情况,这样就存在了释放表空间优化数据库的可行性了,truncate有不能带条件的缺陷,自然就想到用alter table move重移表空间的方法。这里要注意三个要素 1、 alter table move 省略了tablespace XXX, 表示用户移到自己默认的表空间,因此当前表空间至少要是该表两倍大,这很好理解,由于易错所以提出,就不再细说了。 2、 alter table move过程中会导致索引失效,必须要考虑重新索引 3、 alter table move过程中会产生锁,应该避免在业务高峰期操作! 就第二点和第三点做实验说明如下吧 Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as ljb 先获取该SESSION的SID,方便实验观察 SQL> select sid from v$mystat where rownum=1; SID -------------------- 160 SQL> create table ljb_test as select * from dba_objects; Table created SQL> select count(*) from ljb_test; COUNT(*) ------------------- 62659 SQL> create index idx_test on ljb_test(object_id); Index created 查询当前该SESSION并无锁 SQL> select * from v$lock where sid=160; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------------------------------------- 查看索引状态也正常! SQL> select index_name,table_name,status from user_indexes where table_name='LJB_TEST'; INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ ----------------------------------------------- IDX_TEST LJB_TEST VALID alter table ljb_test move; 重新再开一个窗口 执行如下命令,发现锁已经产生了 select * from v$lock where sid=160; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ------ ---- ------- ---------- ------ -------- ------ ------------------------------------------------------------------ 2043451C 20434530 160 CF 0 0 4 0 0 0 1FA072BC 1FA073D8 160 TX 917534 592 6 0 1 0 204344C0 204344D4 160 HW 76 323783147 6 0 0 0 1F9C4224 1F9C423C 160 TM 84825 0 6 0 0 0 204342F4 20434308 160 TT 76 16 4 0 0 0 1F9C377C 1F9C37C4 160 TS 76 323783147 6 0 0 0 不过由于alter table move命令未结束,索引仍然有效! SQL> select index_name,table_name,status from user_indexes where table_name='LJB_TEST'; INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ ---------------------------------------------------- IDX_TEST LJB_TEST VALID 等alter table ljb_test move;命令结束后,再查看发现锁消失了 SQL> select * from v$lock where sid=160; ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ------------------------------------------ 但是索引却失效了! SQL> select index_name,table_name,status from user_indexes where table_name='LJB_TEST'; INDEX_NAME TABLE_NAME STATUS ------------------------------ ------------------------------ ---------------------------------------------------- IDX_TEST LJB_TEST UNUSABLE 总结:这个实验说明:除了知道alter table move命令可以释放空间(当然这语句最根本的作用还是移动表到不同的表空间去,这里只是借用它可以释放空间的一个特性),还要了解该动作会锁表直到命令结束,而且会导致索引失效,属于危险命令,建议千万不要在业务高峰期操作。
都知道alter table move 或shrink space可以收缩段,用来消除部分行迁移,消除空间碎片,使数据更紧密,但move 跟shrink space还是有区别的。
Move会移动高水位,但不会释放申请的空间,是在高水位以下(below HWM)的操作。
而shrink space 同样会移动高水位,但也会释放申请的空间,是在高水位上下(below and above HWM)都有的操作。
也许很难理解吧,看测试就知道了。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> col SEGMENT_NAME for a10
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 10 1280 10
SQL> col TABLE_NAME for a10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 1280
--TEST表初始分配了10M的空间,可以看到有10个EXTENTS,1280个BLOCKS。USER_TABLES视图显示有0个使用的BLOCKS,1280个空闲BLOCKS,即该10M空间内的BLOCK都还没被ORACLE”格式化”。
SQL> begin
2 for i in 1..100000 loop
3 insert into test values(i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
--插入10W条数据后,分配的空间仍不变,因为10个EXTENTS还没使用完。显示使用了186个BLOCKS,空闲1094个BLOCKS。这时候的186BLOCKS即是高水位线
SQL> delete from test where rownum<=50000;
50000 rows deleted.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 186 1094
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test;
USED_BLOCKS
-----------
77
--这边可以看到,删掉一半数据后,仍然显示使用了186个BLOCKS,高水位没变。但查询真正使用的BLOCK数只有77个。所以DELETE操作是不会改变HWM的
SQL> alter table test move;
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 81 1199
--MOVE之后,HWM降低了,空闲块也上去了
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 10 1280
--但是分配的空间并没有改变,仍然是1280个BLOCKS。下面看用SHRINK SPACE的方式
SQL> alter table test enable row movement;
Table altered.
SQL> alter table test shrink space;
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS
---------- ---------- ----------
TEST 1 88
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 81 7
--分配的空间已经降到最小,1个EXTENTS ,88个BLOCKS
所以MOVE并不算真正意义上的压缩空间,只会压缩HWM以下的空间,消除碎片。我们一般建表时没有指定initial参数(默认是8个BLOCK),也就感觉不到这个差异。而SHRINK SPACE真正做到了对段的压缩,包括初始分配的也压了,所以它是blow and above HWM操作。
至于需要哪种方法,得看你的需求来了,需要分析表的增长情况,要是以后还会达到以前的HWM高度,那显然MOVE是更合适的,因为SHRINK SPACE还需要重新申请之前放掉的空间,无疑增加了操作。
注意:
1.不过用MOVE的方式也可以做到真正的压缩分配空间,只要指定STORAGE参数即可。
SQL> drop table test;
Table dropped.
SQL> create table test (id number) storage (initial 10m next 1m) tablespace users;
Table created.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 10 1280 10
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 1280
SQL> alter table test move storage (initial 1m);
Table altered.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME
NT_NAME='TEST';
SEGMENT_NA EXTENTS BLOCKS INIT
---------- ---------- ---------- ----------
TEST 16 128 1
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST';
TABLE_NAME BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
TEST 0 128
2.使用move时,会改变一些记录的ROWID,所以MOVE之后索引会变为无效,需要REBUILD。
3.使用shrink space时,索引会自动维护。如果在业务繁忙时做压缩,可以先shrink space compact,来压缩数据而不移动HWM,等到不繁忙的时候再shrink space来移动HWM。
4.索引也是可以压缩的,压缩表时指定Shrink space cascade会同时压缩索引,也可以alter index xxx shrink space来压缩索引。
5.shrink space需要在表空间是自动段空间管理的,所以system表空间上的表无法shrink space。
发表评论
-
mysqlsla来分析MYSQL的性能及索引
2011-01-17 19:56 1263— Slow log: mysqlsla -lt slow ... -
六款常用mysql slow log分析工具的比较
2011-01-17 19:06 1236转自:http://www.iteye.com/topi ... -
MySQL的大小写敏感性
2011-01-12 14:05 1017转自: http://www.zeali.net/ent ... -
如何查看mysql的版本
2010-05-22 11:52 21977如果我们想要查看mysql数据库的版本有以下四种方法: ... -
MySQL 数据库的备份和恢复
2010-03-19 13:44 891转自 忧里修斯 http://tec ... -
mysql使用show命令以及replace函数批量修改数据
2010-03-19 13:37 2363一.mysql的show命令 a. show tables或 ... -
MySQL中的ROWNUM的实现
2010-01-26 13:43 2157本文转自 http://blog.csdn.net/ACMA ... -
一个MySQL死锁问题的分析及解决
2010-01-20 12:50 1222转自http://java-guru.iteye.com/bl ... -
MySQL 死锁分析
2010-01-20 12:42 32931.MySQL锁和死锁的理解: ... -
sql 按指定顺序排序
2010-01-19 10:53 21261、在ORACLE中使用Decode Decode实 ... -
MYSQL 事务管理
2009-10-26 19:48 1024mysql_query("BEGIN"); ... -
delete 符合条件的记录中的前几条或者重复记录
2009-09-03 20:04 2019今天写代码,遇到了这个问题,只能删除符合条件的记录中的某几条. ... -
sql update delete 中 使用 inner join
2009-08-24 11:38 7144SQL中使用update inner join和delet ... -
What is the difference between VARCHAR, VARCHAR2 ?
2009-06-01 09:43 899Both CHAR and VARCHAR2 types ar ... -
Oracle index
2009-05-15 10:50 0索引是一种可以提高查 ... -
Views and Materialized Views 整理
2009-04-10 14:29 1106Views and Mat ... -
IN and EXISTS, NOT IN AND NOT EXISTS
2009-04-10 14:28 1389Functionally, they are the same ... -
ORACLE 之 TRUNCATE TABLE
2009-03-30 16:49 1949TRUNCATE Caution: Y ... -
深入sql之merge into
2009-01-08 16:38 4696转自 逆水流沙 http://hi.baidu.com/wen ... -
Oracle日期函数操作(收集整理版)
2008-12-04 16:50 2749经常在平时的开发中要用到oracle的日期函数,每次都要上 ...
相关推荐
NULL 博文链接:https://zotao.iteye.com/blog/691609
一个字段: ALTER TABLE table_name MODIFY(字段类型 数据类型)); 添加一个字段: ALTER TABLE table_name ADD(字段类型 数据类型);
--删除表列alter table test modify address char(10) --修改表列类型||alter table test change
SQL Server中alter table一些用法.doc
4.5 alter table … move partition … compress 8 5、分区表的压缩测试 8 5.1 分区表创建 8 5.2 分区表压缩属性查看 8 5.3 分区表压缩属性修改 9 5.4 分区索引压缩属性修改 10 5.5 分区表空间数据压缩 12 5.6 分区...
alter table in Sql server
mysql alter table语句可以修改表的基本结构,例如添加字段、删除字段、添加主键、添加索引、修改字段数据类型、对表重命名等等操作,本文章通过两个简单的实例向大家介绍mysql alter table的使用方法。 实例一:...
mysql实例之使用alter table命令修改表结构 mysql alter table语句可以修改表的基本结构,例如添加字段、删除字段、添加主键、添加索引、修改字段数据类型、对表重命名等等操作,本文章通过两个简单的实例向大家介绍...
ALTER TABLE `ecs_brand` ADD COLUMN `brand_banner` VARCHAR(80) NOT NULL COMMENT '商品品牌banner'; ALTER TABLE `ecs_goods` ADD COLUMN `sales_count` int(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `click_...
解决Oracle 碎片整理 问题。比较Oracle中的alter table t move和alter table t shrink space。Oracle 10g Shrink Table 详解。
MYSQL ALTER TABLE命令用于修改表结构,例如添加/修改/删除字段、索引、主键等等,本文章通过实例向大家介绍MYSQL ALTER TABLE语句的使用方法, MySQL ALTER语法如下: ALTER [IGNORE] TABLE tbl_name alter_spec...
alter table tbl_name add col_name type 例如, 给pet的表增加一列 weight, mysql>alter table pet add weight int; 2. 删除列 alter table tbl_name drop col_name 例如, 删除pet表中的weight这一列 mysql>...
alter table emlog_user add sex varchar(2) NOT NULL DEFAULT ‘1’; alter table emlog_user add qq varchar(20) NOT NULL; alter table emlog_user add zhuye varchar(50) NOT NULL; alter table emlog_user add ...
MySQL Alter Table 修改表信息.docx
ALTER TABLE and CREATE TABLE in Access/vb6
MYSQL ALTER TABLE命令用于修改表结构,例如添加/修改/删除字段、索引、主键等等,本文章通过实例向大家介绍MYSQL ALTER TABLE语句的使用方法,需要的朋友可以参考一下。
数据库管理与应用 4.2.3在ALTER TABLE语句中创建索引 .pdf 学习资料 复习资料 教学资源
数据库管理与应用 3.4.2 使用ALTER TABLE语句修改表结构.pdf 学习资料 复习资料 教学资源
alter table spkcrb add brmle decimal(12,2) null,status char(1) null,rdrq smalldatetime null,zhrkrq smalldatetime null,zhxsrq smalldatetime null,zhwlrq smalldatetime null,zhdhrq smalldatetime null,...
MySQL数据库中ALTER TABLE语句的用法研究.pdf