有一个t_ord_order 的订单表未分区前分页查询速度很快,但是分页之后查询速度变慢,分区 字段为“DISPATCH_VALID_TIME”
查询的语句如下
select PageDB1.* from(select PageDB2.*,rownum as PageStart from (
select o.*, c2.COMPANY_NAME as SETTLE_COPARTNER_NAME,
c3.COMPANY_NAME as CUMTOMER_COPARTNER_NAME, co1.DIC_CODE_NAME as BUSINESS_TYPE_NAME,
co2.DIC_CODE_NAME as CAR_MODEL_NAME, co3.DIC_CODE_NAME as CAR_BRAND_NAME, d.DISPATCH_STATUS,
bi.BILL_TIME,bc.CONTRACT_SN
from T_ORD_ORDER o
left join T_BUS_COPARTNER c2 on o.SETTLE_COPARTNER_ID = c2.ID
left join T_BUS_COPARTNER c3 on o.CUMTOMER_COPARTNER_ID = c3.ID
left join T_SET_DIC_CODE co1 on o.BUSINESS_TYPE_ID = co1.ID
left join T_SET_DIC_CODE co2 on o.CAR_MODEL_ID = co2.ID
left join T_SET_DIC_CODE co3 on o.CAR_BRAND_ID = co3.ID
left join T_ORD_DISPATCH d on o.DISPATCH_ID = d.DISPATCH_ID
left join T_FIN_ORDER_BALANCE b on o.BALANCE_ID = b.BALANCE_ID
left join T_FIN_BILL bi on b.BILL_ID = bi.BILL_ID
left join T_BUS_CONTRACT bc on o.CONTRACT_ID = bc.ID
WHERE o.COMPANY_ID = 4
and o.DISPATCH_VALID_TIME >= 1546272000000
and o.DISPATCH_VALID_TIME <= 1549209600000
and o.SYS_STATUS=1
order by o.ORDER_ID desc
) PageDB2 where rownum <= NVL(0,0)+ 10 ) PageDB1 where PageDB1.PageStart >= NVL(0,0)+1;
未分区前该段查询代码执行时间是0.015秒
分区了以后执行时间是5.174秒
未分区之前的查询计划如下
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 122 (100)| |
|* 1 | VIEW | | 10 | 62170 | 122 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 11 | 68244 | 122 (0)| 00:00:01 |
| 4 | NESTED LOOPS OUTER | | 11 | 9097 | 122 (0)| 00:00:01 |
| 5 | NESTED LOOPS OUTER | | 11 | 8877 | 111 (0)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 11 | 8657 | 100 (0)| 00:00:01 |
| 7 | NESTED LOOPS OUTER | | 11 | 8195 | 78 (0)| 00:00:01 |
| 8 | NESTED LOOPS OUTER | | 11 | 7975 | 67 (0)| 00:00:01 |
| 9 | NESTED LOOPS OUTER | | 11 | 7678 | 56 (0)| 00:00:01 |
| 10 | NESTED LOOPS OUTER | | 11 | 7381 | 45 (0)| 00:00:01 |
| 11 | NESTED LOOPS OUTER | | 11 | 7051 | 42 (0)| 00:00:01 |
| 12 | NESTED LOOPS OUTER | | 11 | 6545 | 33 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| T_ORD_ORDER_TEMP | 124K| 62M| 25 (0)| 00:00:01 |
| 14 | INDEX FULL SCAN DESCENDING| PK_T_ORD_ORDER | 143 | | 3 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID| T_FIN_ORDER_BALANCE | 1 | 70 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_T_FIN_ORDER_BALANCE | 1 | | 0 (0)| |
| 17 | TABLE ACCESS BY INDEX ROWID | T_FIN_BILL | 1 | 46 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | PK_T_FIN_BILL | 1 | | 0 (0)| |
| 19 | TABLE ACCESS BY INDEX ROWID | T_BUS_CONTRACT | 1 | 30 | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_T_BUS_CONTRACT | 1 | | 0 (0)| |
| 21 | TABLE ACCESS BY INDEX ROWID | T_BUS_COPARTNER | 1 | 27 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PK_T_BUS_COPARTNER | 1 | | 0 (0)| |
| 23 | TABLE ACCESS BY INDEX ROWID | T_BUS_COPARTNER | 1 | 27 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_T_BUS_COPARTNER | 1 | | 0 (0)| |
| 25 | TABLE ACCESS BY INDEX ROWID | T_SET_DIC_CODE | 1 | 20 | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | PK_T_SET_DIC_CODE | 1 | | 0 (0)| |
| 27 | TABLE ACCESS BY INDEX ROWID | T_ORD_DISPATCH | 1 | 42 | 2 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | T_ORD_DISPATCH_PK | 1 | | 1 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | T_SET_DIC_CODE | 1 | 20 | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | PK_T_SET_DIC_CODE | 1 | | 0 (0)| |
| 31 | TABLE ACCESS BY INDEX ROWID | T_SET_DIC_CODE | 1 | 20 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | PK_T_SET_DIC_CODE | 1 | | 0 (0)| |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PAGEDB1"."PAGESTART">=1)
2 - filter(ROWNUM<=10)
13 - filter(("O"."DISPATCH_VALID_TIME">=1546272000000 AND "O"."COMPANY_ID"=4 AND
"O"."DISPATCH_VALID_TIME"<=1549209600000 AND "O"."SYS_STATUS"=1))
16 - access("O"."BALANCE_ID"="B"."BALANCE_ID")
18 - access("B"."BILL_ID"="BI"."BILL_ID")
20 - access("O"."CONTRACT_ID"="BC"."ID")
22 - access("O"."CUMTOMER_COPARTNER_ID"="C3"."ID")
24 - access("O"."SETTLE_COPARTNER_ID"="C2"."ID")
26 - access("O"."CAR_MODEL_ID"="CO2"."ID")
28 - access("O"."DISPATCH_ID"="D"."DISPATCH_ID")
30 - access("O"."CAR_BRAND_ID"="CO3"."ID")
32 - access("O"."BUSINESS_TYPE_ID"="CO1"."ID")
分区以后的执行计划如下
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 51199 (100)| | | |
|* 1 | VIEW | | 10 | 62170 | | 51199 (1)| 00:00:02 | | |
|* 2 | COUNT STOPKEY | | | | | | | | |
| 3 | VIEW | | 121K| 719M| | 51199 (1)| 00:00:02 | | |
|* 4 | SORT ORDER BY STOPKEY | | 121K| 87M| 94M| 51199 (1)| 00:00:02 | | |
|* 5 | HASH JOIN RIGHT OUTER | | 121K| 87M| | 31812 (1)| 00:00:02 | | |
| 6 | TABLE ACCESS FULL | T_BUS_COPARTNER | 594 | 16038 | | 5 (0)| 00:00:01 | | |
|* 7 | HASH JOIN RIGHT OUTER | | 121K| 84M| | 31806 (1)| 00:00:02 | | |
| 8 | TABLE ACCESS FULL | T_BUS_COPARTNER | 594 | 16038 | | 5 (0)| 00:00:01 | | |
|* 9 | HASH JOIN RIGHT OUTER | | 121K| 81M| | 31800 (1)| 00:00:02 | | |
| 10 | TABLE ACCESS FULL | T_SET_DIC_CODE | 1528 | 30560 | | 7 (0)| 00:00:01 | | |
|* 11 | HASH JOIN RIGHT OUTER | | 121K| 79M| | 31793 (1)| 00:00:02 | | |
| 12 | TABLE ACCESS FULL | T_SET_DIC_CODE | 1528 | 30560 | | 7 (0)| 00:00:01 | | |
|* 13 | HASH JOIN RIGHT OUTER | | 121K| 76M| | 31785 (1)| 00:00:02 | | |
| 14 | TABLE ACCESS FULL | T_SET_DIC_CODE | 1528 | 30560 | | 7 (0)| 00:00:01 | | |
|* 15 | HASH JOIN OUTER | | 121K| 74M| 71M| 31777 (1)| 00:00:02 | | |
|* 16 | HASH JOIN RIGHT OUTER | | 121K| 69M| | 6521 (1)| 00:00:01 | | |
| 17 | TABLE ACCESS FULL | T_FIN_BILL | 112 | 5152 | | 3 (0)| 00:00:01 | | |
|* 18 | HASH JOIN RIGHT OUTER | | 121K| 64M| | 6517 (1)| 00:00:01 | | |
| 19 | TABLE ACCESS FULL | T_FIN_ORDER_BALANCE | 64 | 4480 | | 3 (0)| 00:00:01 | | |
|* 20 | HASH JOIN RIGHT OUTER | | 121K| 56M| | 6513 (1)| 00:00:01 | | |
| 21 | TABLE ACCESS FULL | T_BUS_CONTRACT | 286 | 8580 | | 5 (0)| 00:00:01 | | |
| 22 | PARTITION RANGE ITERATOR | | 121K| 52M| | 6508 (1)| 00:00:01 | 579 | 580 |
|* 23 | TABLE ACCESS FULL | T_ORD_ORDER | 121K| 52M| | 6508 (1)| 00:00:01 | 579 | 580 |
| 24 | VIEW | index$_join$_014 | 1614K| 64M| | 17588 (1)| 00:00:01 | | |
|* 25 | HASH JOIN | | | | | | | | |
| 26 | BITMAP CONVERSION TO ROWIDS| | 1614K| 64M| | 132 (0)| 00:00:01 | | |
| 27 | BITMAP INDEX FULL SCAN | INDEX_DISPATCH_STATUS_TYPE_S | | | | | | | |
| 28 | INDEX FAST FULL SCAN | T_ORD_DISPATCH_PK | 1614K| 64M| | 13565 (1)| 00:00:01 | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PAGEDB1"."PAGESTART">=1)
2 - filter(ROWNUM<=10)
4 - filter(ROWNUM<=10)
5 - access("O"."SETTLE_COPARTNER_ID"="C2"."ID")
7 - access("O"."CUMTOMER_COPARTNER_ID"="C3"."ID")
9 - access("O"."BUSINESS_TYPE_ID"="CO1"."ID")
11 - access("O"."CAR_MODEL_ID"="CO2"."ID")
13 - access("O"."CAR_BRAND_ID"="CO3"."ID")
15 - access("O"."DISPATCH_ID"="D"."DISPATCH_ID")
16 - access("B"."BILL_ID"="BI"."BILL_ID")
18 - access("O"."BALANCE_ID"="B"."BALANCE_ID")
20 - access("O"."CONTRACT_ID"="BC"."ID")
23 - filter(("O"."DISPATCH_VALID_TIME">=1546272000000 AND "O"."COMPANY_ID"=4 AND "O"."DISPATCH_VALID_TIME"<=1549209600000 AND
"O"."SYS_STATUS"=1))
25 - access(ROWID=ROWID)
该如何优化啊,求大神给予支持