Oracle 数据库分页测试
背景:
目标表:TABLE_NA
数据量:43570324 条
执行内容:分页查500万-9条, 均无条件且不带索引列(第一次均无缓存,之后均带缓存查)
-- ORACLE 去除缓存影响SQL
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH GLOBAL CONTEXT;
情况1:
select *
from (
select d.*, rownum as num
from (
select o.*
from TABLE_NA o
) d
-- where rownum < 20 and rownum > 10
) o
where o.num < 5000010 and o.num > 5000000;
-- 执行过程
-- SEQ_SCAN (TABLE ACCESS FULL) table: TABLE_NA; 43554780 576953.0 cpu_cost = 93,809,016,132, io_cost = 569,506
-- 执行耗时:
-- 第一次:execution: 2 m 3 s 242 ms, fetching: 116 ms
-- 第二次:execution: 1 m 54 s 23 ms, fetching: 88 ms
-- 第三次:execution: 2 m 8 s 480 ms, fetching: 100 ms
## 执行完情况4后, 执行这条语句:
-- 第一次:execution: 1 m 22 s 479 ms, fetching: 109 ms
-- 第二次:execution: 1 m 21 s 729 ms, fetching: 71 ms
情况2:
select *
from (
select d.*, rownum as num
from (
select o.*
from TABLE_NA o
) d
-- where rownum < 20 and rownum > 10
) d
where d.num between 5000000 and 5000010;
-- 执行过程
-- SEQ_SCAN (TABLE ACCESS FULL) table: TABLE_NA; 43554780 576953.0 cpu_cost = 93809016132, io_cost = 569506
-- 执行耗时:
-- *第一次:execution: 1 m 42 s 192 ms, fetching: 87 ms
-- *第二次:execution: 49 s 968 ms, fetching: 77 ms
-- *第二次:execution: 30 s 508 ms, fetching: 110 ms
-- 情况4执行完,再查询
-- *第四次:execution: 32 s 555 ms, fetching: 93 ms
情况3:
select *
from (
select d.*, rownum as num
from (
select o.*
from TABLE_NA o
) d
where rownum > 5000000
) o
-- where o.num < 5000010 -- 这样一行都查不到
where rownum < 5000010;
-- 执行过程
-- SEQ_SCAN (TABLE ACCESS FULL) table: TABLE_NA; 5000009 66235.0 cpu_cost = 10769103420, io_cost = 65380
-- 执行耗时:(看结果,是没走缓存的)
-- *第一次:execution: 1 m 30 s 123 ms, fetching: 91 ms
-- 第二次:execution: 1 m 42 s 408 ms, fetching: 98 ms
-- 第二次:execution: 1 m 31 s 367 ms, fetching: 128 ms
情况4:(最优解):
select *
from (
select d.*, rownum as num
from (
select o.*
from TABLE_NA o
) d
where rownum < 5000010
) o
where o.num > 5000000;
-- 执行过程
-- SEQ_SCAN (TABLE ACCESS FULL) table: TABLE_NA; 5000009 66235.0 cpu_cost = 10769103420, io_cost = 65380
-- 执行耗时:
-- *第一次:execution: 6 s 610 ms, fetching: 84 ms
-- *第二次:execution: 3 s 785 ms, fetching: 77 ms
-- *第三次:execution: 3 s 863 ms, fetching: 77 ms