oracle分区表性能比较案例
--1.创建两张表: part_tab(分区表),no_part_tab(普通表)
CREATE TABLE part_tab
( c1 int default NULL, c2 varchar2(30) default NULL, c3 date not null)
PARTITION BY RANGE(year(c3))
(
PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) ,
PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) ,
PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) ,
PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) ,
PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) ,
PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN (MAXVALUE)
);
CREATE TABLE no_part_tab
( c1 int default NULL, c2 varchar2(30) default NULL, c3 date not null);
--2.用存储过程插入800万条数据
CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 8000000
do
insert into part_tab
values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));
set v = v + 1;
end while;
end;
insert into no_part_tab select * from part_tab;
--3.测试sql性能
--查询分区表:
select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
--1 row in set (2.62 sec)
--查询普通表:
select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
--1 row in set (7.33 sec)
-- 分区表的执行时间比普通表少70%。
--4.通过explain语句来分析执行情况
explain select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
--+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
--| id |select_type | table | type |possible_keys | key | key_len | ref | rows | Extra |
--+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
--| 1 | SIMPLE | part_tab | ALL | NULL | NULL | NULL | NULL | 7980796 | Using where |
--+----+-------------+----------+------+---------------+------+---------+------+---------+-------------+
--1 rowin set
explain select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';
--+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
--
--| id |select_type | table | type |possible_keys | key | key_len | ref | rows | Extra |
--
--+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
--
--| 1 | SIMPLE | no_part_tab | ALL | NULL | NULL | NULL | NULL | 8000206 | Using where |
--
--+----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+
--
--1 rowin set
分区表执行扫描了7980796行,而普通表则扫描了8000206行。
- 1.
- 2.
- 3.
- 4.
- 5.
- 6.
- 7.
- 8.
- 9.
- 10.
- 11.
- 12.
- 13.
- 14.
- 15.
- 16.
- 17.
- 18.
- 19.
- 20.
- 21.
- 22.
- 23.
- 24.
- 25.
- 26.
- 27.
- 28.
- 29.
- 30.
- 31.
- 32.
- 33.
- 34.
- 35.
- 36.
- 37.
- 38.
- 39.
- 40.
- 41.
- 42.
- 43.
- 44.
- 45.
- 46.
- 47.
- 48.
- 49.
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.
- 57.
- 58.
- 59.
- 60.
- 61.
- 62.
- 63.
- 64.
- 65.
- 66.
- 67.
- 68.
- 69.
- 70.
- 71.
- 72.
- 73.
- 74.
- 75.
- 76.
- 77.
- 78.
- 79.
- 80.