create user test8 identified by test8;SYS@odb>grant connect,resource to te..."/>
您好,登錄后才能下訂單哦!
1、建立初始化數(shù)據(jù)演示數(shù)據(jù)表中的高水位的變化
(1)、建立用戶和表
SYS@odb>create user test8 identified by test8;
SYS@odb>grant connect,resource to test8;
SYS@odb>conn test8/test8
TEST8@odb>create table gaoshuiwei as select * from all_objects;
TEST8@odb>select TABLE_NAME,NUM_ROWS,BLOCKS from user_tables where table_name='GAOSHUIWEI';
TABLE_NAME NUM_ROWS BLOCKS
---------------------------------------- ----------
GAOSHUIWEI 68309 500
TEST8@odb>delete from gaoshuiwei;
TEST8@odb>commit;
TEST8@odb>select count(*) from gaoshuiwei;
COUNT(*)
----------
0
TEST8@odb>select TABLE_NAME,NUM_ROWS,BLOCKS from user_tables wheretable_name='GAOSHUIWEI';
TABLE_NAME NUM_ROWS BLOCKS
---------------------------------------- ----------
GAOSHUIWEI 68309 500
可以看到在刪除表中的數(shù)據(jù)后,在user_tables里面該表還是有68309行和500個(gè)快大約8M左右;
(2)、在次插入數(shù)據(jù)
TEST8@ odb>insert intogaoshuiwei select * from all_objects;
TEST8@ odb>commit;
TEST8@ odb>select count(*) fromgaoshuiwei;
COUNT(*)
----------
68309
TEST8@ odb>selectTABLE_NAME,NUM_ROWS,BLOCKS from user_tables where table_name='GAOSHUIWEI';
TABLE_NAME NUM_ROWS BLOCKS
---------------------------------------- ----------
GAOSHUIWEI 68309 500
TEST8@ odb>delete fromgaoshuiwei;
68309 rows deleted.
TEST8@ odb>commit;
Commit complete.
TEST8@ odb>selectTABLE_NAME,NUM_ROWS,BLOCKS from user_tables where table_name='GAOSHUIWEI';
TABLE_NAME NUM_ROWS BLOCKS
---------------------------------------- ----------
GAOSHUIWEI 68309 500
看到信息沒什么變化,需要搜集統(tǒng)計(jì)信息;
TEST8@odb>
execdbms_stats.gather_table_stats('TEST8','GAOSHUIWEI',cascade=>true);
PL/SQL procedure successfullycompleted.
TEST8@ odb>selectTABLE_NAME,NUM_ROWS,BLOCKS from user_tables where table_name='GAOSHUIWEI';
TABLE_NAME NUM_ROWS BLOCKS
---------------------------------------- ----------
GAOSHUIWEI 0 500
TEST8@ odb>selectsegment_name,segment_type,bytes from user_segments wheresegment_name='GAOSHUIWEI'; //查看段的信息
SEGMENT_NAME SEGMENT_TYPE BYTES
-------------------------------------- ----------
GAOSHUIWEI TABLE 8388608
塊500 大約8M左右, 生成執(zhí)行計(jì)劃:
TEST8@odb>explain plan for select count(*) from gaoshuiwei;
Explained.
TEST8@odb>select * from table(dbms_xplan.display);
該表是0行數(shù)據(jù) ,全表掃描,cost 189 ;原因是因?yàn)榈脑摫韥砘氐牟迦雱h除,高水位線一直沒有降下來,還會(huì)掃描500個(gè)塊,全部掃描一遍返回結(jié)果,如果發(fā)現(xiàn)表的數(shù)據(jù)不大,但是查詢很慢,可能和高水位有關(guān):
可采用
方法(1)、使用truncate 方法
方法(2)、使用移動(dòng)表空間的方法
方法(3)、使用收縮表空間的方法
TEST8@ odb>alter table gaoshuiwei enablerow movement; //啟用行移動(dòng)
TEST8@ odb>alter table gaoshuiwei shrinkspace compact; //壓縮表的空間,這時(shí)還沒發(fā)生變化
TEST8@ odb>alter table gaoshuiwei shrinkspace; //降低表的高水位線這時(shí)變化就比較明顯了
對(duì)索引也可以設(shè)置同樣的操作
alter indexPUB_LOG_PK shrink space compact;
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。