关于分区表的性能问题
create table test1(test_id number(9),id number(9),log_time date) partition by range(log_time) (partition t1_01 values less than (to_date('2004-02-01','yyyy-mm-dd')), partition t1_02 values less than (to_date('2004-03-01','yyyy-mm-dd')), partition t1_03 values less than (to_date('2004-04-01','yyyy-mm-dd')), partition t1_04 values less than (to_date('2004-05-01','yyyy-mm-dd')), partition t1_05 values less than (to_date('2004-06-01','yyyy-mm-dd')), partition t1_06 values less than (to_date('2004-07-01','yyyy-mm-dd')), partition t1_07 values less than (to_date('2004-08-01','yyyy-mm-dd')), partition t1_08 values less than (to_date('2004-09-01','yyyy-mm-dd')), partition t1_09 values less than (to_date('2004-10-01','yyyy-mm-dd')), partition t1_10 values less than (to_date('2004-11-01','yyyy-mm-dd')), partition t1_11 values less than (to_date('2004-12-01','yyyy-mm-dd')), partition t1_12 values less than (to_date('2005-01-01','yyyy-mm-dd')));
create table test2(test_id number(9),id number(9),log_time date);
create sequence seq_t1; create sequence seq_t2;
create index idx_test1_logtime on test1(log_time) local; create index idx_test1_testid on test1(test_id) local; create index idx_test1_id on test1(id) local;
create index idx_test2_logtime on test2(log_time); create index idx_test2_testid on test2(test_id); create index idx_test2_id on test2(id);
关于分区表与非分区表的测试 begin for i in 1 .. 1000 loop for j in 1 .. 12 loop for k in 1 .. 28 loop insert into test1 values(seq_t1.nextval,i,to_date('2004-'||to_char(j)||'-'||to_char(k),'yyyy-mm-dd')); end loop; end loop; end loop; end; / begin for i in 1 .. 1000 loop for j in 1 .. 12 loop for k in 1 .. 28 loop insert into test2 values(seq_t2.nextval,i,to_date('2004-'||to_char(j)||'-'||to_char(k),'yyyy-mm-dd')); end loop; end loop; end loop; end; /
SQL> select count(*) from test1;
COUNT(*) ---------- 336000
Elapsed: 00:00:00.02
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1104 consistent gets 0 physical reads 0 redo size 394 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL> select count(*) from test2;
COUNT(*) ---------- 336000
Elapsed: 00:00:00.02
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1067 consistent gets 0 physical reads 0 redo size 394 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
统计显示,分区表在全表扫描时将比非分区表需要更多的逻辑读
SQL> select count(*) from test1 where test_id = 1000;
COUNT(*) ---------- 1
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 24 consistent gets 0 physical reads 0 redo size 393 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
SQL> select count(*) from test2 where test_id = 337000;
COUNT(*) ---------- 1
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 393 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
在没有与分区字段组合查询时,分区表需要更多的逻辑读
SQL> select count(*) 2 from test1 3 where id = 1000 4 and log_time between to_date('2004-11-01','yyyy-mm-dd') and to_date('2004-11-30 23:59:59','yyyy-mm-dd hh24:mi:ss');
COUNT(*) ---------- 28
Elapsed: 00:00:00.01
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 393 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk)
SQL> select count(*) 2 from test2 3 where id = 1000 4 and log_time between to_date('2004-11-01','yyyy-mm-dd') and to_date('2004-11-30 23:59:59','yyyy-mm-dd hh24:mi:ss');
COUNT(*) ---------- 28
Elapsed: 00:00:00.00
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 393 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
与分区字段组合查询时,分区表比非分区表有更好的性能优势