`
tobyqiu
  • 浏览: 40572 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

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

 
阅读更多

这次做 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

               0  recovery blocks read

               0  redo buffer allocation retries

 

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

 

 ----------测试用不用并行的时间相差

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 建索引

--分区索引放在对应表空间
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

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->P | BROADCAST  |

|  10 |           PX BLOCK ITERATOR |            |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWC |            |

|  11 |            TABLE ACCESS FULL| CITY       |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,00 | PCWP |            |

|  12 |         PX BLOCK ITERATOR   |            |  1820K|    27M| 14604   (3)| 00:02:56 |KEY(I) |KEY(I) |  Q1,01 | PCWC |            |

|* 13 |          TABLE ACCESS FULL  | SALES_DATA |  1820K|    27M| 14604   (3)| 00:02:56 |KEY(I) |KEY(I) |  Q1,01 | PCWP |            |

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

 

Predicate Information (identified by operation id):

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

 

   7 - access("SALES_DATA"."CITY_ID"="CITY"."CITY_ID")

  13 - filter("SALES_DATA"."SALES_DATE"=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR 

              "SALES_DATA"."SALES_DATE"=TO_DATE('2009-03-05 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

 

   Statistics

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

              40  recursive calls

          234602  consistent gets direct

          234602  physical reads direct

               0  recovery blocks read

               0  redo buffer allocation retries

 

 用了22秒 没有走分区索引

强制使用索引

select /*+ index(sales_data INDEX_SALES_DATA_PARTITION) */ 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;

 

 

 

50,296ms elapsed

Plan hash value: 1538767871

 

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

| Id  | Operation                                     | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |

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

|   0 | SELECT STATEMENT                              |                            |    23 |   805 |   240K  (1)| 00:48:12 |       |       |        |      |            |

|   1 |  PX COORDINATOR                               |                            |       |       |            |          |       |       |        |      |            |

|   2 |   PX SEND QC (RANDOM)                         | :TQ10003                   |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,03 | P->S | QC (RAND)  |

|   3 |    SORT GROUP BY                              |                            |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,03 | PCWP |            |

|   4 |     PX RECEIVE                                |                            |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,03 | PCWP |            |

|   5 |      PX SEND HASH                             | :TQ10002                   |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,02 | P->P | HASH       |

|   6 |       SORT GROUP BY                           |                            |    23 |   805 |   240K  (1)| 00:48:12 |       |       |  Q1,02 | PCWP |            |

|*  7 |        HASH JOIN                              |                            |  1820K|    60M|   240K  (1)| 00:48:11 |       |       |  Q1,02 | PCWP |            |

|   8 |         PX RECEIVE                            |                            |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |

|   9 |          PX SEND HASH                         | :TQ10001                   |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |

|  10 |           PX BLOCK ITERATOR                   |                            |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWC |            |

|  11 |            TABLE ACCESS FULL                  | CITY                       |    23 |   437 |     1   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |

|  12 |         BUFFER SORT                           |                            |       |       |            |          |       |       |  Q1,02 | PCWC |            |

|  13 |          PX RECEIVE                           |                            |  1820K|    27M|   240K  (1)| 00:48:11 |       |       |  Q1,02 | PCWP |            |

|  14 |           PX SEND HASH                        | :TQ10000                   |  1820K|    27M|   240K  (1)| 00:48:11 |       |       |        | S->P | HASH       |

|  15 |            INLIST ITERATOR                    |                            |       |       |            |          |       |       |        |      |            |

|  16 |             PARTITION RANGE ITERATOR          |                            |  1820K|    27M|   240K  (1)| 00:48:11 |KEY(I) |KEY(I) |        |      |            |

|  17 |              TABLE ACCESS BY LOCAL INDEX ROWID| SALES_DATA                 |  1820K|    27M|   240K  (1)| 00:48:11 |KEY(I) |KEY(I) |        |      |            |

|* 18 |               INDEX RANGE SCAN                | INDEX_SALES_DATA_PARTITION |  1831K|       |  4884   (1)| 00:00:59 |KEY(I) |KEY(I) |        |      |            |

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

Predicate Information (identified by operation id):

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

 

   7 - access("SALES_DATA"."CITY_ID"="CITY"."CITY_ID")

  18 - access("SALES_DATA"."SALES_DATE"=TO_DATE('2009-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') OR "SALES_DATA"."SALES_DATE"=TO_DATE('2009-03-05 00:00:00', 

              'yyyy-mm-dd hh24:mi:ss'))

 

   Statistics

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

              76  recursive calls

               0  consistent gets direct

            3644  physical reads direct

               0  recovery blocks read

               0  redo buffer allocation retries

 

用了50秒..  看来还是要用oralce 自己的优化器...  用了比没用 多了一半的时间

分享到：
| Oracle 分区,索引,测试 (1)
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle索引与分区索引介绍

    Oracle 索引与分区索引介绍 Oracle 索引与分区索引是 Oracle 数据库中两个重要的概念，它们都是为了提高查询性能和数据存储效率而设计的。在本文中，我们将详细介绍 Oracle 索引与分区索引的概念、特点、分类、创建...

    oracle分区表分区索引.docx

    Oracle 分区表分区索引详解 Oracle 分区表分区索引是 Oracle 数据库中的一种重要机制，它可以提高数据的存储和查询效率。下面将详细介绍 Oracle 分区表分区索引的类型、分类、创建方法和维护方式。 一、分区表和...

    分区索引,本地索引,全局索引的区别

    #### 一、Oracle分区索引概念及分类 在Oracle数据库中，分区索引是针对分区表的一种特殊索引类型，它可以显著提高对于大规模数据集的查询性能。根据索引是否与表的分区策略相匹配，分区索引可以分为两大类：本地...

    Oracle 分区表 分区索引

    ### Oracle 分区表与分区索引详解 #### 一、Oracle分区概述 在Oracle数据库中，分区技术是一种非常有效的管理大型表和索引的方法。通过将一个大的表或索引分成多个较小的部分（分区），可以显著提高查询性能，并...

    Oracle分区表及分区索引

    Oracle 分区表及分区索引 Oracle 分区表是指将一个大表分割成多个小表，每个小表称为一个分区，分区表的优点是可以提高查询性能、改善管理型、增强容错性等。 分区表的分类： 1. 范围分区（Range Partitioning） ...

    ORACLE分区与索引

    针对分区表，Oracle 提供了分区索引，它允许索引与分区策略相结合。例如，可以创建局部索引，每个分区都有自己的索引，或者创建全局索引，覆盖所有分区。局部索引通常在查询性能上有优势，而全局索引则适用于跨分区...

    Oracle 分区表 分区索引 索引分区详解

    虽然存储介质和数据处理技术的发展也很快，但是仍然不能满足用户的需求，为了使用户的大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，以改善大型应用系统的性能。

    深入学习分区表及分区索引(详解oracle分区).docx

    以下是对Oracle分区表和分区索引的深入解析： 1. **何时使用分区**： - 当表的数据量超过2GB时，分区有助于避免32位操作系统下的文件大小限制，同时减少大规模数据的备份时间。 - 对于包含历史数据的表，如按月份...

    Oracle分区表和分区索引在VLDB中的研究.pdf

    "Oracle分区表和分区索引在VLDB中的研究" 本文研究了Oracle分区表和分区索引在VLDB（Very Large Databases）中的应用。分区表和分区索引是数据库管理中的关键技术之一，是VLDB中一个重要的性能提升机制。通过分析一...

    深入oracle分区索引的详解

    局部索引一定是分区索引，分区键等同于表的分区键，分区数等同于表的分区数，一句话，局部索引的分区机制和表的分区机制一样。2.如果局部索引的索引列以分区键开头，则称为前缀局部索引。3.如果局部索引的列不是以...

    oracle分区与索引

    ### Oracle 分区与索引详解 #### 一、Oracle 分区概述 在Oracle数据库中，分区是一种物理数据组织技术，它将一个大的表或索引分成多个较小的部分，每个部分都可以独立管理。通过分区，可以显著提高查询性能，简化...

    oracle分区索引的失效和重建代码示例

    Oracle分区索引是一种优化大型数据库查询性能的有效方法。它将大的单个索引分解为较小、更易管理的分区，每个分区对应表中的一个数据段。这样，查询只需要扫描与查询条件相关的分区，而非整个索引，从而提高查询速度...

    oracle索引分区实践笔记

    2. **全局索引的分区类型**：全局分区索引支持范围分区和散列分区，但散列分区是从Oracle 10g开始支持的。 3. **全局索引的应用场景**：全局索引多应用于OLTP系统中。 ### 分区索引和表分区的关系 索引是否分区应...

    深入学习分区表及分区索引(详解oracle分区.docx

    总结来说，Oracle分区是一种强大的数据库管理工具，特别适合处理大数据量和历史数据存储的情况。通过合理选择分区策略，可以极大地提升数据处理的速度和系统的整体性能。然而，设计和实施分区策略需要深思熟虑，以...

    全面学习分区表及分区索引-Oracle.pdf

    ### 分区表及分区索引概述 在Oracle数据库中，分区技术是一种重要的数据管理手段，尤其适用于处理大型数据集。通过将大型表或索引分解为较小、更易于...希望本文提供的信息能帮助读者更好地理解和运用Oracle分区功能。

    Oracle分区技术介绍

    Oracle分区技术是一种数据库管理系统（DBMS）特性，用于将大型表和索引分割成更小、更易管理和处理的部分，从而提升查询性能和数据管理效率。这种技术特别适用于数据仓库和决策支持系统（DSS），因为这些系统通常...

    Oracle分区表详解

    Oracle 的分区技术是一种用于管理和优化超大型表和索引的有效手段。通过将一个大型的表或者索引分割成多个较小且可管理的部分，分区技术能够显著提升数据库的性能、可用性和可维护性。 ##### 分区的作用： 1. **...

Magicbox
Global site tag (gtag.js) - Google Analytics