一次分页SQL优化
SELECT id,status,
agent_account,
operatorAccount,
aisleName,
interface_id,
customer_number,
serialnumber,
customer_serialnumber,
TYPE,
money,
create_date,
remark,
processed,
calls,
upNum,
area,
reversal_type,
brokerage_type,
payment_type,
refund_type,
activities
FROM (SELECT temp.id,
temp.agent_account,
temp.operatorAccount,
temp.aisleName,
temp.interface_id,
temp.customer_number,
temp.serialnumber,
temp.customer_serialnumber,
temp.type,
temp.money,
temp.create_date,
temp.status,
temp.remark,
temp.processed,
temp.calls,
temp.upNum,
temp.area,
temp.reversal_type,
temp.brokerage_type,
temp.payment_type,
temp.refund_type,
temp.activities,
rownum row_id
FROM (SELECT
/*+ use_nl(a,ta) use_nl(a,b) use_nl(a,c) leading(a,b,c) */
a.id,
a.agent_account,
b.login_name AS operatorAccount,
c.aislename AS aisleName,
a.interface_id,
a.customer_number,
a.serialnumber,
a.customer_serialnumber,
a.type,
a.money,
a.create_date,
a.status,
a.remark,
a.processed,
a.calls,
a.upNum,
a.area,
a.reversal_type,
a.brokerage_type,
a.payment_type,
a.refund_type,
a.activities
FROM (SELECT id FROM tb_agent WHERE sales_id = 103585) ta
INNER JOIN tb_recharge a
ON ta.id = a.agent_id
LEFT JOIN tb_operator b
ON a.operator_id = b.id
LEFT JOIN tb_aisle c
ON a.aisle_id = c.id
WHERE a.status = 1
ORDER BY a.id DESC) temp
WHERE rownum0
SQL>select*from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3946970243
---------------------------------------------------------------------------------------------------------------------------------------
| Id| Operation| Name | Rows| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT| | 4 |4052 |5405(1)| 00:01:05 | | |
|*1 |VIEW| | 4 |4052 |5405(1)| 00:01:05 | | |
|*2 | COUNT STOPKEY | | | | | | | |
| 3 | VIEW | | 4 |4000 |5405(1)| 00:01:05 | | |
| 4 | NESTED LOOPS| | 4 | 900 |5405(1)| 00:01:05 | | |
| 5 | NESTED LOOPS| |1194 | 900 |5405(1)| 00:01:05 | | |
| 6 | NESTED LOOPS OUTER| |1194 | 250K|4211(1)| 00:00:51 | | |
| 7 | NESTED LOOPS OUTER| |1194 | 218K|3017(1)| 00:00:37 | | |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| TB_RECHARGE |1194 | 198K|1822(0)| 00:00:22 | ROWID | ROWID |
|*9 |INDEX RANGE SCAN DESCENDING| IDX_TB_RECHARGE_STATUS_ID |1194 | | 13(0)| 00:00:01 | | |
|10 | TABLE ACCESS BY INDEX ROWID| TB_OPERATOR | 1 | 17 | 1(0)| 00:00:01 | | |
|* 11 |INDEX UNIQUE SCAN| TB_OPERATOR_PK | 1 | | 0(0)| 00:00:01 | | |
|12 | TABLE ACCESS BY INDEX ROWID| TB_AISLE | 1 | 28 | 1(0)| 00:00:01 | | |
|* 13 | INDEX UNIQUE SCAN| TB_AISLEV_PK | 1 | | 0(0)| 00:00:01 | | |
|* 14 | INDEX UNIQUE SCAN | TB_AGENT_PK | 1 | | 0(0)| 00:00:01 | | |
|* 15 | TABLE ACCESS BY INDEX ROWID| TB_AGENT | 1 | 10 | 1(0)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------
页:
[1]