视图查询 非常卡,怎么优化
一个视图重复运用,会造成卡?
-- 获取mapping
create or replace function Get_Dept_Mapping(MyORGID in varchar2)
return varchar2 is
FunctionResult varchar2(300);
begin
FunctionResult:='';
for rec in (select tt.RA
from (select RA, ORG_ID from V_DEPT t start with ORG_ID=MyORGID connect by prior PARENT_ORG_ID = ORG_ID) tt
order by tt.RA) loop
FunctionResult:=FunctionResult || rec.RA || ',';
end loop;
return(FunctionResult);
end Get_Dept_Mapping;
-- 部门视图
create or replace view v_dept as
select tab."RA",tab."ORG_ID",tab."PARENT_ORG_ID",tab."ORG_NAME" from
(select rownum AS RA,"ID" as ORG_ID , PARENTID AS PARENT_ORG_ID ,"NAME" as ORG_NAME
from GD_DEPT t
start with PARENTID = '0' connect by prior "ID" = PARENTID) tab;
-- 部门表
ID VARCHAR2(255)
ACCOUNTID VARCHAR2(255)
NAME VARCHAR2(255)
PARENTID VARCHAR2(255)
REMAK VARCHAR2(255)
索引 : ID . PARENTID
-- 查询初始化后的 数据 ,这个查询非常非常卡,求教怎么优化下
select tt.*, get_dept_mapping(tt.org_id) as mymap, nvl(aa.RA,0) as PID from v_dept tt
left join v_dept aa on tt.PARENT_ORG_ID= aa.ORG_ID