使用Oracle with内嵌视图优化一例

用户1148526

发布于 2019-05-25 19:45:07

6210

发布于 2019-05-25 19:45:07

文章被收录于专栏：Hadoop数据仓库

需求：

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

[sql] view plain copy

-- 创建分类表
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万条分类表数据
DECLARE
p_id NUMBER (8) DEFAULT 0;
class_name VARCHAR2 (10);
BEGIN
FOR i IN 1 .. 10000
LOOP
p_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 tc
SET tc.siblings_order =
(SELECT xx.rn
FROM (SELECT tc2.id,
ROW_NUMBER ()
OVER (PARTITION BY tc2.p_id
ORDER BY tc2.class_name)
rn
FROM tab_class tc2) xx
WHERE xx.id = tc.id);
COMMIT;
END;
/
-- 生成100万条物资表数据
DECLARE
item_name VARCHAR2 (10);
rd_date NUMBER (3);
BEGIN
FOR i IN 1 .. 1000000
LOOP
item_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;
/
-- 生成分类/物资关系表数据
DECLARE
ic_id NUMBER (8) DEFAULT 1;
class_id NUMBER (8);
item_id NUMBER (8);
c_count NUMBER (2);
BEGIN
FOR item_id IN 1 .. 1000000
LOOP
c_count := FLOOR (10 * DBMS_RANDOM.VALUE) + 1;
FOR i IN 1 .. c_count
LOOP
class_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_pid
ON tab_class (p_id);
CREATE INDEX idx_ic_class_id
ON tab_item_class (class_id);
CREATE INDEX idx_ic_item_id
ON 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;
-- 查询语句1
WITH vs_tree
AS ( SELECT fls.id flid,
fls.class_name name,
SUBSTR (SYS_CONNECT_BY_PATH (siblings_order, '.'), 2) xuhao,
ROWNUM rn
FROM tab_class fls
CONNECT BY PRIOR fls.id = fls.p_id
START WITH fls.p_id = 0
ORDER SIBLINGS BY fls.siblings_order)
SELECT temp.flid, temp.name, temp.xuhao
FROM (SELECT t.flid,
t.name,
t.xuhao,
t.rn,
0 xx
FROM vs_tree t
UNION ALL
SELECT 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)
xx
FROM vs_tree, tab_item_class gx, tab_item wz
WHERE wz.id = gx.item_id AND t.flid = gx.class_id) temp
ORDER BY temp.rn, temp.xx;
-- 用时：7分9秒
-- 查询语句2
SELECT flid, name, RTRIM (SUBSTR (xuhao1, 2) || '.' || xx, '.') xuhao
FROM ( SELECT flid,
name,
SYS_CONNECT_BY_PATH (siblings_order, '.') xuhao1,
xx,
ROWNUM rid
FROM (SELECT fls.p_id pid,
fls.id flid,
fls.class_name name,
siblings_order,
0 xx
FROM tab_class fls
UNION ALL
SELECT t.pid,
t.flid,
wz.item_name,
siblings_order,
rom_number ()
OVER (PARTITION BY t.flid ORDER BY wz.create_time ASC)
xx
FROM (SELECT fls.p_id pid,
fls.id flid,
fls.class_name name,
siblings_order
FROM tab_class fls) t,
tab_item_class gx,
tab_item wz
WHERE wz.id = gx.item_id AND t.flid = gx.class_id) temp
CONNECT BY PRIOR flid = pid
START WITH pid = 0
ORDER SIBLINGS BY siblings_order, xx)
ORDER BY rid;
-- 用时：半小时没出来，中断退出