Oracle分页数据测试

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

---