本文分享了一个关于SQL优化的实例，涉及到视图融合。原内嵌视图orderinfo执行耗时较长，通过添加hints尝试强制执行，但实际运行时间仍然较长，讨论了如何进一步优化该SQL语句。
今天找到一条5分钟的SQL
SQL> explain plan for
SELECT orderinfo.order_id, guest_num, guest_name, pnr,
orderinfo.order_flow_id, deliverassign.deliver_type_id,
deliverassign.deliver_employee_id,
t_tp_employee_deliver.deliver_employee_name, deliverassign.assign_time
FROM (SELECT t_to_order_info.order_id,
(SELECT MAX (assign_id)
FROM t_to_deliver_assign_record
WHERE t_to_deliver_assign_record.order_type = 1
AND t_to_deliver_assign_record.order_id =
t_to_order_info.order_id)
maxdeliverid,
guest_num, pnr, t_to_order_info.order_flow_id,
(SELECT guest_name
FROM t_to_order_tickets
WHERE t_to_order_tickets.order_id =
t_to_order_info.order_id
AND ROWNUM <= 1)
|| CASE guest_num
WHEN 1
THEN ''
ELSE ' ...'
END guest_name
FROM t_to_task LEFT JOIN t_to_order_info
ON t_to_task.order_id = t_to_order_info.order_id
WHERE ( t_to_order_info.order_status_id = 6
OR t_to_order_info.order_status_id = 5
)
AND t_to_task.task_type_id = 7
AND t_to_order_info.partner_id IN (SELECT partner_id
FROM t_tp_casher
WHERE employee_id = 2838)) orderinfo
INNER JOIN
t_to_deliver_assign_record deliverassign
ON deliverassign.assign_id = orderinfo.maxdeliverid
LEFT JOIN t_tp_employee_deliver
ON deliverassign.deliver_employee_id =
t_tp_employee_deliver.deliver_employee_id
LEFT JOIN t_tp_casher ttc ON deliverassign.casher_id = ttc.casher_id
LEFT JOIN t_s_employee te ON te.employee_id = ttc.employee_id
WHERE 1 = 1
AND deliverassign.assign_time >=
TO_DATE ('2012-04-22', 'yyyy-MM-dd HH24:MI:SS')
AND deliverassign.assign_time <=
TO_DATE ('2012-04-23 23:59:59', 'yyyy-MM-dd HH24:MI:SS')
;
Explained
SQL> set linesize 10000;
SQL> set pagesize 10000;
SQL> set col 10000;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 4292618648
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 89 | 1792K (1)| 05:58:30 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_TICKETS | 1 | 16 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TORDERINFO_PORDER_FK | 1 | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 89 | 8625 (1)| 00:01:44 |
| 6 | NESTED LOOPS OUTER | | 1 | 85 | 8624 (1)| 00:01:44 |
| 7 | NESTED LOOPS OUTER | | 1 | 71 | 8623 (1)| 00:01:44 |
| 8 | NESTED LOOPS | | 1 | 63 | 8622 (1)| 00:01:44 |
|* 9 | HASH JOIN | | 2139 | 89838 | 7120 (1)| 00:01:26 |
|* 10 | TABLE ACCESS FULL | T_TO_TASK | 48649 | 427K| 414 (7)| 00:00:05 |
|* 11 | TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_INFO | 203K| 4965K| 6693 (1)| 00:01:21 |
| 12 | NESTED LOOPS | | 101K| 3277K| 6700 (1)| 00:01:21 |
| 13 | SORT UNIQUE | | 1 | 8 | 6 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | T_TP_CASHER | 1 | 8 | 6 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | OUTTCOLLABORATOR_FK | 35554 | | 36 (3)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 21 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | 1 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 15 | | |
|* 19 | TABLE ACCESS BY INDEX ROWID| T_TO_DELIVER_ASSIGN_RECORD | 1 | 15 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | 1 (0)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID | T_TP_CASHER | 1 | 8 | 1 (0)| 00:00:01 |
|* 22 | INDEX UNIQUE SCAN | PK_T_TP_CASHER | 1 | | 1 (0)| 00:00:01 |
| 23 | TABLE ACCESS BY INDEX ROWID | T_TP_EMPLOYEE_DELIVER | 1 | 14 | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_T_TP_EMPLOYEE_DELIVER | 1 | | 1 (0)| 00:00:01 |
|* 25 | INDEX UNIQUE SCAN | PK_T_S_EMPLOYEE | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
3 - access("T_TO_ORDER_TICKETS"."ORDER_ID"=:B1)
4 - filter(TO_DATE('2012-04-22','yyyy-MM-dd HH24:MI:SS')<=TO_DATE('2012-04-23 23:59:59', 'yyyy-mm-dd
hh24:mi:ss'))
9 - access("T_TO_TASK"."ORDER_ID"="T_TO_ORDER_INFO"."ORDER_ID")
10 - filter("T_TO_TASK"."TASK_TYPE_ID"=7)
11 - filter("T_TO_ORDER_INFO"."ORDER_STATUS_ID"=5 OR "T_TO_ORDER_INFO"."ORDER_STATUS_ID"=6)
14 - filter("EMPLOYEE_ID"=2838)
15 - access("T_TO_ORDER_INFO"."PARTNER_ID"="PARTNER_ID")
16 - filter("DELIVERASSIGN"."ASSIGN_TIME">=TO_DATE('2012-04-22','yyyy-MM-dd HH24:MI:SS') AND
"DELIVERASSIGN"."ASSIGN_TIME"<=TO_DATE('2012-04-23 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
17 - access("DELIVERASSIGN"."ASSIGN_ID"= (SELECT /*+ */ MAX("ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD"
"T_TO_DELIVER_ASSIGN_RECORD" WHERE "T_TO_DELIVER_ASSIGN_RECORD"."ORDER_ID"=:B1 AND
"T_TO_DELIVER_ASSIGN_RECORD"."ORDER_TYPE"=1))
19 - filter("T_TO_DELIVER_ASSIGN_RECORD"."ORDER_TYPE"=1)
20 - access("T_TO_DELIVER_ASSIGN_RECORD"."ORDER_ID"=:B1)
22 - access("DELIVERASSIGN"."CASHER_ID"="TTC"."CASHER_ID"(+))
24 - access("DELIVERASSIGN"."DELIVER_EMPLOYEE_ID"="T_TP_EMPLOYEE_DELIVER"."DELIVER_EMPLOYEE_ID"(+))
25 - access("TE"."EMPLOYEE_ID"(+)="TTC"."EMPLOYEE_ID")
55 rows selected
统计信息
----------------------------------------------------------
2263 recursive calls
34 db block gets
2116 consistent gets
90 physical reads
0 redo size
5467 bytes sent via SQL*Net to client
268 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
28 sorts (memory)
0 sorts (disk)
55 rows processed=============================================================================
/*+ no_unnest */ 子查询解嵌套 where后面,不展开就走filter，展开了就表连接了，filter工作机制类似于嵌套循环
/*+ no_merge */ 视图融合 from 后面
这里就可以是子查询解嵌套，解的是半连接，类似in或者exsist的，把它转化成连接
AND t_to_order_info.partner_id IN (SELECT partner_id
FROM t_tp_casher
WHERE employee_id = 2838)) orderinfo
from后面的子查询也相同于一个内嵌视图，视图融合就是将视图里面的条件展开与外面的
等值条件做为连接
我加/*+ no_merge */
FROM (SELECT /*+ no_merge */ t_to_order_info.order_id,
(SELECT MAX (assign_id)
FROM t_to_deliver_assign_record
WHERE t_to_deliver_assign_record.order_type = 1
写成这种，就会在执行计划里面看到view的字样
=======================================================================================================
内嵌视图orderinfo大概跑17秒，出来1000多行数据，这里考虑不让它展开，加hints强制
执行，SQL和执行计划如何下，运行仍然是4分钟
SQL> explain plan for SELECT orderinfo.order_id,
guest_num,
guest_name,
pnr,
orderinfo.order_flow_id,
t_to_deliver_assign_record.deliver_type_id,
t_to_deliver_assign_record.deliver_employee_id,
t_tp_employee_deliver.deliver_employee_name,
t_to_deliver_assign_record.assign_time
FROM (SELECT /*+ no_merge*/t_to_order_info.order_id,
(SELECT MAX(assign_id)
FROM t_to_deliver_assign_record
WHERE t_to_deliver_assign_record.order_type = 1
AND t_to_deliver_assign_record.order_id =
t_to_order_info.order_id) maxdeliverid,
guest_num,
pnr,
t_to_order_info.order_flow_id,
(SELECT guest_name
FROM t_to_order_tickets
WHERE t_to_order_tickets.order_id = t_to_order_info.order_id
AND ROWNUM <= 1) || CASE guest_num
WHEN 1 THEN
''
ELSE
' ...'
END guest_name
FROM t_to_task
LEFT JOIN t_to_order_info
ON t_to_task.order_id = t_to_order_info.order_id
WHERE (t_to_order_info.order_status_id = 6 or
t_to_order_info.order_status_id = 5)
AND t_to_task.task_type_id = 7
AND t_to_order_info.partner_id IN
(SELECT partner_id FROM t_tp_casher WHERE employee_id = 2838)) orderinfo
INNER JOIN t_to_deliver_assign_record
ON t_to_deliver_assign_record.assign_id = orderinfo.maxdeliverid
LEFT JOIN t_tp_employee_deliver
ON t_to_deliver_assign_record.deliver_employee_id =
t_tp_employee_deliver.deliver_employee_id
LEFT JOIN t_tp_casher ttc
ON t_to_deliver_assign_record.casher_id = ttc.casher_id
LEFT JOIN t_s_employee te
ON te.employee_id = ttc.employee_id
WHERE 1 = 1
AND t_to_deliver_assign_record.assign_time >=
TO_DATE('2012-04-22', 'yyyy-MM-dd HH24:MI:SS')
AND t_to_deliver_assign_record.assign_time <=
TO_DATE('2012-04-23 23:59:59', 'yyyy-MM-dd HH24:MI:SS')
/
Explained
SQL> set col 1000
SQL> set pagesize 1000
SQL> set linesize 1000
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2088732904
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 283 | 7152 (1)| 00:01:26 |
| 1 | NESTED LOOPS OUTER | | 1 | 283 | 7152 (1)| 00:01:26 |
| 2 | NESTED LOOPS OUTER | | 1 | 279 | 7151 (1)| 00:01:26 |
| 3 | NESTED LOOPS OUTER | | 1 | 265 | 7150 (1)| 00:01:26 |
|* 4 | HASH JOIN | | 1 | 257 | 7149 (1)| 00:01:26 |
| 5 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 608 | 12768 | 28 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_DEL_ASS_TIME | 624 | | 1 (0)| 00:00:01 |
| 7 | VIEW | | 2139 | 492K| 7120 (1)| 00:01:26 |
|* 8 | FILTER | | | | | |
|* 9 | HASH JOIN | | 2139 | 89838 | 7120 (1)| 00:01:26 |
|* 10 | TABLE ACCESS FULL | T_TO_TASK | 48649 | 427K| 414 (7)| 00:00:05 |
|* 11 | TABLE ACCESS BY INDEX ROWID| T_TO_ORDER_INFO | 203K| 4965K| 6693 (1)| 00:01:21 |
| 12 | NESTED LOOPS | | 101K| 3277K| 6700 (1)| 00:01:21 |
| 13 | SORT UNIQUE | | 1 | 8 | 6 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | T_TP_CASHER | 1 | 8 | 6 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | OUTTCOLLABORATOR_FK | 35554 | | 36 (3)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | T_TP_CASHER | 1 | 8 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | PK_T_TP_CASHER | 1 | | 1 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | T_TP_EMPLOYEE_DELIVER | 1 | 14 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | PK_T_TP_EMPLOYEE_DELIVER | 1 | | 1 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | PK_T_S_EMPLOYEE | 1 | 4 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T_TO_DELIVER_ASSIGN_RECORD"."ASSIGN_ID"="ORDERINFO"."MAXDELIVERID")
6 - access("T_TO_DELIVER_ASSIGN_RECORD"."ASSIGN_TIME">=TO_DATE('2012-04-22','yyyy-MM-dd HH24:MI:SS')
AND "T_TO_DELIVER_ASSIGN_RECORD"."ASSIGN_TIME"<=TO_DATE('2012-04-23 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
8 - filter(TO_DATE('2012-04-22','yyyy-MM-dd HH24:MI:SS')<=TO_DATE('2012-04-23 23:59:59', 'yyyy-mm-dd
hh24:mi:ss'))
9 - access("T_TO_TASK"."ORDER_ID"="T_TO_ORDER_INFO"."ORDER_ID")
10 - filter("T_TO_TASK"."TASK_TYPE_ID"=7)
11 - filter("T_TO_ORDER_INFO"."ORDER_STATUS_ID"=5 OR "T_TO_ORDER_INFO"."ORDER_STATUS_ID"=6)
14 - filter("EMPLOYEE_ID"=2838)
15 - access("T_TO_ORDER_INFO"."PARTNER_ID"="PARTNER_ID")
17 - access("T_TO_DELIVER_ASSIGN_RECORD"."CASHER_ID"="TTC"."CASHER_ID"(+))
19 - access("T_TO_DELIVER_ASSIGN_RECORD"."DELIVER_EMPLOYEE_ID"="T_TP_EMPLOYEE_DELIVER"."DELIVER_EMPLOYE
E_ID"(+))
20 - access("TE"."EMPLOYEE_ID"(+)="TTC"."EMPLOYEE_ID")
45 rows selected
速度没有提升，倒是观察到一个东西
9 - access("T_TO_TASK"."ORDER_ID"="T_TO_ORDER_INFO"."ORDER_ID")
10 - filter("T_TO_TASK"."TASK_TYPE_ID"=7)建立组合索引，然后让其视图融合，不到1秒出结果
create index index_type_orderid_sec on T_TO_TASK (ORDER_ID,TASK_TYPE_ID) online nologging
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2729681438
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 89 | 404K (1)| 01:20:54 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_TICKETS | 1 | 16 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TORDERINFO_PORDER_FK | 1 | | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | NESTED LOOPS OUTER | | 1 | 89 | 755 (17)| 00:00:10 |
| 6 | NESTED LOOPS OUTER | | 1 | 85 | 754 (17)| 00:00:10 |
| 7 | NESTED LOOPS OUTER | | 1 | 71 | 753 (17)| 00:00:10 |
| 8 | NESTED LOOPS | | 1 | 63 | 752 (17)| 00:00:10 |
|* 9 | HASH JOIN RIGHT SEMI | | 482 | 20244 | 414 (30)| 00:00:05 |
|* 10 | TABLE ACCESS FULL | T_TP_CASHER | 1 | 8 | 6 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 48664 | 1615K| 406 (30)| 00:00:05 |
|* 12 | INDEX FAST FULL SCAN | INDEX_TYPE_ORDERID_SEC | 48649 | 427K| 203 (10)| 00:00:03 |
|* 13 | TABLE ACCESS BY INDEX ROWID | T_TO_ORDER_INFO | 1 | 25 | 1 (0)| 00:00:01 |
|* 14 | INDEX UNIQUE SCAN | PK_T_TO_ORDER_INFO | 1 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID | T_TO_DELIVER_ASSIGN_RECORD | 1 | 21 | 1 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | PK_T_TO_DELIVER_ASSIGN_RECORD | 1 | | 1 (0)| 00:00:01 |
| 17 | SORT AGGREGATE | | 1 | 15 | | |
|* 18 | TABLE ACCESS BY INDEX ROWID| T_TO_DELIVER_ASSIGN_RECORD | 1 | 15 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | TORDERADDRESS_FK | 1 | | 1 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | T_TP_CASHER | 1 | 8 | 1 (0)| 00:00:01 |
|* 21 | INDEX UNIQUE SCAN | PK_T_TP_CASHER | 1 | | 1 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | T_TP_EMPLOYEE_DELIVER | 1 | 14 | 1 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | PK_T_TP_EMPLOYEE_DELIVER | 1 | | 1 (0)| 00:00:01 |
|* 24 | INDEX UNIQUE SCAN | PK_T_S_EMPLOYEE | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
3 - access("T_TO_ORDER_TICKETS"."ORDER_ID"=:B1)
4 - filter(TO_DATE('2012-04-22','yyyy-MM-dd HH24:MI:SS')<=TO_DATE('2012-04-23 23:59:59', 'yyyy-mm-dd
hh24:mi:ss'))
9 - access("T_TO_ORDER_INFO"."PARTNER_ID"="PARTNER_ID")
10 - filter("EMPLOYEE_ID"=2838)
12 - filter("T_TO_TASK"."TASK_TYPE_ID"=7)
13 - filter("T_TO_ORDER_INFO"."ORDER_STATUS_ID"=5 OR "T_TO_ORDER_INFO"."ORDER_STATUS_ID"=6)
14 - access("T_TO_TASK"."ORDER_ID"="T_TO_ORDER_INFO"."ORDER_ID")
15 - filter("DELIVERASSIGN"."ASSIGN_TIME">=TO_DATE('2012-04-22','yyyy-MM-dd HH24:MI:SS') AND
"DELIVERASSIGN"."ASSIGN_TIME"<=TO_DATE('2012-04-23 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
16 - access("DELIVERASSIGN"."ASSIGN_ID"= (SELECT /*+ */ MAX("ASSIGN_ID") FROM "T_TO_DELIVER_ASSIGN_RECORD"
"T_TO_DELIVER_ASSIGN_RECORD" WHERE "T_TO_DELIVER_ASSIGN_RECORD"."ORDER_ID"=:B1 AND
"T_TO_DELIVER_ASSIGN_RECORD"."ORDER_TYPE"=1))
18 - filter("T_TO_DELIVER_ASSIGN_RECORD"."ORDER_TYPE"=1)
19 - access("T_TO_DELIVER_ASSIGN_RECORD"."ORDER_ID"=:B1)
21 - access("DELIVERASSIGN"."CASHER_ID"="TTC"."CASHER_ID"(+))
23 - access("DELIVERASSIGN"."DELIVER_EMPLOYEE_ID"="T_TP_EMPLOYEE_DELIVER"."DELIVER_EMPLOYEE_ID"(+))
24 - access("TE"."EMPLOYEE_ID"(+)="TTC"."EMPLOYEE_ID")
54 rows selected
注意：就在这个下面第12步提升了效率
|* 12 | INDEX FAST FULL SCAN | INDEX_TYPE_ORDERID_SEC | 48649 | 427K
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
