Oracle多表的複雜查詢

一．分頁查詢：

1.      根據rowid來分
  select * from t_xiaoxi where rowid in (select rid from (select rownum rn, rid from(select rowid rid, cid from t_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;
執行時間0.03秒

2.      按分析函數來分
  select * from (select t.*, row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;
執行時間1.01秒

3.      按rownum來分
  select * from (select t.*,rownum rn from(select * from t_xiaoxi order by cid desc)t where rownum<10000) where rn>9980;

執行時間0.1秒

按rownum來分
a. rownum 分頁
  SELECT * FROM emp;   
b. 顯示rownum[Oracle分配的]
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e;
 rn相當於Oracle分配的行的ID號
c.挑選出6—10條記錄
先查出1-10條記錄
SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10;
如果後面加上rownum>=6是不行的，
然後查出6-10條記錄
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT * FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6;
*指定查詢列，只需要修改最裡層的子查詢

SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp) e WHERE ROWNUM <= 10) WHERE rn >= 6;

*排序查詢，只需要修改最裡層的子查詢
工資排序後查詢6-10條資料
SELECT * FROM (SELECT e.*, ROWNUM rn FROM (SELECT ename, sal FROM emp ORDER by sal) e WHERE ROWNUM <= 10) WHERE rn >= 6;

二．用查詢結果建立新表
CREATE TABLE mytable (id, name, sal, job, deptno) as SELECT empno, ename, sal, job, deptno FROM emp;

三．合并查詢

實際應用中，有時為了合并多個select語句的結果，可以使用集合操作符號union，union all，intersect，minus
多用於資料量比較大的資料局庫，運行速度快。
1). union
該操作符用於取得兩個結果集的並集。當使用該操作符時，會自動去掉結果集中重複行。
SELECT ename, sal, job FROM emp WHERE sal >2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
2).union all
該操作符與union相似，但是它不會取消重複行，而且不會排序。
SELECT ename, sal, job FROM emp WHERE sal >2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
該操作符用於取得兩個結果集的並集。當使用該操作符時，會自動去掉結果集中重複行。
3). intersect
  使用該操作符用於取得兩個結果集的交集。
SELECT ename, sal, job FROM emp WHERE sal >2500
INTERSECT
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
4). minus
使用改操作符用於取得兩個結果集的差集，他只會顯示存在第一個集合中，而不存在第二個集合中的資料。
SELECT ename, sal, job FROM emp WHERE sal >2500
MINUS
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
（MINUS就是減法的意思）

四．左右串連
  1、內串連(自然串連): 只有兩個表相匹配的行才能在結果集中出現
  2、外串連: 包括
 （1）左外串連 (左邊的表不加限制) select a.studentno, a.studentname, b.classname
  from students a, classes b where a.classid = b.classid(+);
 （2）右外串連(右邊的表不加限制) select a.studentno, a.studentname, b.classname
  from students a, classes b where a.classid(+) = b.classid
 （3）全外串連(左右兩表都不加限制)

總之,
左串連顯示左邊全部的和右邊與左邊相同的
右串連顯示右邊全部的和左邊與右邊相同的
內串連是只顯示滿足條件的!
補充：
使用（+）的注意事項：
1.（+）操作符只能出現在where子句中，並且不能與outer join文法同時使用。
2.當使用（+）操作符執行外串連時，如果在where子句中包含有多個條件，則必須在所有條件中都包含（+）操作符。
3.（+）操作符不能與or和in操作符一起使用。
4.（+）操作符只能用於實現左外串連和右外串連，而不能用於實現完全外串連。

Oracle 並行查詢

Oracle使用者資訊查詢動作陳述式

Oracle單表查詢某列最大最小值的效能問題

資源回收筒引發Oracle查詢資料表空間使用緩慢