需求:
有一张旧的权限表,7个等级(1-7)。除1级以外,每个用户ID都有关联一个上级用户ID。
要做一张新表,把每个人对应7级的ID都抽出来。
create table new_Competence(
userid varchar(10),
parentid vachar(10),
level number(2),
avail_start varchar(8),
avail_end varchar(8),
level1 number(2),
level2 number(2),
…
level7 number(2),
);
实现方案:
开始按常规考虑,一个小游标抽出全体,再写一个过程,循环调用。一测试,好了4万的数据用了半个多小时。
仔细分析一下,查询次数太多了,游标记录4000件左右,每条数据次还要再查3-4次(平均)。
先考虑优化,将全局条件抽取后做一个临时表(4000)件,改后再测,2分钟。好多了,但还不够理想。
条件中有很多TRIM()函数,无法使用索引优化。
于是试着用WITH写了一下一个SQL(省略了一些不太重要的查询条件):
WITH
Temp AS (SELECT * FROM Old_Competence WHERE avail_start <= &APL_DATE and avail_end >= &APL_DATE ),
LEV1 AS (SELECT * FROM Temp WHERE level = 1),
LEV2 AS (SELECT * FROM Temp WHERE level = 2),
LEV3 AS (SELECT * FROM Temp WHERE level = 3),
LEV4 AS (SELECT * FROM Temp WHERE level = 4),
LEV5 AS (SELECT * FROM Temp WHERE level = 5),
LEV6 AS (SELECT * FROM Temp WHERE level = 6),
LEV7 AS (SELECT * FROM Temp WHERE level = 7)
(SELECT LEV1.*, null uc1, null uc2, null uc3, null uc4, null uc5, null uc6, null uc7
FROM LEV1
UNION ALL
SELECT LEV2.*, LEV1.unitcode uc1, null uc2, null uc3, null uc4, null uc5, null uc6, null uc7
FROM LEV1, LEV2
WHERE LEV1.book_id = LEV2.parent_id
UNION ALL
SELECT LEV3.*, LEV1.unitcode uc1, LEV2.unitcode uc2, null uc3, null uc4, null uc5, null uc6, null uc7
FROM LEV1, LEV2, LEV3
WHERE LEV1.book_id = LEV2.parent_id
AND LEV2.book_id = LEV3.parent_id
…(中间3个表省略)
UNION ALL
SELECT LEV7.*, LEV1.unitcode uc1, LEV2.userid uc2, LEV3.userid uc3, LEV4.userid uc4, LEV5.userid uc5, LEV6.userid uc6, LEV7.userid uc7
FROM LEV1, LEV2, LEV3, LEV4, LEV5, LEV6, LEV7
WHERE LEV1.book_id = LEV2.parent_id
AND LEV2.book_id = LEV3.parent_id
AND LEV3.book_id = LEV4.parent_id
AND LEV4.book_id = LEV5.parent_id
AND LEV5.book_id = LEV6.parent_id
AND LEV6.book_id = LEV7.parent_id
);
将它做成游标,一个循环就好了,执行一下,3秒!搞掂。
总结:
WITH的强大之处,可以写多个中间表语句结构更清晰更容易阅读,层层筛选有点类似于管道概念。
特别对于层级关系的自关联查询(如本例)尤其适合。
还有一大好处是中间表只存在于内存中,完全无害(中间表名与其他表名不发生冲突,比Global Temporary 好)。
写WITH句的几个原则:
原则1:优先过滤掉不要的数据。
原则2:一次生成一个单纯的表,不用过于担心表的数量和代码的行数,而要在乎是否能方便关联。
原则3:如果可能尽量只在一个SQL中完成。
后记:后来原版的较慢的版本又通过正确的使用函数索引,性能得到大幅提高,但也未能超过这个版本。
相关推荐
详细介绍oracle数据库中新出的with_as语法以及相关使用
oracle数据库startwith用法
Reading this book and using SQL helps you learn to tune even the most complex SQL, and you'll learn to do it quickly, without the huge learning curve usually associated with tuning as a whole. ...
with tt as ( select '0' as pid, '1' as id, '1' as name from dual union all select '1' as pid, '2' as id, '2' as name from dual union all select '1' as pid, '3' as id, '3' as name from dual union...
对于需重复查询的sql语句,可巧用with as 对sql语句进行精简,提高查询效率
Oracle SQL Tuning with Oracle SQLTXPLAIN,Oracle SQL Tuning with Oracle SQLTXPLAIN,Oracle SQL Tuning with Oracle SQLTXPLAIN
oracle9i_As_操作手册,oracle9i_As_操作手册第一部分 oracle9i_As_操作手册,oracle9i_As_操作手册第一部分
Oracle9i Application Server (9iAS) with Oracle E-Business Suite Release 11i Troubleshooting (Doc ID 216208.1)
Redhat AS3安装Oracle9i Redhat AS3安装Oracle9i
Oracle SQL Tuning with SQLTXPLAIN is a practical guide to SQL tuning the way Oracle's own experts do it, using a freely downloadable tool called SQLTXPLAIN. Using this simple tool you'll learn how to ...
Oracle SQL Tuning with Oracle SQLTXPLAIN.pdf
ORACLE 视图的 with check option
Use machine learning and Oracle Business Intelligence Enterprise Edition (OBIEE) as a comprehensive BI solution. This book follows a when-to, why-to, and how-to approach to explain the key steps ...
Oracle Advanced Programming With Packages pdf文档
oracle 10g as 安装步骤part02
oracle ebs r12 redhat as 4 安装.doc
Oracle_9i_AS_Portal_宝典1.pdf
Oracle Process Manufacturing Oracle Order Management with Process Inventory API User’s Guide Release 11i Part No. B10662-01
系统讲解oracle数据库中PLSQL的使用,存储过程的编写