Oracle分区,索引,测试(2)

Sql代码  
--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;  

 一些语句
Sql代码  
--查看表空间的文件存放等  
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      

小了好多 ;

 搜集信息
Sql代码  
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 看看压缩不压缩的区别
Sql代码  
---为压缩 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
               0  recovery blocks read
               0  redo buffer allocation retries

差不多相差2倍多的读取.

 ----------测试用不用并行的时间相差
Sql代码  
alter table sales_data NOPARALLEL;  
alter table city NOPARALLEL;  
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;  


42,734ms elapsed
Plan hash value: 3773866511

---------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            |    23 |   805 | 58734  (13)| 00:11:45 |       |       |
|   1 |  SORT GROUP BY             |            |    23 |   805 | 58734  (13)| 00:11:45 |       |       |
|*  2 |   HASH JOIN                |            |    50M|  1679M| 53390   (4)| 00:10:41 |       |       |
|   3 |    TABLE ACCESS FULL       | CITY       |    23 |   437 |     3   (0)| 00:00:01 |       |       |
|   4 |    PARTITION RANGE ITERATOR|            |    50M|   767M| 52923   (3)| 00:10:36 |     1 |     2 |
|*  5 |     TABLE ACCESS FULL      | SALES_DATA |    50M|   767M| 52923   (3)| 00:10:36 |     1 |     2 |
---------------------------------------------------------------------------------------------------------

对比 一个34秒对 42秒
对比 压缩的 就是21 对 42秒

 SALES_DATA 建索引
Sql代码  
--分区索引放在对应表空间  
create index index_sales_data_partition on sales_data (sales_date) local   
(  
partition sales_200901 tablespace ts_sales_200901,  
partition sales_200902 tablespace ts_sales_200902,  
partition sales_200903 tablespace ts_sales_200903,  
partition sales_200904 tablespace ts_sales_200904,  
partition sales_200905 tablespace ts_sales_200905,  
partition sales_200906 tablespace ts_sales_200906,  
partition sales_200907 tablespace ts_sales_200907,  
partition sales_200908 tablespace ts_sales_200908,  
partition sales_200909 tablespace ts_sales_200909,  
partition sales_200910 tablespace ts_sales_200910,  
partition sales_200911 tablespace ts_sales_200911,  
partition sales_200912 tablespace ts_sales_200912,  
partition sales_201001 tablespace ts_sales_201001,  
partition sales_201002 tablespace ts_sales_201002  
);  
---位图  
create bitmap index index_sales_data_sales_type on sales_data (sales_type) local ;  
--位图连接  
create bitmap index index_sales_data_city on sales_data (city.city_id)   
from sales_data,city   
where sales_data.city_id=city.city_id  
local ;  

跑SQL
Sql代码  
select  city,sum(sales_amount) from sales_data  
inner join city using (city_id)  
where sales_date = to_date ('2009-01-1' ,'yyyy-mm-dd')  or sales_date = to_date ('2009-03-5' ,'yyyy-mm-dd')   
group by city;  


22,493ms elapsed
Plan hash value: 303492610

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |    23 |   805 | 14650   (3)| 00:02:56 |       |       |        |      |            |
|   1 |  PX COORDINATOR             |            |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)       | :TQ10002   |    23 |   805 | 14650   (3)| 00:02:56 |       |       |  Q1,02 | P->S | QC (RAND)  |
|   3 |    SORT GROUP BY            |            |    23 |   805 | 14650   (3)| 00:02:56 |       |       |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE              |            |    23 |   805 | 14650   (3)| 00:02:56 |       |       |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH           | :TQ10001   |    23 |   805 | 14650   (3)| 00:02:56 |       |       |  Q1,0