分页SQL优化
SELECT *
FROM (SELECT pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_,
pubformdat0_.process_id process3_332_,
pubformdat0_.entity_id entity4_332_,
pubformdat0_.file_type file5_332_,
pubformdat0_.title title332_, pubformdat0_.word_no word7_332_,
pubformdat0_.priority priority332_,
pubformdat0_.secret_level secret9_332_,
pubformdat0_.drafter drafter332_,
pubformdat0_.drafter_name drafter11_332_,
pubformdat0_.draft_dept draft12_332_,
pubformdat0_.draft_deptname draft13_332_,
pubformdat0_.draft_date draft14_332_,
pubformdat0_.end_date end15_332_,
pubformdat0_.arch_state arch16_332_,
pubformdat0_.arch_fileid arch17_332_,
pubformdat0_.gq_templateid gq18_332_,
pubformdat0_.gw_templateid gw19_332_,
pubformdat0_.edit_flag edit20_332_,
pubformdat0_.delete_flag delete21_332_,
pubformdat0_.operator operator332_,
pubformdat0_.operate_date operate23_332_,
pubformdat0_.file_security_level file24_332_,
pubformdat0_.yj_flag yj25_332_, pubformdat0_.yj_user yj26_332_
FROM wf_pub_form_data pubformdat0_
WHERE 1 = 1
and pubformdat0_.operate_date is not null
AND pubformdat0_.id IN (SELECT DISTINCT pubformcas1_.form_id
FROM wf_pub_form_case pubformcas1_
WHERE pubformcas1_.case_id IN
(SELECT /*+unnest*/ to_char(
caserun0_.id)
FROM case_run caserun0_,
workitem_run workitemru1_
WHERE caserun0_.id
=
workitemru1_.caseid
AND caserun0_.state
= 2
AND workitemru1_.performer
IN (
'300016/00415'
)
AND workitemru1_.valid_flag
= '0'
UNION ALL
SELECT to_char(
caserun2_.id)
FROM case_run caserun2_,
workitem_history workitemhi3_
WHERE caserun2_.id
=
workitemhi3_.caseid
AND caserun2_.state
= 2
AND workitemhi3_.performer
IN (
'300016/00415'
)
AND workitemhi3_.valid_flag
= '0')
)
ORDER BY pubformdat0_.operate_date DESC)
WHERE rownum <= 10
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2s8unmtbkvpvj, child number 0
-------------------------------------
SELECT * FROM (SELECT pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_, pubformdat0_.process_id
process3_332_, pubformdat0_.entity_id entity4_332_, pubformdat0_.file_type file5_332_,
pubformdat0_.title title332_, pubformdat0_.word_no word7_332_, pubformdat0_.priority priority332_,
pubformdat0_.secret_level secret9_332_, pubformdat0_.drafter drafter332_, pubformdat0_.drafter_name
drafter11_332_, pubformdat0_.draft_dept draft12_332_, pubformdat0_.draft_deptname draft13_332_,
pubformdat0_.draft_date draft14_332_, pubformdat0_.end_date end15_332_,
pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid arch17_332_,
pubformdat0_.gq_templateid gq18_332_, pubformdat0_.gw_templateid gw19_332_,
Plan hash value: 63818532
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 5 |00:00:00.33 | 16288 | | |
| 2 | VIEW | | 1 | 656 | 5 |00:00:00.33 | 16288 | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 656 | 5 |00:00:00.33 | 16288 | 3072 | 3072 | 2048 (0)|
|* 4 | HASH JOIN RIGHT SEMI | | 1 | 656 | 5 |00:00:00.33 | 16288 | 842K| 842K|91136 (0)|
| 5 | VIEW | VW_NSO_2 | 1 | 656 | 5 |00:00:00.16 | 3243 | | |
|* 6 | HASH JOIN | | 1 | 656 | 5 |00:00:00.16 | 3243 | 1306K| 1306K| 114K (0)|
| 7 | VIEW | VW_NSO_1 | 1 | 656 | 5 |00:00:00.01 | 21 | | |
| 8 | UNION-ALL | | 1 | | 5 |00:00:00.01 | 21 | | |
| 9 | NESTED LOOPS | | 1 | 115 | 5 |00:00:00.01 | 20 | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN | 1 | 115 | 5 |00:00:00.01 | 8 | | |
|* 11 | INDEX RANGE SCAN | IDX_WORKITEM_R_8 | 1 | 116 | 5 |00:00:00.01 | 3 | | |
|* 12 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 12 | | |
|* 13 | INDEX UNIQUE SCAN | PK_CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 7 | | |
| 14 | NESTED LOOPS | | 1 | 541 | 0 |00:00:00.01 | 1 | | |
| 15 | TABLE ACCESS BY INDEX ROWID| WORKITEM_HISTORY | 1 | 541 | 0 |00:00:00.01 | 1 | | |
|* 16 | INDEX RANGE SCAN | IDX_WORKITEM_H_14 | 1 | 541 | 0 |00:00:00.01 | 1 | | |
|* 17 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 0 | 1 | 0 |00:00:00.01 | 0 | | |
|* 18 | INDEX UNIQUE SCAN | PK_CASE_RUN | 0 | 1 | 0 |00:00:00.01 | 0 | | |
| 19 | INDEX FAST FULL SCAN | IDX_WF_PUB_FC_4 | 1 | 319K| 337K|00:00:00.01 | 3222 | | |
|* 20 | TABLE ACCESS FULL | WF_PUB_FORM_DATA | 1 | 256K| 257K|00:00:00.01 | 13045 | | |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - access("PUBFORMDAT0_"."ID"="$nso_col_1")
6 - access("PUBFORMCAS1_"."CASE_ID"="$nso_col_1")
10 - filter("WORKITEMRU1_"."VALID_FLAG"='0')
11 - access("WORKITEMRU1_"."PERFORMER"='300016/00415')
12 - filter("CASERUN0_"."STATE"=2)
13 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID")
16 - access("WORKITEMHI3_"."PERFORMER"='300016/00415' AND "WORKITEMHI3_"."VALID_FLAG"='0')
filter("WORKITEMHI3_"."VALID_FLAG"='0')
17 - filter("CASERUN2_"."STATE"=2)
18 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID")
20 - filter("PUBFORMDAT0_"."OPERATE_DATE" IS NOT NULL)
55 rows selected.
使用/*+index_desc(pubformdat0_ WF_PUB_FORM_DATA_IDX1)*/:
WF_PUB_FORM_DATA_IDX1 对OPERATE_DATE进行索引
SELECT *
FROM (SELECT /*+index_desc(pubformdat0_ WF_PUB_FORM_DATA_IDX1)*/ pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_,
pubformdat0_.process_id process3_332_,
pubformdat0_.entity_id entity4_332_,
pubformdat0_.file_type file5_332_,
pubformdat0_.title title332_, pubformdat0_.word_no word7_332_,
pubformdat0_.priority priority332_,
pubformdat0_.secret_level secret9_332_,
pubformdat0_.drafter drafter332_,
pubformdat0_.drafter_name drafter11_332_,
pubformdat0_.draft_dept draft12_332_,
pubformdat0_.draft_deptname draft13_332_,
pubformdat0_.draft_date draft14_332_,
pubformdat0_.end_date end15_332_,
pubformdat0_.arch_state arch16_332_,
pubformdat0_.arch_fileid arch17_332_,
pubformdat0_.gq_templateid gq18_332_,
pubformdat0_.gw_templateid gw19_332_,
pubformdat0_.edit_flag edit20_332_,
pubformdat0_.delete_flag delete21_332_,
pubformdat0_.operator operator332_,
pubformdat0_.operate_date operate23_332_,
pubformdat0_.file_security_level file24_332_,
pubformdat0_.yj_flag yj25_332_, pubformdat0_.yj_user yj26_332_
FROM wf_pub_form_data pubformdat0_
WHERE 1 = 1
and pubformdat0_.operate_date is not null
AND pubformdat0_.id IN (SELECT DISTINCT pubformcas1_.form_id
FROM wf_pub_form_case pubformcas1_
WHERE pubformcas1_.case_id IN
(SELECT /*+unnest*/ to_char(
caserun0_.id)
FROM case_run caserun0_,
workitem_run workitemru1_
WHERE caserun0_.id
=
workitemru1_.caseid
AND caserun0_.state
= 2
AND workitemru1_.performer
IN (
'300016/00415'
)
AND workitemru1_.valid_flag
= '0'
UNION ALL
SELECT to_char(
caserun2_.id)
FROM case_run caserun2_,
workitem_history workitemhi3_
WHERE caserun2_.id
=
workitemhi3_.caseid
AND caserun2_.state
= 2
AND workitemhi3_.performer
IN (
'300016/00415'
)
AND workitemhi3_.valid_flag
= '0')
)
ORDER BY pubformdat0_.operate_date DESC)
WHERE rownum <= 10
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 6ryapwnk5hv20, child number 0
-------------------------------------
SELECT * FROM (SELECT /*+index_desc(pubformdat0_ WF_PUB_FORM_DATA_IDX1)*/ pubformdat0_.id id332_, pubformdat0_.domain_id
domain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_,
pubformdat0_.file_type file5_332_, pubformdat0_.title title332_, pubformdat0_.word_no word7_332_,
pubformdat0_.priority priority332_, pubformdat0_.secret_level secret9_332_, pubformdat0_.drafter
drafter332_, pubformdat0_.drafter_name drafter11_332_, pubformdat0_.draft_dept draft12_332_,
pubformdat0_.draft_deptname draft13_332_, pubformdat0_.draft_date draft14_332_,
pubformdat0_.end_date end15_332_, pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid
arch17_332_, pubformdat0_.gq_templateid gq18_332_,
Plan hash value: 3079359560
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 5 |00:00:00.79 | 250K| | | |
| 2 | VIEW | | 1 | 656 | 5 |00:00:00.79 | 250K| | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 656 | 5 |00:00:00.79 | 250K| 3072 | 3072 | 2048 (0)|
|* 4 | HASH JOIN RIGHT SEMI | | 1 | 656 | 5 |00:00:00.79 | 250K| 842K| 842K|91136 (0)|
| 5 | VIEW | VW_NSO_2 | 1 | 656 | 5 |00:00:00.15 | 3243 | | | |
|* 6 | HASH JOIN | | 1 | 656 | 5 |00:00:00.15 | 3243 | 1306K| 1306K| 116K (0)|
| 7 | VIEW | VW_NSO_1 | 1 | 656 | 5 |00:00:00.01 | 21 | | | |
| 8 | UNION-ALL | | 1 | | 5 |00:00:00.01 | 21 | | | |
| 9 | NESTED LOOPS | | 1 | 115 | 5 |00:00:00.01 | 20 | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN | 1 | 115 | 5 |00:00:00.01 | 8 | | | |
|* 11 | INDEX RANGE SCAN | IDX_WORKITEM_R_8 | 1 | 116 | 5 |00:00:00.01 | 3 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 12 | | | |
|* 13 | INDEX UNIQUE SCAN | PK_CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 7 | | | |
| 14 | NESTED LOOPS | | 1 | 541 | 0 |00:00:00.01 | 1 | | | |
| 15 | TABLE ACCESS BY INDEX ROWID| WORKITEM_HISTORY | 1 | 541 | 0 |00:00:00.01 | 1 | | | |
|* 16 | INDEX RANGE SCAN | IDX_WORKITEM_H_14 | 1 | 541 | 0 |00:00:00.01 | 1 | | | |
|* 17 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 18 | INDEX UNIQUE SCAN | PK_CASE_RUN | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 19 | INDEX FAST FULL SCAN | IDX_WF_PUB_FC_4 | 1 | 319K| 337K|00:00:00.01 | 3222 | | | |
| 20 | TABLE ACCESS BY INDEX ROWID | WF_PUB_FORM_DATA | 1 | 256K| 257K|00:00:00.52 | 247K| | | |
|* 21 | INDEX FULL SCAN DESCENDING | WF_PUB_FORM_DATA_IDX1 | 1 | 257K| 257K|00:00:00.01 | 686 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
4 - access("PUBFORMDAT0_"."ID"="$nso_col_1")
6 - access("PUBFORMCAS1_"."CASE_ID"="$nso_col_1")
10 - filter("WORKITEMRU1_"."VALID_FLAG"='0')
11 - access("WORKITEMRU1_"."PERFORMER"='300016/00415')
12 - filter("CASERUN0_"."STATE"=2)
13 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID")
16 - access("WORKITEMHI3_"."PERFORMER"='300016/00415' AND "WORKITEMHI3_"."VALID_FLAG"='0')
filter("WORKITEMHI3_"."VALID_FLAG"='0')
17 - filter("CASERUN2_"."STATE"=2)
18 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID")
21 - filter("PUBFORMDAT0_"."OPERATE_DATE" IS NOT NULL)
56 rows selected.
还是不行扫描了257K条记录
创建组合索引:
create index WF_PUB_FORM_DATA_IDX2 on WF_PUB_FORM_DATA(ID,OPERATE_DATE);
SELECT *
FROM (SELECT /*+index_desc(pubformdat0_ WF_PUB_FORM_DATA_IDX2)*/ pubformdat0_.id id332_, pubformdat0_.domain_id domain2_332_,
pubformdat0_.process_id process3_332_,
pubformdat0_.entity_id entity4_332_,
pubformdat0_.file_type file5_332_,
pubformdat0_.title title332_, pubformdat0_.word_no word7_332_,
pubformdat0_.priority priority332_,
pubformdat0_.secret_level secret9_332_,
pubformdat0_.drafter drafter332_,
pubformdat0_.drafter_name drafter11_332_,
pubformdat0_.draft_dept draft12_332_,
pubformdat0_.draft_deptname draft13_332_,
pubformdat0_.draft_date draft14_332_,
pubformdat0_.end_date end15_332_,
pubformdat0_.arch_state arch16_332_,
pubformdat0_.arch_fileid arch17_332_,
pubformdat0_.gq_templateid gq18_332_,
pubformdat0_.gw_templateid gw19_332_,
pubformdat0_.edit_flag edit20_332_,
pubformdat0_.delete_flag delete21_332_,
pubformdat0_.operator operator332_,
pubformdat0_.operate_date operate23_332_,
pubformdat0_.file_security_level file24_332_,
pubformdat0_.yj_flag yj25_332_, pubformdat0_.yj_user yj26_332_
FROM wf_pub_form_data pubformdat0_
WHERE 1 = 1
and pubformdat0_.operate_date is not null
AND pubformdat0_.id IN (SELECT DISTINCT pubformcas1_.form_id
FROM wf_pub_form_case pubformcas1_
WHERE pubformcas1_.case_id IN
(SELECT /*+unnest*/ to_char(
caserun0_.id)
FROM case_run caserun0_,
workitem_run workitemru1_
WHERE caserun0_.id
=
workitemru1_.caseid
AND caserun0_.state
= 2
AND workitemru1_.performer
IN (
'300016/00415'
)
AND workitemru1_.valid_flag
= '0'
UNION ALL
SELECT to_char(
caserun2_.id)
FROM case_run caserun2_,
workitem_history workitemhi3_
WHERE caserun2_.id
=
workitemhi3_.caseid
AND caserun2_.state
= 2
AND workitemhi3_.performer
IN (
'300016/00415'
)
AND workitemhi3_.valid_flag
= '0')
)
ORDER BY pubformdat0_.operate_date DESC)
WHERE rownum <= 10
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID arx424k0ck6sc, child number 0
-------------------------------------
SELECT * FROM (SELECT /*+index_desc(pubformdat0_ WF_PUB_FORM_DATA_IDX2)*/ pubformdat0_.id id332_, pubformdat0_.domainid
domain2_332_, pubformdat0_.process_id process3_332_, pubformdat0_.entity_id entity4_332_,
pubformdat0_.file_type file5_332_, pubformdat0_.title title332_, pubformdat0_.word_no word7_332_,
pubformdat0_.priority priority332_, pubformdat0_.secret_level secret9_332_, pubformdat0_.dafter
drafter332_, pubformdat0_.drafter_name drafter11_332_, pubformdat0_.draft_dept draft12_332,
pubformdat0_.draft_deptname draft13_332_, pubformdat0_.draft_date draft14_332_, pubformat0_.end_date
end15_332_, pubformdat0_.arch_state arch16_332_, pubformdat0_.arch_fileid arch17_332_,
pubformdat0_.gq_templateid gq18_332_,
Plan hash value: 3309727489
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Mem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 5 |00:00:00.15 | 3260 | | | |
| 2 | VIEW | | 1 | 656 | 5 |00:00:00.15 | 3260 | | | |
|* 3 | SORT ORDER BY STOPKEY | | 1 | 656 | 5 |00:00:00.15 | 3260 | 072 | 3072 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID | WF_PUB_FORM_DATA | 1 | 1 | 5 |00:00:00.15 | 3260 | | | |
| 5 | NESTED LOOPS | | 1 | 656 | 11 |00:00:00.15 | 3255 | | | |
| 6 | VIEW | VW_NSO_2 | 1 | 656 | 5 |00:00:00.15 | 3243 | | | |
| 7 | HASH UNIQUE | | 1 | 656 | 5 |00:00:00.15 | 3243 | 846K| 846K| 121K (0)|
|* 8 | HASH JOIN | | 1 | 656 | 5 |00:00:00.15 | 3243 | 306K| 1306K| 124K (0)|
| 9 | VIEW | VW_NSO_1 | 1 | 656 | 5 |00:00:00.01 | 21 | | | |
| 10 | UNION-ALL | | 1 | | 5 |00:00:00.01 | 21 | | | |
| 11 | NESTED LOOPS | | 1 | 115 | 5 |00:00:00.01 | 20 | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID| WORKITEM_RUN | 1 | 115 | 5 |00:00:00.01 | 8 | | | |
|* 13 | INDEX RANGE SCAN | IDX_WORKITEM_R_8 | 1 | 116 | 5 |00:00:00.01 | 3 | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 12 | | | |
|* 15 | INDEX UNIQUE SCAN | PK_CASE_RUN | 5 | 1 | 5 |00:00:00.01 | 7 | | | |
| 16 | NESTED LOOPS | | 1 | 541 | 0 |00:00:00.01 | 1 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID| WORKITEM_HISTORY | 1 | 541 | 0 |00:00:00.01 | 1 | | | |
|* 18 | INDEX RANGE SCAN | IDX_WORKITEM_H_14 | 1 | 541 | 0 |00:00:00.01 | 1 | | | |
|* 19 | TABLE ACCESS BY INDEX ROWID| CASE_RUN | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|* 20 | INDEX UNIQUE SCAN | PK_CASE_RUN | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
| 21 | INDEX FAST FULL SCAN | IDX_WF_PUB_FC_4 | 1 | 319K| 337K|00:00:00.01 | 3222 | | | |
|* 22 | INDEX RANGE SCAN DESCENDING | WF_PUB_FORM_DATA_IDX2 | 5 | 1 | 5 |00:00:00.01 | 12 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
8 - access("PUBFORMCAS1_"."CASE_ID"="$nso_col_1")
12 - filter("WORKITEMRU1_"."VALID_FLAG"='0')
13 - access("WORKITEMRU1_"."PERFORMER"='300016/00415')
14 - filter("CASERUN0_"."STATE"=2)
15 - access("CASERUN0_"."ID"="WORKITEMRU1_"."CASEID")
18 - access("WORKITEMHI3_"."PERFORMER"='300016/00415' AND "WORKITEMHI3_"."VALID_FLAG"='0')
filter("WORKITEMHI3_"."VALID_FLAG"='0')
19 - filter("CASERUN2_"."STATE"=2)
20 - access("CASERUN2_"."ID"="WORKITEMHI3_"."CASEID")
22 - access("PUBFORMDAT0_"."ID"="$nso_col_1")
filter("PUBFORMDAT0_"."OPERATE_DATE" IS NOT NULL)
57 rows selected.
此时扫描5条数据后就停止了,问题解决
索引扫描 没停住 是因为建立错了
要 包含
所有的 where 条件列 + order by 列
分页语句 只能order by 一个表的列,不能是两个表的列
分页SQL创建索引的顺序:
where 列前面， order by 的列放后面
