SELECT大表的處理

尛樣兒發表於2010-04-21
系統中有一張表是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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章