Oracle根据执行计划进行优化

需求
根据输入的子节点参数递归查询所有上级节点，并拼接成字符串显示。数据量大查找耗时久

单行测试执行结果

//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) 

单行执行时间虽然没什么太大差异，但是升级数据量查询时，时间终于降下去了。

推荐一篇看懂执行计划的文章，很有用。
执行计划解读
完结★,°:.☆(￣▽￣)/$:.°★ 。