专栏首页Hadoop数据仓库使用Oracle with内嵌视图优化一例

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

需求:

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

[sql] view plain copy

  1. -- 创建分类表
  2. CREATE TABLE tab_class
  3. (
  4. id NUMBER (8) NOT NULL PRIMARY KEY,
  5. p_id NUMBER (8) NOT NULL,
  6. class_name VARCHAR2 (10),
  7. siblings_order NUMBER (4)
  8. );
  9. -- 创建物资表
  10. CREATE TABLE tab_item
  11. (
  12. id NUMBER (8) NOT NULL PRIMARY KEY,
  13. item_name VARCHAR2 (10),
  14. create_time DATE
  15. );
  16. -- 创建分类/物资关系表
  17. CREATE TABLE tab_item_class
  18. (
  19. id NUMBER (8) NOT NULL PRIMARY KEY,
  20. class_id NUMBER (8) NOT NULL,
  21. item_id NUMBER (8) NOT NULL
  22. );
  23. -- 生成1万条分类表数据
  24. DECLARE
  25. p_id NUMBER (8) DEFAULT 0;
  26. class_name VARCHAR2 (10);
  27. BEGIN
  28. FOR i IN 1 .. 10000
  29. LOOP
  30. p_id := FLOOR (SQRT (i - 1));
  31. class_name := SUBSTR (TO_CHAR (DBMS_RANDOM.VALUE), 1, 10);
  32. INSERT INTO tab_class (id,
  33. p_id,
  34. class_name,
  35. siblings_order)
  36. VALUES (i,
  37. p_id,
  38. class_name,
  39. 0);
  40. END LOOP;
  41. UPDATE tab_class tc
  42. SET tc.siblings_order =
  43. (SELECT xx.rn
  44. FROM (SELECT tc2.id,
  45. ROW_NUMBER ()
  46. OVER (PARTITION BY tc2.p_id
  47. ORDER BY tc2.class_name)
  48. rn
  49. FROM tab_class tc2) xx
  50. WHERE xx.id = tc.id);
  51. COMMIT;
  52. END;
  53. /
  54. -- 生成100万条物资表数据
  55. DECLARE
  56. item_name VARCHAR2 (10);
  57. rd_date NUMBER (3);
  58. BEGIN
  59. FOR i IN 1 .. 1000000
  60. LOOP
  61. item_name := SUBSTR (TO_CHAR (DBMS_RANDOM.VALUE), 1, 10);
  62. rd_date := FLOOR (1000 * (DBMS_RANDOM.VALUE - 0.5));
  63. INSERT INTO tab_item (id, item_name, create_time)
  64. VALUES (i, item_name, SYSDATE + rd_date);
  65. END LOOP;
  66. COMMIT;
  67. END;
  68. /
  69. -- 生成分类/物资关系表数据
  70. DECLARE
  71. ic_id NUMBER (8) DEFAULT 1;
  72. class_id NUMBER (8);
  73. item_id NUMBER (8);
  74. c_count NUMBER (2);
  75. BEGIN
  76. FOR item_id IN 1 .. 1000000
  77. LOOP
  78. c_count := FLOOR (10 * DBMS_RANDOM.VALUE) + 1;
  79. FOR i IN 1 .. c_count
  80. LOOP
  81. class_id := FLOOR (10000 * DBMS_RANDOM.VALUE) + 1;
  82. INSERT INTO tab_item_class (id, class_id, item_id)
  83. VALUES (ic_id, class_id, item_id);
  84. ic_id := ic_id + 1;
  85. END LOOP;
  86. END LOOP;
  87. COMMIT;
  88. END;
  89. /
  90. -- 建立索引
  91. CREATE INDEX idx_class_pid
  92. ON tab_class (p_id);
  93. CREATE INDEX idx_ic_class_id
  94. ON tab_item_class (class_id);
  95. CREATE INDEX idx_ic_item_id
  96. ON tab_item_class (item_id);
  97. -- 分析表
  98. ANALYZE TABLE tab_class COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;
  99. ANALYZE TABLE tab_item COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;
  100. ANALYZE TABLE tab_item_class COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;
  101. -- 查询语句1
  102. WITH vs_tree
  103. AS ( SELECT fls.id flid,
  104. fls.class_name name,
  105. SUBSTR (SYS_CONNECT_BY_PATH (siblings_order, '.'), 2) xuhao,
  106. ROWNUM rn
  107. FROM tab_class fls
  108. CONNECT BY PRIOR fls.id = fls.p_id
  109. START WITH fls.p_id = 0
  110. ORDER SIBLINGS BY fls.siblings_order)
  111. SELECT temp.flid, temp.name, temp.xuhao
  112. FROM (SELECT t.flid,
  113. t.name,
  114. t.xuhao,
  115. t.rn,
  116. 0 xx
  117. FROM vs_tree t
  118. UNION ALL
  119. SELECT t.flid,
  120. wz.item_name,
  121. t.xuhao
  122. || '.'
  123. || (ROW_NUMBER ()
  124. OVER (PARTITION BY t.flid ORDER BY wz.create_time ASC))
  125. xuhao,
  126. t.rn,
  127. ROW_NUMBER ()
  128. OVER (PARTITION BY t.flid ORDER BY wz.create_time ASC)
  129. xx
  130. FROM vs_tree, tab_item_class gx, tab_item wz
  131. WHERE wz.id = gx.item_id AND t.flid = gx.class_id) temp
  132. ORDER BY temp.rn, temp.xx;
  133. -- 用时:7分9秒
  134. -- 查询语句2
  135. SELECT flid, name, RTRIM (SUBSTR (xuhao1, 2) || '.' || xx, '.') xuhao
  136. FROM ( SELECT flid,
  137. name,
  138. SYS_CONNECT_BY_PATH (siblings_order, '.') xuhao1,
  139. xx,
  140. ROWNUM rid
  141. FROM (SELECT fls.p_id pid,
  142. fls.id flid,
  143. fls.class_name name,
  144. siblings_order,
  145. 0 xx
  146. FROM tab_class fls
  147. UNION ALL
  148. SELECT t.pid,
  149. t.flid,
  150. wz.item_name,
  151. siblings_order,
  152. rom_number ()
  153. OVER (PARTITION BY t.flid ORDER BY wz.create_time ASC)
  154. xx
  155. FROM (SELECT fls.p_id pid,
  156. fls.id flid,
  157. fls.class_name name,
  158. siblings_order
  159. FROM tab_class fls) t,
  160. tab_item_class gx,
  161. tab_item wz
  162. WHERE wz.id = gx.item_id AND t.flid = gx.class_id) temp
  163. CONNECT BY PRIOR flid = pid
  164. START WITH pid = 0
  165. ORDER SIBLINGS BY siblings_order, xx)
  166. ORDER BY rid;
  167. -- 用时:半小时没出来,中断退出

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 递归层次汇总查询

    用户1148526
  • 浅尝辄止MongoDB:GridFS

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.n...

    用户1148526
  • 批量取redis key/value和批量删除key

    需求:取出所有前缀为g.at.ga.的string类型的key及其value 实现: 1. 取出key redis-cli -p 26379 -a 123...

    用户1148526
  • MyBatis学习总结(五)——实现关联表查询

    一枝花算不算浪漫
  • 一个适用于ListView/GridView/RecyclerView的通用适配器

    续写经典
  • 唯品会数据采集-异步瀑布流 python3抓取异步百度瀑布流动态图片(二)get、json下载代码讲解

    采集入口为手机端,在火狐浏览器下ctrl+shift+M进入手机模式,并点击触屏模式,进入唯品会网站m.vip.com,刷新网页。

    机器学习和大数据挖掘
  • MyBatis 实现关联表查询

    一、一对一关联  1.1、提出需求   根据班级id查询班级信息(带老师的信息) 1.2、创建表和数据   创建一张教师表和班级表,这里我们假设一个老师只负责教...

    庞小明
  • Mybatid关联表查询

    一、一对一关联  1.1、提出需求   根据班级id查询班级信息(带老师的信息) 1.2、创建表和数据   创建一张教师表和班级表,这里我们假设一个老师只负...

    汤高
  • 以金山界面库(openkui)为例思考和分析界面库的设计和实现——问题

            随着物质生活的丰富,人们的精神生活也越来越丰富。人们闲暇的时间也相对变多,于是很多人就开始寻找打发时间的方法。其中电视便是其中一种非常重要的消遣...

    方亮
  • OpenStack Apis Authenticate (Token ID&Tenant ID)

    tanmx

扫码关注云+社区

领取腾讯云代金券