前言
分页语句的需求是要按指定的顺序返回数据，分页才有价值，不然不同页面可能会出现重复数据或返回无序数据引起可读性降低
分页语句优化的目标是尽早返回所需数据，所以分页语句中要以nest loop 及索引访问为主
因要返回有序数据，所以要尽量通过索引直接返回已经排序过的数据来提升性能
案例模拟
同事发来一个慢查询，虽然客户咨询的第一目标不是性能问题，但通过PLAN及语句本身仍然发现了两个问题。首先脱敏案例环境模拟如下
TRUNCATE TABLE organizations;TRUNCATE TABLE stores;TRUNCATE TABLE phone_grades;TRUNCATE TABLE phone_inventory;
-- 1. 创建表结构（保持脱敏命名）CREATE TABLE organizations (
org_id VARCHAR(20) PRIMARY KEY,
org_name VARCHAR(100));
CREATE TABLE stores (
store_id VARCHAR(20),
region_code VARCHAR(10),
store_name VARCHAR(100),
PRIMARY KEY (region_code, store_id));
CREATE TABLE phone_grades (
region_code VARCHAR(10),
grade_code VARCHAR(20),
resource_type VARCHAR(20),
grade_name VARCHAR(100),
PRIMARY KEY (region_code, grade_code, resource_type));
CREATE TABLE phone_inventory (
phone_id VARCHAR(50) PRIMARY KEY,
resource_type VARCHAR(20),
grade_code VARCHAR(20),
region_code VARCHAR(10),
org_id VARCHAR(20),
store_id VARCHAR(20),
group_id VARCHAR(20),
hlr_id VARCHAR(20),
imsi_value VARCHAR(50),
status VARCHAR(20),
business_status VARCHAR(20),
hlr_status VARCHAR(20),
base_price NUMERIC(18,2),
status_date TIMESTAMP,
created_at TIMESTAMP,
usage_count INTEGER,
recycle_date TIMESTAMP,
brand_id VARCHAR(20),
notes TEXT,
phone_class VARCHAR(20),
class_name VARCHAR(50));
-- 2. 生成关联数据（关键修正点）-- 组织数据（确保有ORG.01前缀）INSERT INTO organizationsSELECT 'ORG.01' || LPAD(i::text, 3, '0'), '组织_' || iFROM generate_series(1, 50) i;
-- 门店数据（确保有R951区域）INSERT INTO storesSELECT
'STORE_' || LPAD(s::text, 4, '0'),
CASE WHEN s <= 30 THEN 'R951' ELSE 'R'||(800+s%10) END,
'门店_' || sFROM generate_series(1, 100) s;
-- 电话等级数据（确保有R951和R999区域）INSERT INTO phone_gradesSELECT
CASE WHEN g%5 = 0 THEN 'R999' ELSE 'R951' END,
'GRADE_' || LPAD((g%20 + 1)::text, 2, '0'),
'RS_TGSM',
'等级_' || gFROM generate_series(1, 100) g;
-- 主表数据（强制关联）INSERT INTO phone_inventorySELECT
'PHONE_' || LPAD(i::text, 7, '0'),
'RS_TGSM',
'GRADE_' || LPAD((i%20 + 1)::text, 2, '0'),
'R951',
'ORG.01' || LPAD((i%50 + 1)::text, 3, '0'),
'STORE_' || LPAD((i%30 + 1)::text, 4, '0'),
'GROUP_' || (i%5 + 1),
'HLR_' || (i%3 + 1),
'IMSI_' || i,
CASE WHEN i%10 > 0 THEN 'ACTIVE' ELSE 'INACTIVE' END,
CASE WHEN i%20 = 0 THEN 'WAITING_UNBIND' ELSE 'NORMAL' END,
'STATUS_' || (i%3 + 1),
(i%1000)*10,
NOW() - (i%365)::int * '1 day'::interval,
NOW() - (i%730)::int * '1 day'::interval,
i%100,
CASE WHEN i%5 = 0 THEN NOW() - (i%180)::int * '1 day'::interval ELSE NULL END,
'BRAND_' || (i%5 + 1),
'备注内容_' || i,
'CLASS_' || (i%5 + 1),
'类别_' || (i%5 + 1)FROM generate_series(1, 10000) i; -- 生成1万条数据
-- 3. 创建索引CREATE INDEX idx_inv_region ON phone_inventory(region_code);CREATE INDEX idx_inv_org ON phone_inventory(org_id);CREATE INDEX idx_inv_grade ON phone_inventory(grade_code);CREATE INDEX phone_inventory_n1 on phone_inventory(region_code,resource_type,phone_id);
explain analyzeselect * from (
select row_.phone_id,
row_.resource_type,
row_.grade_code,
nvl((select a.grade_name from phone_grades a
where a.region_code in
(select min(region_code) from phone_grades tr where tr.region_code in (row_.region_code,'R999') and row_.grade_code = tr.grade_code and row_.resource_type = tr.resource_type)
and row_.grade_code = a.grade_code
and row_.resource_type = a.resource_type) , 'N/A') grade_name,
nvl((select tc.class_name from phone_inventory tc
where tc.region_code = row_.region_code and tc.phone_id = row_.phone_id), '') phone_class,
to_char(row_.region_code) region_code,
row_.org_id,
( select org_name from organizations where org_id = row_.org_id ) org_name,
row_.store_id,
( select store_name from stores where region_code = row_.region_code and store_id = row_.store_id ) store_name ,
row_.group_id, row_.hlr_id, row_.imsi_value, row_.status, row_.business_status, row_.hlr_status,
row_.base_price, row_.status_date, row_.created_at, row_.usage_count, row_.recycle_date, row_.brand_id , row_.notes , row_.phone_class , rownum rownum_
from (
select t.phone_id, t.resource_type, t.grade_code, t.region_code, t.org_id, t.store_id, t.group_id, t.hlr_id, t.imsi_value, t.status,
t.business_status, t.hlr_status, to_char(nvl(t.base_price,0)/100) base_price, to_char(t.status_date, 'yyyy-mm-dd hh24:mi:ss') status_date,
to_char(t.created_at, 'yyyy-mm-dd hh24:mi:ss') created_at, to_char(t.usage_count) usage_count, to_char(t.recycle_date, 'yyyy-mm-dd hh24:mi:ss') recycle_date,
t.brand_id, t.notes , t.phone_class from phone_inventory t
where t.region_code = 'R951' and t.org_id like 'ORG.01'||'%' AND business_status <> 'WAITING_UNBIND' and t.resource_type = 'RS_TGSM'
and t.phone_id like '%' and rownum <= 100 order by t.phone_id
) row_ where rownum <= 100) where rownum_ > 0;
模拟语句PLAN如下
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on "390573141__unnamed_subquery__" (cost=33.43..60.35 rows=1 width=1606) (actual time=7.332..7.332 rows=0 loops=1)
Filter: ("390573141__unnamed_subquery__".rownum_ > 0)
-> Limit (cost=33.43..60.34 rows=1 width=1104) (actual time=7.332..7.332 rows=0 loops=1)
-> Subquery Scan on row_ (cost=33.43..60.34 rows=1 width=1104) (actual time=7.330..7.330 rows=0 loops=1)
-> Sort (cost=33.43..33.44 rows=1 width=992) (actual time=7.329..7.329 rows=0 loops=1)
Sort Key: t.phone_id
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on phone_inventory t (cost=25.50..33.42 rows=1 width=992) (actual time=7.320..7.320 rows=0 loops=1)
Recheck Cond: ((region_code)::text = 'R951'::text)
Filter: (((org_id)::text ~~ 'ORG.01%'::text) AND ((business_status)::text <> 'WAITING_UNBIND'::text) AND ((phone_id)::text ~~ '%'::text) AND (ROWNUM <= 100) AND ((resource_type)::text = 'RS_TGSM'::text))
-> BitmapAnd (cost=25.50..25.50 rows=2 width=0) (actual time=7.316..7.316 rows=0 loops=1)
-> Bitmap Index Scan on idx_inv_region (cost=0.00..12.00 rows=500 width=0) (actual time=7.164..7.164 rows=100000 loops=1)
Index Cond: ((region_code)::text = 'R951'::text)
-> Bitmap Index Scan on idx_inv_org (cost=0.00..13.25 rows=500 width=0) (actual time=0.011..0.011 rows=0 loops=1)
Index Cond: (((org_id)::text >= 'ORG.01'::text) AND ((org_id)::text < 'ORG.02'::text))
SubPlan 1
-> Hash Join (cost=1.05..2.08 rows=1 width=218) (Actual time: never executed)
Hash Cond: ((a.region_code)::text = (min((tr.region_code)::text)))
-> Seq Scan on phone_grades a (cost=0.00..1.01 rows=1 width=256) (Actual time: never executed)
Filter: (((row_.grade_code)::text = (grade_code)::text) AND ((row_.resource_type)::text = (resource_type)::text))
-> Hash (cost=1.04..1.04 rows=1 width=32) (Actual time: never executed)
Buckets: 0 Batches: 0 Memory Usage: 0kB
-> Aggregate (cost=1.02..1.03 rows=1 width=70) (Actual time: never executed)
-> Seq Scan on phone_grades tr (cost=0.00..1.02 rows=1 width=38) (Actual time: never executed)
Filter: (((region_code)::text = ANY ((ARRAY[row_.region_code, 'R999'::varchar])::text[])) AND ((row_.grade_code)::text = (grade_code)::text) AND ((row_.resource_type)::text = (resource_type)::text))
SubPlan 2
-> Index Scan using phone_inventory_pkey on phone_inventory tc (cost=0.00..8.27 rows=1 width=118) (Actual time: never executed)
Index Cond: ((phone_id)::text = (row_.phone_id)::text)
Filter: ((region_code)::text = (row_.region_code)::text)
SubPlan 3
-> Index Scan using organizations_pkey on organizations (cost=0.00..8.27 rows=1 width=218) (Actual time: never executed)
Index Cond: ((org_id)::text = (row_.org_id)::text)
SubPlan 4
-> Index Scan using idx_stores_composite on stores (cost=0.00..8.27 rows=1 width=218) (Actual time: never executed)
Index Cond: (((region_code)::text = (row_.region_code)::text) AND ((store_id)::text = (row_.store_id)::text))
Total runtime: 7.613 ms(36 rows)
写法谬误
在看性能问题之前，我们先纠正写法中的一个问题：语句中用了 order by t.phone_id 来排序，也用了分页模式，但未完全用对， 在order by 前多了个 rownum <= 100。我们用简单的表来模拟下看会发生什么情况。
有序数据如下：
orcl=> select * from emp order by ename;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+--------+-----------+------+---------------------+------+------+--------
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | | 30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | | 10
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | | 20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | | 30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | | 20
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000 | | 10
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | | 10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | | 20
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | | 20
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30(14 rows)
我们取前两行，预期应行到 7876 与 7499 两行
orcl=> select * from (select rownum as rn,t.* from (select * from emp where rownum <= 2 order by ename)t) where rn >=0 and rn <= 2;
rn | empno | ename | job | mgr | hiredate | sal | comm | deptno ----+-------+-------+----------+------+---------------------+------+------+--------
1 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30
2 | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | | 20(2 rows)
以上结果不对，问题就在于 order by 前不该有 rownum <= 2，正确的方式应该嵌套后再加这个 rownum <= 2
orcl=> select * from (select rownum as rn,t.* from (select * from emp order by ename)t where rownum <= 2) where rn >=0 and rn <= 2;
rn | empno | ename | job | mgr | hiredate | sal | comm | deptno
----+-------+-------+----------+------+---------------------+------+------+--------
1 | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | | 20
2 | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30(2 rows)
使用limit 也可以
orcl=> select * from emp order by ename limit 2;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+----------+------+---------------------+------+------+--------
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 | | 20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30
原语句中是先取100行再排序，修正后会先排序再取100行，性能正常会因要排序这个动作降低。
explain analyzeselect * from (
select row_.phone_id,
row_.resource_type,
row_.grade_code,
nvl((select a.grade_name from phone_grades a
where a.region_code in
(select min(region_code) from phone_grades tr where tr.region_code in (row_.region_code,'R999') and row_.grade_code = tr.grade_code and row_.resource_type = tr.resource_type)
and row_.grade_code = a.grade_code
and row_.resource_type = a.resource_type) , 'N/A') grade_name,
nvl((select tc.class_name from phone_inventory tc
where tc.region_code = row_.region_code and tc.phone_id = row_.phone_id), '') phone_class,
to_char(row_.region_code) region_code,
row_.org_id,
( select org_name from organizations where org_id = row_.org_id ) org_name,
row_.store_id,
( select store_name from stores where region_code = row_.region_code and store_id = row_.store_id ) store_name ,
row_.group_id, row_.hlr_id, row_.imsi_value, row_.status, row_.business_status, row_.hlr_status,
row_.base_price, row_.status_date, row_.created_at, row_.usage_count, row_.recycle_date, row_.brand_id , row_.notes , row_.phone_class , rownum rownum_
from (
select t.phone_id, t.resource_type, t.grade_code, t.region_code, t.org_id, t.store_id, t.group_id, t.hlr_id, t.imsi_value, t.status,
t.business_status, t.hlr_status, to_char(nvl(t.base_price,0)/100) base_price, to_char(t.status_date, 'yyyy-mm-dd hh24:mi:ss') status_date,
to_char(t.created_at, 'yyyy-mm-dd hh24:mi:ss') created_at, to_char(t.usage_count) usage_count, to_char(t.recycle_date, 'yyyy-mm-dd hh24:mi:ss') recycle_date,
t.brand_id, t.notes , t.phone_class from phone_inventory t
where t.region_code = 'R951' and t.org_id like 'ORG.01'||'%' AND business_status <> 'WAITING_UNBIND' and t.resource_type = 'RS_TGSM'
and t.phone_id like '%' order by t.phone_id
) row_ where rownum <= 100) where rownum_ > 0;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on "1342460730__unnamed_subquery__" (cost=8.32..54.00 rows=1 width=809) (actual time=52.377..53.623 rows=100 loops=1)
Filter: ("1342460730__unnamed_subquery__".rownum_ > 0)
-> Limit (cost=8.32..53.99 rows=1 width=274) (actual time=52.372..53.592 rows=100 loops=1)
-> Subquery Scan on row_ (cost=8.32..53.99 rows=1 width=274) (actual time=52.371..53.579 rows=100 loops=1)
-> Sort (cost=8.32..8.32 rows=1 width=148) (actual time=52.287..52.295 rows=100 loops=1)
Sort Key: t.phone_id
Sort Method: quicksort Memory: 3056kB
-> Index Scan using idx_inv_org on phone_inventory t (cost=0.00..8.31 rows=1 width=148) (actual time=0.099..44.632 rows=9500 loops=1)
Index Cond: (((org_id)::text >= 'ORG.01'::text) AND ((org_id)::text < 'ORG.02'::text))
Filter: (((org_id)::text ~~ 'ORG.01%'::text) AND ((business_status)::text <> 'WAITING_UNBIND'::text) AND ((phone_id)::text ~~ '%'::text) AND ((region_code)::text = 'R951'::text) AND ((resource_type)::text = 'RS_TGSM'::text))
Rows Removed by Filter: 500
SubPlan 2
-> Nested Loop (cost=12.54..20.84 rows=1 width=218) (actual time=0.466..0.466 rows=0 loops=100)
-> Result (cost=12.54..12.55 rows=1 width=0) (actual time=0.383..0.385 rows=100 loops=100)
InitPlan 1 (returns $3)
-> Limit (cost=0.00..12.54 rows=1 width=38) (actual time=0.343..0.343 rows=0 loops=100)
-> Index Only Scan using phone_grades_pkey on phone_grades tr (cost=0.00..12.54 rows=1 width=38) (actual time=0.221..0.221 rows=0 loops=100)
Index Cond: ((region_code IS NOT NULL) AND (region_code = ANY ((ARRAY[row_.region_code, 'R999'::varchar])::text[])) AND (grade_code = (row_.grade_code)::text) AND (res
ource_type = (row_.resource_type)::text))
Heap Fetches: 18
-> Index Scan using phone_grades_pkey on phone_grades a (cost=0.00..8.27 rows=1 width=256) (actual time=0.008..0.008 rows=0 loops=100)
Index Cond: (((region_code)::text = ($3)) AND ((row_.grade_code)::text = (grade_code)::text) AND ((row_.resource_type)::text = (resource_type)::text))
SubPlan 3
-> Index Scan using phone_inventory_pkey on phone_inventory tc (cost=0.00..8.27 rows=1 width=8) (actual time=0.233..0.241 rows=100 loops=100)
Index Cond: ((phone_id)::text = (row_.phone_id)::text)
Filter: ((region_code)::text = (row_.region_code)::text)
SubPlan 4
-> Index Scan using organizations_pkey on organizations (cost=0.00..8.27 rows=1 width=15) (actual time=0.147..0.153 rows=100 loops=100)
Index Cond: ((org_id)::text = (row_.org_id)::text)
SubPlan 5
-> Index Scan using idx_stores_composite on stores (cost=0.00..8.27 rows=1 width=9) (actual time=0.166..0.173 rows=100 loops=100)
Index Cond: (((region_code)::text = (row_.region_code)::text) AND ((store_id)::text = (row_.store_id)::text))
Total runtime: 53.957 ms(32 rows)
优化
我们前面讲过，要尽量返回有序数据来提升性能，既然修正后因排序降低了性能，那我们增加一排引，通过索引直接返回有序数据。
CREATE INDEX phone_inventory_n1 on phone_inventory(region_code,resource_type,phone_id);
加索引后优化器无法自动使用该索引
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on "1248933315__unnamed_subquery__" (cost=8.32..54.00 rows=1 width=809) (actual time=53.121..54.341 rows=100 loops=1)
Filter: ("1248933315__unnamed_subquery__".rownum_ > 0)
-> Limit (cost=8.32..53.99 rows=1 width=274) (actual time=53.115..54.307 rows=100 loops=1)
-> Subquery Scan on row_ (cost=8.32..53.99 rows=1 width=274) (actual time=53.114..54.298 rows=100 loops=1)
-> Sort (cost=8.32..8.32 rows=1 width=148) (actual time=53.032..53.036 rows=100 loops=1)
Sort Key: t.phone_id
Sort Method: quicksort Memory: 3056kB
-> Index Scan using idx_inv_org on phone_inventory t (cost=0.00..8.31 rows=1 width=148) (actual time=0.046..45.251 rows=9500 loops=1)
Index Cond: (((org_id)::text >= 'ORG.01'::text) AND ((org_id)::text < 'ORG.02'::text))
Filter: (((org_id)::text ~~ 'ORG.01%'::text) AND ((business_status)::text <> 'WAITING_UNBIND'::text) AND ((phone_id)::text ~~ '%'::text) AND ((region_code)::text = 'R951'::text) AND ((resource_type)::text = 'RS_TGSM'::text))
Rows Removed by Filter: 500
SubPlan 2
-> Nested Loop (cost=12.54..20.84 rows=1 width=218) (actual time=0.452..0.452 rows=0 loops=100)
-> Result (cost=12.54..12.55 rows=1 width=0) (actual time=0.367..0.370 rows=100 loops=100)
InitPlan 1 (returns $3)
-> Limit (cost=0.00..12.54 rows=1 width=38) (actual time=0.317..0.317 rows=0 loops=100)
-> Index Only Scan using phone_grades_pkey on phone_grades tr (cost=0.00..12.54 rows=1 width=38) (actual time=0.195..0.195 rows=0 loops=100)
Index Cond: ((region_code IS NOT NULL) AND (region_code = ANY ((ARRAY[row_.region_code, 'R999'::varchar])::text[])) AND (grade_code = (row_.grade_code)::text) AND (res
ource_type = (row_.resource_type)::text))
Heap Fetches: 0
-> Index Scan using phone_grades_pkey on phone_grades a (cost=0.00..8.27 rows=1 width=256) (actual time=0.008..0.008 rows=0 loops=100)
Index Cond: (((region_code)::text = ($3)) AND ((row_.grade_code)::text = (grade_code)::text) AND ((row_.resource_type)::text = (resource_type)::text))
SubPlan 3
-> Index Scan using phone_inventory_pkey on phone_inventory tc (cost=0.00..8.27 rows=1 width=8) (actual time=0.236..0.245 rows=100 loops=100)
Index Cond: ((phone_id)::text = (row_.phone_id)::text)
Filter: ((region_code)::text = (row_.region_code)::text)
SubPlan 4
-> Index Scan using organizations_pkey on organizations (cost=0.00..8.27 rows=1 width=15) (actual time=0.126..0.137 rows=100 loops=100)
Index Cond: ((org_id)::text = (row_.org_id)::text)
SubPlan 5
-> Index Scan using idx_stores_composite on stores (cost=0.00..8.27 rows=1 width=9) (actual time=0.152..0.161 rows=100 loops=100)
Index Cond: (((region_code)::text = (row_.region_code)::text) AND ((store_id)::text = (row_.store_id)::text))
Total runtime: 54.679 ms(32 rows)
我们通过hint来应用索引
/*+ indexscan(t phone_inventory_n1) */
explain analyzeselect * from (
select row_.phone_id,
row_.resource_type,
row_.grade_code,
nvl((select a.grade_name from phone_grades a
where a.region_code in
(select min(region_code) from phone_grades tr where tr.region_code in (row_.region_code,'R999') and row_.grade_code = tr.grade_code and row_.resource_type = tr.resource_type)
and row_.grade_code = a.grade_code
and row_.resource_type = a.resource_type) , 'N/A') grade_name,
nvl((select tc.class_name from phone_inventory tc
where tc.region_code = row_.region_code and tc.phone_id = row_.phone_id), '') phone_class,
to_char(row_.region_code) region_code,
row_.org_id,
( select org_name from organizations where org_id = row_.org_id ) org_name,
row_.store_id,
( select store_name from stores where region_code = row_.region_code and store_id = row_.store_id ) store_name ,
row_.group_id, row_.hlr_id, row_.imsi_value, row_.status, row_.business_status, row_.hlr_status,
row_.base_price, row_.status_date, row_.created_at, row_.usage_count, row_.recycle_date, row_.brand_id , row_.notes , row_.phone_class , rownum rownum_
from (
select /*+ indexscan(t phone_inventory_n1) */ t.phone_id, t.resource_type, t.grade_code, t.region_code, t.org_id, t.store_id, t.group_id, t.hlr_id, t.imsi_value, t.status,
t.business_status, t.hlr_status, to_char(nvl(t.base_price,0)/100) base_price, to_char(t.status_date, 'yyyy-mm-dd hh24:mi:ss') status_date,
to_char(t.created_at, 'yyyy-mm-dd hh24:mi:ss') created_at, to_char(t.usage_count) usage_count, to_char(t.recycle_date, 'yyyy-mm-dd hh24:mi:ss') recycle_date,
t.brand_id, t.notes , t.phone_class from phone_inventory t
where t.region_code = 'R951' and t.org_id like 'ORG.01'||'%' AND business_status <> 'WAITING_UNBIND' and t.resource_type = 'RS_TGSM'
and t.phone_id like '%' order by t.phone_id
) row_ where rownum <= 100) where rownum_ > 0;
得到plan如下：
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on "1716438901__unnamed_subquery__" (cost=0.00..1347.02 rows=1 width=809) (actual time=0.175..1.988 rows=100 loops=1)
Filter: ("1716438901__unnamed_subquery__".rownum_ > 0)
-> Limit (cost=0.00..1347.00 rows=1 width=274) (actual time=0.172..1.961 rows=100 loops=1)
-> Subquery Scan on row_ (cost=0.00..1347.00 rows=1 width=274) (actual time=0.171..1.948 rows=100 loops=1)
-> Index Scan using phone_inventory_n1 on phone_inventory t (cost=0.00..1301.34 rows=1 width=148) (actual time=0.122..0.599 rows=100 loops=1)
Index Cond: (((region_code)::text = 'R951'::text) AND ((resource_type)::text = 'RS_TGSM'::text))
Filter: (((org_id)::text ~~ 'ORG.01%'::text) AND ((business_status)::text <> 'WAITING_UNBIND'::text) AND ((phone_id)::text ~~ '%'::text))
Rows Removed by Filter: 5
SubPlan 2
-> Nested Loop (cost=12.54..20.84 rows=1 width=218) (actual time=0.510..0.510 rows=0 loops=100)
-> Result (cost=12.54..12.55 rows=1 width=0) (actual time=0.413..0.419 rows=100 loops=100)
InitPlan 1 (returns $3)
-> Limit (cost=0.00..12.54 rows=1 width=38) (actual time=0.372..0.372 rows=0 loops=100)
-> Index Only Scan using phone_grades_pkey on phone_grades tr (cost=0.00..12.54 rows=1 width=38) (actual time=0.238..0.238 rows=0 loops=100)
Index Cond: ((region_code IS NOT NULL) AND (region_code = ANY ((ARRAY[row_.region_code, 'R999'::varchar])::text[])) AND (grade_code = (row_.grade_code)::text) AND (res
ource_type = (row_.resource_type)::text))
Heap Fetches: 0
-> Index Scan using phone_grades_pkey on phone_grades a (cost=0.00..8.27 rows=1 width=256) (actual time=0.009..0.009 rows=0 loops=100)
Index Cond: (((region_code)::text = ($3)) AND ((row_.grade_code)::text = (grade_code)::text) AND ((row_.resource_type)::text = (resource_type)::text))
SubPlan 3
-> Index Scan using phone_inventory_pkey on phone_inventory tc (cost=0.00..8.27 rows=1 width=8) (actual time=0.244..0.258 rows=100 loops=100)
Index Cond: ((phone_id)::text = (row_.phone_id)::text)
Filter: ((region_code)::text = (row_.region_code)::text)
SubPlan 4
-> Index Scan using organizations_pkey on organizations (cost=0.00..8.27 rows=1 width=15) (actual time=0.132..0.142 rows=100 loops=100)
Index Cond: ((org_id)::text = (row_.org_id)::text)
SubPlan 5
-> Index Scan using idx_stores_composite on stores (cost=0.00..8.27 rows=1 width=9) (actual time=0.163..0.171 rows=100 loops=100)
Index Cond: (((region_code)::text = (row_.region_code)::text) AND ((store_id)::text = (row_.store_id)::text))
Total runtime: 2.181 ms(29 rows)
结论
优化问题可以找人咨询，但语句本身返回的数据是可以自己测试的，语句写完后要细心测试，注意返回结果是否与预期一致。
