这次做 select 操作每张表都是1亿

三个表的索引都disable了

先测试压缩

--sales_data 有位图不适合
alter table sales_data1 compress;

---查看
SELECT table_name, partition_name, compression
  FROM user_tab_partitions;

SELECT table_name, partition_name, compression
  FROM user_tables;

---压缩
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200901 COMPRESS PARALLEL;
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200902 COMPRESS PARALLEL;
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200903 COMPRESS PARALLEL;
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200904 COMPRESS PARALLEL;
ALTER TABLE SALES_DATA1 MOVE PARTITION SALES_200905 COMPRESS PARALLEL;

?

?一些语句

--查看表空间的文件存放等
SELECT FILE_NAME,TABLESPACE_NAME,BYTES,AUTOEXTENSIBLE,
MAXBYTES,INCREMENT_BY
FROM DBA_DATA_FILES;

---表空间使用率
select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b
where   a.tablespace_name=b.tablespace_name
order   by   ((a.bytes-b.bytes)/a.bytes)   desc

---表空间是否自增
select file_id,file_name,tablespace_name,autoextensible,increment_by from dba_data_files order by file_id desc; 

---表的大小
Select Segment_Name, Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name

??

压缩完后的比较

SEGMENT_NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?SUM(BYTES)/1024/1024 ??

--------------------------------------------- ----------------------?

SALES_DATA ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?3676.625 ? ? ? ? ? ? ??

SALES_DATA1 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 1643.625 ? ? ? ? ? ? ??

SALES_DATA2 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 3717 ? ? ?

?

小了好多 ;

?

?搜集信息

execute dbms_stats.gather_table_stats(ownname => 'TOBY', tabname => 'SALES_DATA',degree =>4);
execute dbms_stats.gather_table_stats(ownname => 'TOBY', tabname => 'SALES_DATA1',degree =>4);
execute dbms_stats.gather_table_stats(ownname => 'TOBY', tabname => 'SALES_DATA2',degree =>4);

?

做join 看看压缩不压缩的区别

---为压缩 35,167ms elapsed
select city,sum(sales_amount) from sales_data
inner join city using (city_id)
where sales_date between to_date ('2009-01-1' ,'yyyy-mm-dd') and to_date ('2009-02-28' ,'yyyy-mm-dd')
group by city;


---压缩 21,549ms
select city,sum(sales_amount) from sales_data1
inner join city using (city_id)
where sales_date between to_date ('2009-01-1' ,'yyyy-mm-dd') and to_date ('2009-02-28' ,'yyyy-mm-dd')
group by city;

?

35秒对21秒?

再看执行计划

?Statistics

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

? ? ? ? ? ? ?267 ?recursive calls

? ? ? ? ? 234762 ?consistent gets direct

? ? ? ? ? 234762 ?physical reads direct

? ? ? ? ? ? ? ?0 ?recovery blocks read

? ? ? ? ? ? ? ?0 ?redo buffer allocation retries

?

?Statistics

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

? ? ? ? ? ? ?357 ?recursive calls

? ? ? ? ? 104407 ?consistent gets direct

? ? ? ? ? 104407 ?physical reads direct

? ? ? ?