需求
根据输入的子节点参数递归查询所有上级节点,并拼接成字符串显示。数据量大查找耗时久
单行测试执行结果
//1行 执行时间 0.050s
select wmsys.wm_concat(mg.fname_l2) wlmc
from EAS.T_BD_MaterialGroup mg
start with mg.fid = (
select mg.fid
from EAS.T_BD_Material m
where m.FMaterialGroupID=mg.fid and m.fnumber ='KJL3-BGL3-1'
)
connect by prior mg.FParentID = mg.fid;
为了方便多行测试 ,把执行过程创建成函数,进行数据量的测试
测试执行近2w数据执行了近50s ,这是什么鬼东西!!!
//执行函数
CREATE OR REPLACE
FUNCTION GET_MATERIALCAT(materialNumber IN VARCHAR)
RETURN VARCHAR Deterministic IS materialCat VARCHAR(255) ;
BEGIN
select wmsys.wm_concat(mg.fname_l2) into materialCat
from T_BD_MaterialGroup mg
start with mg.fid = (
select FMaterialGroupID
from T_BD_Material m
where m.FMaterialGroupID=mg.fid and fnumber =materialNumber
)
connect by prior mg.FParentID = mg.fid;
RETURN materialCat;
END;
//测试 49.388s
SELECT wlbm,GET_MATERIALCAT(wlbm) as name FROM (SELECT DISTINCT WLBM FROM ZCOF_XMCBGJMXB WHERE wlbm is not null)
这样的SQL搞出去掉人品,我都接受不了。前段时间考的OCP还是有用的,效率问题,查执行计划呗。连上正式机开始干活
SQL>SET AUTOTRACE ON ;//开启执行跟踪
SQL> set autotrace traceonly explain; //只打印执行计划
SQL> set linesize 1000
SQL> select wmsys.wm_concat(mg.fname_l2) wlmc
from EAS.T_BD_MaterialGroup mg
start with mg.fid = (
select mg.fid
from EAS.T_BD_Material m
where m.FMaterialGroupID=mg.fid and m.fnumber ='KJL3-BGL3-1'
)
connect by prior mg.FParentID = mg.fid; 2 3 4 5 6 7 8
Execution Plan
----------------------------------------------------------
Plan hash value: 504111097
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 170 | 12 (9)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 170 | | |
|* 2 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 3 | TABLE ACCESS FULL | T_BD_MATERIALGROUP | 801 | 42453 | 11 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IX_BD_MATE_GRANDNU | 1 | 32 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MG"."FID"=PRIOR "MG"."FPARENTID")
filter("MG"."FID"= (SELECT :B1 FROM "EAS"."T_BD_MATERIAL" "M" WHERE
"M"."FNUMBER"=U'KJL3-BGL3-1' AND "M"."FMATERIALGROUPID"=:B2))
4 - access("M"."FMATERIALGROUPID"=:B1 AND "M"."FNUMBER"=U'KJL3-BGL3-1')
第一步就出了个全表扫描,(′д` )…彡…彡 800多行 那两万行的数据乘以800,效率不低才不正常。知道了问题那就改呗。看看写的东西,又查了下递归sql的小知识,被自己气死了
//修改前 明明可以不进行连接关联查询,
select wmsys.wm_concat(mg.fname_l2) wlmc
from EAS.T_BD_MaterialGroup mg
start with mg.fid = (
select mg.fid
from EAS.T_BD_Material m
where m.FMaterialGroupID=mg.fid and m.fnumber ='KJL3-BGL3-1'
)
connect by prior mg.FParentID = mg.fid;
//修改后 分类和明细 有直接关联关系,不需要进行连接关联
select wmsys.wm_concat(mg.fname_l2) wlmc
from EAS.T_BD_MaterialGroup mg
start with mg.fid =
(
select FMaterialGroupID
from EAS.T_BD_Material
where fnumber ='KJL3-BGL3-1'
)
connect by prior mg.FParentID = mg.fid;
//执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 777942556
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 170 | 8 (25)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 170 | | |
|* 2 | CONNECT BY WITH FILTERING | | | | | |
| 3 | NESTED LOOPS | | 1 | 85 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T_BD_MATERIAL | 1 | 32 | 2 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | UX_BD_MATE_FNUM | 1 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID| T_BD_MATERIALGROUP | 1 | 53 | 1 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | PK_MATERIALGROUP | 1 | | 1 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 77 | 4 (0)| 00:00:01 |
| 9 | CONNECT BY PUMP | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID| T_BD_MATERIALGROUP | 1 | 53 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_MATERIALGROUP | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("MG"."FID"=PRIOR "MG"."FPARENTID")
5 - access("FNUMBER"=U'KJL3-BGL3-1')
7 - access("MG"."FID"="FMATERIALGROUPID")
11 - access("connect$_by$_pump$_005"."prior mg.FParentID "="MG"."FID")
//多行查询
SELECT wlbm,GET_MATERIALCAT(wlbm) as name FROM (SELECT DISTINCT WLBM FROM ZCOF_XMCBGJMXB WHERE wlbm is not null)
单行执行时间虽然没什么太大差异,但是升级数据量查询时,时间终于降下去了。
推荐一篇看懂执行计划的文章,很有用。
执行计划解读
完结★,°:.☆( ̄▽ ̄)/$:.°★ 。