Oracle分区,索引,测试(2)
Oracle分区,索引,测试(2)
这次做 select 操作每张表都是1亿
三个表的索引都disable了
先测试压缩
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
- 更多Oracle疑问解答:
- 运行exp备份oracle数据库提示oracle-12154错误
- 有没有,生产Oracle Rman 备份脚本的工具啊!
- 初学orcle,希望有大大帮忙解说一下详细步骤,从登录oracle到创建表的过程
- oracle语句问题:一张user表,三个字段,id,name,time,插入记录比如:张三2007,李四2008,张三2011
- 如何写一个ORACLE触发器同步两个表中的数据?
- oracle 如何查看一个服务器上有多少个数据库.
- oracle 创建包的时候错误 求解
- oracle 重复列的问题
- oracle 中如何查处2星期前的数据
- 请教oracle数据库安装中的问题
- 请问谁能提供给我标准的oracle ERP的数据库表结构并详细说明各表主要的作用?
- 安装oracle遇到的问题 invalid entry CRC (expected 0x3e12e795 but got 0x9db0e9fd)
- 我的是ORACLE 10G,在RMAN中如何按指定的时间恢复数据文件啊?
- oracle为什么没有自动增长列
- oracle快捷键都有哪些啊?