Oracle分区,索引,测试(2)(二)
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,01 | P->P | HASH |
| 6 | SORT GROUP BY | | 23 | 805 | 14650 (3)| 00:02:56 | | | Q1,01 | PCWP | |
|* 7 | HASH JOIN | | 1820K| 60M| 14609 (3)| 00:02:56 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 23 | 437 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 23 | 437 | 1 (0)| 00:00:01 | | | Q1,00 | P