Oracle高級查詢實例,提升效率

使用Oracle特有的查詢語法, 可以達到事半功倍的效果。

如下：

1. 樹查詢

create table tree (
     id number(10) not null primary key,
     name varchar2(100) not null,-
 

     super number(10) not null                // 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 employee order by birthday
     ) my_table where rownum < 120
) where my_rownum >= 100;

3. 累加查詢, 以scott.emp為例

select empno, ename, sal, sum(sal) over(order by empno) result from emp;

      EMPNO ENAME              SAL      RESULT
---------- ---------- ---------- ----------
      7369 SMITH             800        800
      7499 ALLEN            1600       2400
      7521 WARD             1250       3650
      7566 JONES            2975       6625
      7654 MARTIN           1250       7875
      7698 BLAKE            2850      10725
      7782 CLARK            2450      13175
      7788 SCOTT            3000      16175
      7839 KING             5000      21175
      7844 TURNER           1500      22675
      7876 ADAMS            1100      23775
      7900 JAMES             950      24725
      7902 FORD             3000      27725
      7934 MILLER           1300      29025

4. 高級group by

select decode(grouping(deptno),1,'all deptno',deptno) deptno,
        decode(grouping(job),1,'all job',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

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]