一、分頁語句優化思路
正确的分頁架構：
SELECT *
FROM (SELECT *
FROM (SELECT A.*, ROWNUM AS RN
FROM (需要分頁的SQL) A)
WHERE ROWNUM <= 10)
WHERE RN >= 1;
分頁語句的優化思路：如果分頁語句中有排序（order by ），要利用索引已排序的特征，将order by 的列包含在索引中，同時也要利用rownum的COUNT STOPKEY特性來優化分頁SQL。如果分頁中沒有排序，可以直接利用rownum的COUNT STOPKE特性來優化分頁SQL。
執行個體1：
SELECT *
FROM (SELECT A.*, ROWNUM AS RN
FROM (select * from t_page order by object_id) A） WHERE ROWNUM<=10)
WHERE RN >= 1
該SQL沒有過濾條件，隻有排序，可以利用索引已經排序這個特性來優化分頁語句，也就是說要将分頁語句中的SORT ORDER BY消除。
對排序列建立索引，在索引中添加一個常量0，注意0不能放在前面。為什麼要再索引中添加一個常量0呢？因為object_id允許為空，如果不加常量（不一定是0，可以是1、2、3，也可以是英文字母），索引中就不能存儲空值，然而SQL中并沒有寫 where object_id is not null。
建立索引：
如果SQL有過濾條件是等值過濾，當然也有order by 。
SELECT * FROM T_PAGE WHERE OWNER='SYS' ORDER BY OBJECT_ID。
這就需要将過濾列（SYS）包含在索引中，排序列是object_id。
建立索引：
如果有如下分頁語句：
SELECT *
FROM (SELECT *
FROM (SELECT A.*, ROWNUM AS RN
FROM (select *
from t_page
order by object_id, object_name desc) A)
WHERE ROWNUM <= 10)
WHERE RN >= 1
那麼建立索引的時候，隻能是object_id列在前，object_name列在後面，另外object_name是降序顯示的，那麼在建立索引的時候，還要指定object_name列降序排序。
建立索引：
如果建立索引的時候将object_name放在前面，object_id放在後面，這時，索引中列先後順序與分頁語句中排序先後順序不一緻，強制走索引的時候，執行計劃中會出現SORT ORDER BY關鍵字。如果建立索引的時候沒有指定object_name列降序排序，那麼執行計劃中也會出現SORT ORDER BY。因為索引中排序合分頁語句中排序不一緻。
如果有如下分頁語句：
SELECT *
FROM (SELECT *
FROM (SELECT A.*, ROWNUM AS RN
FROM (select *
from t_page
where owner = 'SYS'
and object_id > 10000
order by object_name) A)
WHERE ROWNUM <= 10)
WHERE RN >= 1
該SQL過濾條件有等值條件，也有非等值條件，當然也有order by 。應該怎麼建立索引，進而優化上面的分頁語句？因為owner是等值過濾，object_id是非等值過濾，建立索引的時候，優先将等值過濾列和排序組合在一起，然後将非等值過濾列放到後面。
create index idx_page_ownerid on t_page(object,object_name desc,object_id)。
總結：
1、分頁語句 看到執行計劃有走全表掃描 、走sort order by …就是錯誤的
2、分頁語句沒有 order by這種業務邏輯就是錯的。分頁語句裡面必須有 order by 某個列。
3、特殊情況下沒有 order by 走全表掃描是對的
4、分頁語句如果有 order by 必須走索引 ，而且索引必須是包含了 order by 這個列的索引，隻要分頁有sort order by就是錯的，有sort order by絕對沒有走索引。分頁語句的優化始終要怎麼考慮？？？ 答：一定要讓ORDER BY的列有索引。
5、order by 這個列如果出現在 where條件裡面，在建立索引的時候是不是引導列無所謂。并不需要特别注意順序。不在where條件中的時候必須是引導列。
6、分頁語句走索引必須包含什麼？ 必須包含order by 列，另外分頁語句最好不要有回表再過濾，所有最好建組合索引，加where列加進去
7、如果 order by裡面的列沒有出現在where列裡面，也就是說order by 列的值可能為空值，也
不會走索引，是以最好改SQL，在where列上加一個order by 列的值is not null，或者使用
alter table page modify （order by 列） not null;來解決。
8、可能是錯的。但實用。落總總結：分頁語句 建立索引，不要管選擇性了。 以ORDER BY
的列為引導列，其他的過濾條件的列，按照選擇性，依次放後面。選擇性太低的列就不要放索引裡了。
9、遇到分頁語句中有order by desc的，寫HINT一定要寫
提
10、是不是會把資料都掃描完最終才來distinct，是以會走全表掃描。那分頁搞毛啊，你選第一頁、二頁其他都TM沒差別啊。是以分頁語句裡面同樣不能有group by
分頁語句裡面能否有union? 也不能，有union會排序 union all也是不可以的
總結下來：分頁語句裡不能用distinct，group by，union ，union all
11、select … from a,b where a.id=b.id order by a.xx;
兩個表關聯a,b。取第一頁可能就十到二十條資料。
像這種傳回資料非常少的是走NL對吧？
而order by a傳回的資料是不是根據a表來排序，那根據a來排序，也就是取完a的資料再去驅動b。分頁語句裡面order by誰，誰就是驅動表
二、DBLINK優化
現在有兩個表，a表是遠端表（1800萬），b表是本地表（100行）。
SQL：select * from a@dblink,b where a.id =b.id
1、優化方案（一）
預設情況下會将遠端表a的資料傳輸到本地，然後再進行關聯，遠端表b很大，對資料進行傳輸會耗費大量時間，本地表b表很小，而且a和b關聯之後傳回資料量很少，可以将本地表b傳輸到遠端，在遠端進行關聯，然後再将結果集傳回本地，這時需要使用hint:driving_site。
select /*+driving_site(a)*/ * from a@dblink,b where a.id =b.id
2、優化方案（二）
現在在遠端表a的連接配接列建立索引idx_id。因為b表隻有100行資料，a表有1800萬行資料，兩表關聯傳回2.5萬行資料，現在可以讓a表與b表走嵌套循環，b表作為驅動表，a表作為被驅動表，而且走連接配接索引。
select /*+index(a) leading(b) use_nl(a,b)*/ * from a@dblink,b where a.id =b.id
強制a表走索引之後，隻需将索引過濾之後的資料傳輸到本地，性能得到極大的提升。
3、優化方案（三）
如果遠端表a很大，本地表b也很大，兩表關聯傳回資料量多，這既不能将遠端表a傳到本地，也不能将本地表b傳輸到遠端，，是以無論采用哪種方式，SQL都很慢。可以在本地建立一個帶有DBLINK的物化視圖，将遠端的資料刷到本地。如果SQL語句中有多個DBLINK源，最好在本地針對每個DBLINK源建立帶有DBLINK的物化視圖，因為多個DBLINK之間進行資料傳輸，網絡資訊交換會導緻嚴重性能問題。
4、DBLINK永遠不能作為NL的被驅動表
三、超大表與超小表關聯優化方法
SELECT * FROM A, B WHERE A.OBJECT_ID = B.OBJECT_ID
表a有30MB，b有30GB，兩表關聯後傳回大量資料，應該走HASH連接配接，因為a是小表所有a應該作為HASH JOIN的驅動表，大表b作為HASH JOIN的被驅動表。在進行HASH的時候，驅動表會被放在PGA中，因為驅動表a隻有30MB，PGA能夠完全容納下驅動表。因為被驅動表b特别大，想要加快SQL查詢速度，必須開啟并行查詢。超大表與超小表在進行并行HASH連接配接的時候，可以将小表（驅動表）廣播到所有的查詢程序，然後對大表進行并行随機掃描，每個查詢程序查詢部分b表資料，然後再進行關聯。假設對以上SQL啟用6個并行程序對a表的并行廣播，對b表進行随機并行掃描。怎麼讓a表進行廣播？需要添加hint：pq_distribute(驅動表 none, broadcast)。具體查詢語句如下：
SELECT /*+parallel(6) use_hash(a,b) pq_distribute(a none,broadcast)*/
* FROM A, B
WHERE A.OBJECT_ID = B.OBJECT_ID
注意：如果是兩個大表關聯，千萬不能讓大表廣播。
四、對表進行ROWID切片
對于一個很大的非分區表進行update、delete，如果隻在一個會話裡面運作，很容易引發UNDO不夠，如果會話連接配接中斷，會導緻大量資料從UNDO復原，這将是一場災難。
對于非分區表，可以對表按照ROWID切片，然後開啟多個視窗同時執行SQL，這樣既能加快執行速度，還能減少對HUNDO的占用。
Oracle提供了一個内置函數DBMS_ROWID.ROWID_CREATE()用于生成ROWID。對于一個非分區表，一個表就是一個段（segment）,段是由多個區組成，每個區裡面的塊實體是是連續的。于是，可以根據資料字典DBA_EXTENTS，DBA_OBJECTS關聯，然後再利用生成ROWID的内置函數人工生成ROWID。
查詢SQL腳本：
SELECT DBMS_ROWID.ROWID_CREATE(1,
B.DATA_OBJECT_ID,
A.RELATIVE_FNO,
A.BLOCK_ID,
0),
DBMS_ROWID.ROWID_CREATE(1,
B.DATA_OBJECT_ID,
A.RELATIVE_FNO,
A.BLOCK_ID + A.BLOCKS - 1,
999)
FROM DBA_EXTENTS A
INNER JOIN DBA_OBJECTS B
ON A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.OBJECT_NAME
WHERE B.OWNER = 'SCOTT'
AND B.OBJECT_NAME = 'T_PAGE'
假如要執行DELETE FROM T_PAGE WHERE OBJECT_ID>50000000，T_PAGE表有1億條資料，要删除其中5000萬行資料，可以根據上述辦法對表按照ROWID切片删除。
DELETE FROM T_PAGE
WHERE OBJECT_ID > 5000000
AND ROWID BETWEEN 'AABE3nAAEAAAACoAAA' AND 'AABE3nAAEAAAACvAPn';
DELETE FROM T_PAGE
WHERE OBJECT_ID > 5000000
AND ROWID BETWEEN 'AABE3nAAEAAAACwAAA' AND 'AABE3nAAEAAAAC3APn';
…
上述方法需要手動編輯大量SQL腳本，如果表的EXTENT很多，這将會帶來很大工作量，是以可以通過存儲過程來實作。
CREATE OR REPLACE PROCEDURE P_DEL_ROWID(P_RANGE NUMBER, P_ID NUMBER) AS
CURSOR CUR_ROWID IS
SELECT DBMS_ROWID.ROWID_CREATE(1,
B.DATA_OBJECT_ID,
A.RELATIVE_FNO,
A.BLOCK_ID,
0) AS ROWID1,
DBMS_ROWID.ROWID_CREATE(1,
B.DATA_OBJECT_ID,
A.RELATIVE_FNO,
A.BLOCK_ID + A.BLOCKS - 1,
999) AS ROWID2
FROM DBA_EXTENTS A
INNER JOIN DBA_OBJECTS B
ON A.OWNER = B.OWNER
AND A.SEGMENT_NAME = B.OBJECT_NAME
WHERE B.OWNER = 'SCOTT'
AND B.OBJECT_NAME = 'T_PAGE'
AND MOD(A.EXTENT_ID, P_RANGE) = P_ID;
V_SQL VARCHAR2(4000);
BEGIN
FOR CUR IN CUR_ROWID LOOP
V_SQL := 'DELETE FROM T_PAGE WHERE OBJECT_ID >100 AND ROWID BETWEEN :1 AND :2';
EXECUTE IMMEDIATE V_SQL
USING CUR.ROWID1, CUR.ROWID2;
COMMIT;
END LOOP;
CLOSE CUR_ROWID;
END;
五、固化查詢/+MATERIALIZE/
通過對查詢語句采用 WITH temp AS select /+MATERIALIZE/ * from 子查詢固化查詢語句，不讓查詢語句展開，如果該查詢語句傳回資料量比較少，該子查詢可作為主表，進行嵌套循環連接配接。
六、外連接配接有OR關聯條件隻能走NL
SELECT E.*, D.DEPTNO AS DEPTNO2, D.LOC
FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
AND (D.DEPTNO >= E.SAL AND E.SAL < 1000 OR E.ENAME LIKE '%O')
從執行計劃中看到，兩表走的是嵌套循環。當兩表用外連接配接關聯，關聯條件中有OR關聯條件，那麼這時隻能走嵌套循環，而且驅動表固定為主表，此時不能走HASH連接配接，即使通過HINT：USE_HASH要無法修改執行計劃。如果主表資料量很大，那麼這時就會出現嚴重性能問題。可以将外連接配接的OR關聯/過濾條件放到查詢中，用CASE WHEN進行過濾，進而讓SQL可以走HASH連接配接。
SELECT E.*,
(case
when (D.DEPTNO >= E.SAL AND E.SAL < 1000 OR E.ENAME LIKE '%O') then
D.DEPTNO
end) AS DEPTNO2,
(case
when (D.DEPTNO >= E.SAL AND E.SAL < 1000 OR E.ENAME LIKE '%O') then
D.LOC
end) as LOC
FROM EMP E
LEFT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
利用case when改寫外連接配接OR連接配接條件有個限制：從表（被驅動表）隻能是1的關系，不能是N的關系，從表要展示多少個列，就要寫多少個case when。
如果主表屬于1的關系，從表屬于n的關系，就不能用case when進行等價改寫。