使用Oraclewith内嵌视图优化一例

2017-01-04 19:29:25来源:作者:人点击

第七城市

需求:

有分类、物资、分类/物资关系三个表,要求按树的遍历方式查询出分类ID、分类/物资名称、从根到当前节点的路径。一个分类下的物资显示在该分类下,同一级分类按序号排序,一个分类下的物资按创建时间排序。

[sql] view plain copy
  1. -- 创建分类表CREATE TABLE tab_class(id NUMBER (8) NOT NULL PRIMARY KEY,p_id NUMBER (8) NOT NULL,class_name VARCHAR2 (10),siblings_order NUMBER (4));-- 创建物资表CREATE TABLE tab_item(id NUMBER (8) NOT NULL PRIMARY KEY,item_name VARCHAR2 (10),create_time DATE);-- 创建分类/物资关系表CREATE TABLE tab_item_class(id NUMBER (8) NOT NULL PRIMARY KEY,class_id NUMBER (8) NOT NULL,item_id NUMBER (8) NOT NULL);-- 生成1万条分类表数据DECLAREp_id NUMBER (8) DEFAULT 0;class_name VARCHAR2 (10);BEGINFOR i IN 1 .. 10000LOOPp_id := FLOOR (SQRT (i - 1));class_name := SUBSTR (TO_CHAR (DBMS_RANDOM.VALUE), 1, 10);INSERT INTO tab_class (id,p_id,class_name,siblings_order)VALUES (i,p_id,class_name,0);END LOOP;UPDATE tab_class tcSET tc.siblings_order =(SELECT xx.rnFROM (SELECT tc2.id,ROW_NUMBER ()OVER (PARTITION BY tc2.p_idORDER BY tc2.class_name)rnFROM tab_class tc2) xxWHERE xx.id = tc.id);COMMIT;END;/-- 生成100万条物资表数据DECLAREitem_name VARCHAR2 (10);rd_date NUMBER (3);BEGINFOR i IN 1 .. 1000000LOOPitem_name := SUBSTR (TO_CHAR (DBMS_RANDOM.VALUE), 1, 10);rd_date := FLOOR (1000 * (DBMS_RANDOM.VALUE - 0.5));INSERT INTO tab_item (id, item_name, create_time)VALUES (i, item_name, SYSDATE + rd_date);END LOOP;COMMIT;END;/-- 生成分类/物资关系表数据DECLAREic_id NUMBER (8) DEFAULT 1;class_id NUMBER (8);item_id NUMBER (8);c_count NUMBER (2);BEGINFOR item_id IN 1 .. 1000000LOOPc_count := FLOOR (10 * DBMS_RANDOM.VALUE) + 1;FOR i IN 1 .. c_countLOOPclass_id := FLOOR (10000 * DBMS_RANDOM.VALUE) + 1;INSERT INTO tab_item_class (id, class_id, item_id)VALUES (ic_id, class_id, item_id);ic_id := ic_id + 1;END LOOP;END LOOP;COMMIT;END;/-- 建立索引CREATE INDEX idx_class_pidON tab_class (p_id);CREATE INDEX idx_ic_class_idON tab_item_class (class_id);CREATE INDEX idx_ic_item_idON tab_item_class (item_id);-- 分析表ANALYZE TABLE tab_class COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;ANALYZE TABLE tab_item COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;ANALYZE TABLE tab_item_class COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;-- 查询语句1WITH vs_treeAS ( SELECT fls.id flid,fls.class_name name,SUBSTR (SYS_CONNECT_BY_PATH (siblings_order, '.'), 2) xuhao,ROWNUM rnFROM tab_class flsCONNECT BY PRIOR fls.id = fls.p_idSTART WITH fls.p_id = 0ORDER SIBLINGS BY fls.siblings_order)SELECT temp.flid, temp.name, temp.xuhaoFROM (SELECT t.flid,t.name,t.xuhao,t.rn,0 xxFROM vs_tree tUNION ALLSELECT t.flid,wz.item_name,t.xuhao|| '.'|| (ROW_NUMBER ()OVER (PARTITION BY t.flid ORDER BY wz.create_time ASC))xuhao,t.rn,ROW_NUMBER ()OVER (PARTITION BY t.flid ORDER BY wz.create_time ASC)xxFROM vs_tree, tab_item_class gx, tab_item wzWHERE wz.id = gx.item_id AND t.flid = gx.class_id) tempORDER BY temp.rn, temp.xx;-- 用时:7分9秒-- 查询语句2SELECT flid, name, RTRIM (SUBSTR (xuhao1, 2) || '.' || xx, '.') xuhaoFROM ( SELECT flid,name,SYS_CONNECT_BY_PATH (siblings_order, '.') xuhao1,xx,ROWNUM ridFROM (SELECT fls.p_id pid,fls.id flid,fls.class_name name,siblings_order,0 xxFROM tab_class flsUNION ALLSELECT t.pid,t.flid,wz.item_name,siblings_order,rom_number ()OVER (PARTITION BY t.flid ORDER BY wz.create_time ASC)xxFROM (SELECT fls.p_id pid,fls.id flid,fls.class_name name,siblings_orderFROM tab_class fls) t,tab_item_class gx,tab_item wzWHERE wz.id = gx.item_id AND t.flid = gx.class_id) tempCONNECT BY PRIOR flid = pidSTART WITH pid = 0ORDER SIBLINGS BY siblings_order, xx)ORDER BY rid;-- 用时:半小时没出来,中断退出

     

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台