SELECT大表的处理

系统中有一张表是2秒insert一次，一次大概有100多条，一天有几百万的数据，目前这张表大概有30GB的数据量了。

需求可能查询一天的数据，也可能查询一个月的数据。速度那就不用说了，这样的数据量，即使使用索引也慢得不行了。而且频率还挺高，系统出现大量cache buffer chain latch。

考虑将此表修改为按时间的分区表，一天一个分区，将索引创建为分区索引。

实验如下：

1.创建原始表：

create table EMS.UNIT_POWER_SECOND_T

(

  APPLIEDTIME DATE not null,

  METERCODE   INTEGER not null,

  OBJID       INTEGER not null,

  DATA        NUMBER(18,4)

);

2.创建索引：

create index ems.pk_unit_power_second_t on ems.unit_power_second_t(appliedtime,metercode,objid);

3.插入模拟数据

declare

v_d date :=to_date('2010-1-1','yyyy-mm-dd');

metercode number;

objid number;

begin

for i in 1..1000000 loop

v_d:=v_d+2/24/60/60;

metercode :=abs(dbms_random.random mod 9);

objid :=abs(dbms_random.random mod 99);

insert into EMS.UNIT_POWER_SECOND_T values(

v_d,

metercode,

objid,

dbms_random.random);

end loop;

end;

/

4.执行SQL

SQL> SELECT *

      FROM ems.UNIT_POWER_SECOND_T

     WHERE appliedTime > =to_date('2010-1-1','yyyy-mm-dd')

       AND appliedTime < to_date('2010-1-2','yyyy-mm-dd')

     ORDER BY appliedTime;

43199 rows selected.

Execution Plan

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

Plan hash value: 991247716

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

| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |                        | 31888 |  1494K|   255   (1)| 00:00:04 |

|   1 |  TABLE ACCESS BY INDEX ROWID| UNIT_POWER_SECOND_T    | 31888 |  1494K|   255   (1)| 00:00:04 |

|*  2 |   INDEX RANGE SCAN          | PK_UNIT_POWER_SECOND_T | 31888 |       |   120   (1)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

   2 - access("APPLIEDTIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "APPLIEDTIME"

Note

-----

   - dynamic sampling used for this statement

Statistics

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

         10  recursive calls

          1  db block gets

       6510  consistent gets

        398  physical reads

      26228  redo size

    1500722  bytes sent via SQL*Net to client

      32161  bytes received via SQL*Net from client

       2881  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      43199  rows processed

5.创建分区表：

create table EMS.UNIT_POWER_SECOND_T111

(

  APPLIEDTIME DATE not null,

  METERCODE   INTEGER not null,

  OBJID       INTEGER not null,

  DATA        NUMBER(18,4)

)

partition by range (appliedtime)(

partition unit_power_second_t100102 values less than (to_date('2010-1-2','yyyy-mm-dd')),

partition unit_power_second_t100103 values less than (to_date('2010-1-3','yyyy-mm-dd')),

partition unit_power_second_t100104 values less than (to_date('2010-1-4','yyyy-mm-dd')),

partition unit_power_second_t100105 values less than (to_date('2010-1-5','yyyy-mm-dd')),

partition unit_power_second_t100106 values less than (to_date('2010-1-6','yyyy-mm-dd')),

partition unit_power_second_t100107 values less than (to_date('2010-1-7','yyyy-mm-dd')),

partition unit_power_second_t100108 values less than (to_date('2010-1-8','yyyy-mm-dd')),

partition unit_power_second_t100109 values less than (to_date('2010-1-9','yyyy-mm-dd')),

partition unit_power_second_t100110 values less than (to_date('2010-1-10','yyyy-mm-dd')),

partition unit_power_second_t100111 values less than (to_date('2010-1-11','yyyy-mm-dd')),

partition unit_power_second_t100112 values less than (to_date('2010-1-12','yyyy-mm-dd')),

partition unit_power_second_t100113 values less than (to_date('2010-1-13','yyyy-mm-dd')),

partition unit_power_second_t100114 values less than (to_date('2010-1-14','yyyy-mm-dd')),

partition unit_power_second_t100115 values less than (to_date('2010-1-15','yyyy-mm-dd')),

partition unit_power_second_t100116 values less than (to_date('2010-1-16','yyyy-mm-dd')),

partition unit_power_second_t100117 values less than (to_date('2010-1-17','yyyy-mm-dd')),

partition unit_power_second_t100118 values less than (to_date('2010-1-18','yyyy-mm-dd')),

partition unit_power_second_t100119 values less than (to_date('2010-1-19','yyyy-mm-dd')),

partition unit_power_second_t100120 values less than (to_date('2010-1-20','yyyy-mm-dd')),

partition unit_power_second_t100121 values less than (to_date('2010-1-21','yyyy-mm-dd')),

partition unit_power_second_t100122 values less than (to_date('2010-1-22','yyyy-mm-dd')),

partition unit_power_second_t100123 values less than (to_date('2010-1-23','yyyy-mm-dd')),

partition unit_power_second_t100124 values less than (to_date('2010-1-24','yyyy-mm-dd')),

partition unit_power_second_t100125 values less than (to_date('2010-1-25','yyyy-mm-dd')));

6.插入数据：

insert /*+append*/ into ems.unit_power_second_t111 select * from ems.unit_power_second_t;

7.执行查询：

SQL>SELECT *

      FROM ems.UNIT_POWER_SECOND_T111

     WHERE appliedTime > =to_date('2010-1-1','yyyy-mm-dd')

       AND appliedTime < to_date('2010-1-2','yyyy-mm-dd')

     ORDER BY appliedTime  2    3    4    5  ;

43199 rows selected.

Execution Plan

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

Plan hash value: 3250869502

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

| Id  | Operation              | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT       |                        | 43199 |   759K|       |   290   (2)| 00:00:04 |       |       |

|   1 |  PARTITION RANGE SINGLE|                        | 43199 |   759K|       |   290   (2)| 00:00:04 |     1 |     1 |

|   2 |   SORT ORDER BY        |                        | 43199 |   759K|  2728K|   290   (2)| 00:00:04 |       |       |

|*  3 |    TABLE ACCESS FULL   | UNIT_POWER_SECOND_T111 | 43199 |   759K|       |    40   (3)| 00:00:01 |     1 |     1 |

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

Predicate Information (identified by operation id):

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

   3 - filter("APPLIEDTIME">=TO_DATE(' 2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics

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

          0  recursive calls

          0  db block gets

        162  consistent gets

        159  physical reads

          0  redo size

    1500722  bytes sent via SQL*Net to client

      32161  bytes received via SQL*Net from client

       2881  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      43199  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23135684/viewspace-660273/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23135684/viewspace-660273/