Oracle高级查询实例，提升效率

来源：互联网发布：济宁市高新区网络问政编辑：程序博客网时间：2024/05/29 13:57

使用Oracle特有的查询语法,可以达到事半功倍的效果。
如下：

1. 树查询

create table tree (
     id number(10) not null primary key,
     name varchar2(100) not null,
     super number(10) notnull               // 0 is root
);
-- 从子到父
select * from tree start with id = ? connect by id = prior super
-- 从父到子
select * from tree start with id = ? connect by prior id = suepr
-- 整棵树
select * from tree start with super = 0 connect by prior id = suepr

2. 分页查询

select * from (
    select my_table.*, rownum   my_rownum from (
        select name, birthday from employeeorder by birthday
     ) my_table where rownum < 120
) where my_rownum >= 100;

3. 累加查询, 以scott.emp为例

select empno, ename, sal, sum(sal) over(order by empno) resultfrom emp;
      EMPNOENAME             SAL      RESULT
---------- ---------- ---------- ----------
      7369SMITH            800        800
      7499ALLEN           1600       2400
      7521WARD            1250       3650
      7566JONES            2975      6625
      7654MARTIN          1250       7875
      7698BLAKE           2850      10725
      7782CLARK           2450      13175
      7788SCOTT           3000      16175
      7839KING            5000      21175
      7844 TURNER          1500      22675
      7876ADAMS           1100      23775
      7900JAMES            950      24725
      7902FORD            3000      27725
      7934MILLER          1300      29025

4. 高级group by

select decode(grouping(deptno),1,'all deptno',deptno) deptno,
        decode(grouping(job),1,'alljob',job) job,
       sum(sal) sal
from emp
group by ROLLUP(deptno,job);
DEPTNO                                   JOB              SAL
---------------------------------------- --------- ----------
10                                       CLERK           1300
10                                       MANAGER         2450
10                                       PRESIDENT       5000
10                                      all job         8750
20                                       CLERK           1900
20                                       ANALYST         6000
20                                       MANAGER         2975
20                                      all job        10875
30                                       CLERK            950
30                                       MANAGER         2850
30                                       SALESMAN        5600
30                                      all job         9400
all deptno                              all job        29025

5. use hint

当多表连接很慢时,用ORDERED提示试试,也许会快很多
SELECT /**//*+ ORDERED */*
FROM a, b, c, d
WHERE

阅读全文

0 0