视图融合的例子
来源：互联网 发布：淘宝店被释放了怎么办 编辑：程序博客网 时间：2024/05/01 16:28
今天找到一条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') / ExplainedSQL> set col 1000SQL> set pagesize 1000SQL> set linesize 1000SQL> 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- 视图融合的例子
- GBDT+LR特征融合的例子
- 一个模型视图的例子
- 定义视图的小例子
- 多视图关联选择的例子
- 一个保存视图状态的小例子
- 一个分段视图使用的例子
- Extjs4.2 线图和柱图的融合图的简单例子1
- silverlight 融合ASP.NET MVC 例子
- D3D的动作融合
- 地址的融合
- 投影机融合的工作报告
- “融合”的力量
- 投影融合的要求
- opencv-图像的融合
- 简单的图像融合
- 编程宗派的融合
- 简单的贴图融合
- 如何编写DLL实例,很早以前的笔记
- 端口号
- C# WinForm窗口最小化到系统托盘 右键操作菜单
- android疑问 学习链接
- const static总结
- 视图融合的例子
- mysql用户创建
- Xorg 硬件加速
- 一个数据挖掘算法的项目:dami
- iphone的内存管理（摘自iphone文档，英语）
- Java虚拟机类装载的原理及实现
- android 内存溢出 内存管理
- Linux备份命令rsync 安装和使用
- RMAN 系列（九） ---- 调整RMAN备份与恢复操作的性能
