Oracle語句最佳化之一

標籤：

public List<TdDepartment> createZtreeDep(String compId) {                List<TdDepartment> dd = new ArrayList<TdDepartment>();       //尋找所有子部門的sql語句
　　　　 String sql = "select dep_id,dep_name,super_id,folder from td_department "+                        "start with super_id in ( "+                                                "select dep_id  from td_department "+                         "where valid_flag = ‘Y‘  and comp_id = ‘"+compId+"‘)connect by prior dep_id = super_id";        
　　　　//尋找部門本身的sql語句        String parentsql = "select dep_id ,dep_name,super_id,folder  from td_department "+                    "where valid_flag = ‘Y‘  and comp_id = ‘"+compId+"‘";
        EpDB db = new EpDB();                ArrayList<HashMap> parentdeparts = db.getHashData(parentsql);        ArrayList<HashMap> departs = db.getHashData(sql);        if(parentdeparts == null || parentdeparts.size() <= 0)            return null;      
　　　　//將部門加入到list中          for(int i=0; i<parentdeparts.size(); i++){            String dep_id = parentdeparts.get(i).get("DEP_ID").toString();            String dep_name = parentdeparts.get(i).get("DEP_NAME").toString();            String super_id = parentdeparts.get(i).get("SUPER_ID").toString();                        TdDepartment td = new TdDepartment();            td.setId(dep_id);            td.setPid(super_id);            td.setName(dep_name);            if(parentdeparts.get(i).get("FOLDER") != null){                String folder = parentdeparts.get(i).get("FOLDER").toString();                if("Y".equals(folder)){                    td.setOpen("true");//非葉子結點則需要展開                }else{                    td.setOpen("false");                }            }            //取消迴圈讀取            //td.setChild(createDepartments(dep_id));                        dd.add(td);        }　　　　　//將子部門加入到list中　        for(int i=0; i<departs.size(); i++){            String dep_id = departs.get(i).get("DEP_ID").toString();            String dep_name = departs.get(i).get("DEP_NAME").toString();            String super_id = departs.get(i).get("SUPER_ID").toString();                        TdDepartment td = new TdDepartment();            td.setId(dep_id);            td.setPid(super_id);            td.setName(dep_name);            if(departs.get(i).get("FOLDER") != null){                String folder = departs.get(i).get("FOLDER").toString();                if("Y".equals(folder)){                    td.setOpen("true");//非葉子結點則需要展開                }else{                    td.setOpen("false");                }            }            //取消迴圈讀取            //td.setChild(createDepartments(dep_id));                        dd.add(td);        }                return dd;    }

需要最佳化點：

思考一：通過sql語句的最佳化來最佳化

思考二：通過程式遞迴的方法來最佳化

Oracle語句最佳化之一