Sql優(yōu)化是一項復(fù)雜的工作，以下的一些基本原則是本人看書時所記錄下來的，很明確且沒什么廢話：

1．  索引的使用：

（1）.當(dāng)插入的數(shù)據(jù)為數(shù)據(jù)表中的記錄數(shù)量的10%以上，首先需要刪除該表的索引來提高數(shù)據(jù)的插入效率，當(dāng)數(shù)據(jù)插入后，再建立索引。

（2）.避免在索引列上使用函數(shù)或計算，在where子句中，如果索引是函數(shù)的一部分，優(yōu)化器將不再使用索引而使用全表掃描。如：

低效：select * from dept where sal*12 >2500;

高效：select * from dept where sal>2500/12;

(3).避免在索引列上使用not和 “!=”，索引只能告訴什么存在于表中，而不能告訴什么不存在于表中，當(dāng)數(shù)據(jù)庫遇到not 和 “!=”時，就會停止使用索引而去執(zhí)行全表掃描。

(4).索引列上>=代替>

 低效：select * from emp where deptno > 3

 高效：select * from emp where deptno >=4

兩者的區(qū)別在于，前者dbms將直接跳到第一個deptno等于4的記錄，而后者將首先定位到deptno等于3的記錄并且向前掃描到第一個deptno大于3的。

(5).非要對一個使用函數(shù)的列啟用索引，基于函數(shù)的索引是一個較好的方案。

2. 游標(biāo)的使用：

   當(dāng)在海量的數(shù)據(jù)表中進(jìn)行數(shù)據(jù)的刪除、更新、插入操作時，用游標(biāo)處理的效率是最慢的，但是游標(biāo)又是必不可少的，所以正確使用游標(biāo)十分重要：

   (1). 在數(shù)據(jù)抽取的源表中使用時間戳，這樣每天的維表數(shù)據(jù)維護(hù)只針對更新日期為最新時間的數(shù)據(jù)來進(jìn)行，大大減少需要維護(hù)的數(shù)據(jù)記錄數(shù)。

   (2). 在insert和update維表時都加上一個條件來過濾維表中已經(jīng)存在的記錄，例如：

insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)

 ods_customer為數(shù)據(jù)源表。dim_customer為維表。

   (3). 使用顯式的游標(biāo)，因為隱式的游標(biāo)將會執(zhí)行兩次操作，第一次檢索記錄，第二次檢查too many rows這個exception,而顯式游標(biāo)不執(zhí)行第二次操作。

3．  據(jù)抽取和上載時的sql優(yōu)化：

(1). Where 子句中的連接順序：

oracle采用自下而上的順序解析where子句，根據(jù)這個原理，表之間的連接必須寫在其他where條件之前，那些可以過濾掉大量記錄的條件必須寫在where子句的末尾。如：

低效：select * from emp e where sal>5000 and job = ‘manager’ and 25<(select count (*) from emp where mgr=e.empno);

高效：select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job=’manager’;

   (2). 刪除全表時，用truncate 替代 delete,同時注意truncate只能在刪除全表時適用，因為truncate是ddl而不是dml。

   (3). 盡量多使用commit

只要有可能就在程序中對每個delete,insert,update操作盡量多使用commit,這樣系統(tǒng)性能會因為commit所釋放的資源而大大提高。

   (4). 用exists替代in ，可以提高查詢的效率。

   (5). 用not exists 替代 not in

   (6). 優(yōu)化group by

提高group by語句的效率，可以將不需要的記錄在group by之前過濾掉。如：

低效：select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;

高效： select job, avg(sal) from emp having  job=’president’ or job=’manager’ group by job;

   (7). 有條件的使用union-all 替代 union：這樣做排序就不必要了，效率會提高3到5倍。

   (8). 分離表和索引

       總是將你的表和索引建立在不同的表空間內(nèi)，決不要將不屬于oracle內(nèi)部系統(tǒng)的對象存放到system表空間內(nèi)。同時確保數(shù)據(jù)表空間和索引表空間置于不同的硬盤控制卡控制的硬盤上。

/*
建表：
dept:
 deptno(primary key),dname,loc
emp:
 empno(primary key),ename,job,mgr,sal,deptno
*/

1 列出emp表中各部門的部門號，最高工資，最低工資
select max(sal) as 最高工資,min(sal) as 最低工資,deptno from emp group by deptno;

2 列出emp表中各部門job為'CLERK'的員工的最低工資，最高工資
select max(sal) as 最高工資,min(sal) as 最低工資,deptno as 部門號 from emp where job = 'CLERK' group by deptno;

3 對于emp中最低工資小于1000的部門，列出job為'CLERK'的員工的部門號，最低工資，最高工資
select max(sal) as 最高工資,min(sal) as 最低工資,deptno as 部門號 from emp as b
where job='CLERK' and 1000>(select min(sal) from emp as a where a.deptno=b.deptno) group by b.deptno

4 根據(jù)部門號由高而低，工資有低而高列出每個員工的姓名，部門號，工資
select deptno as 部門號,ename as 姓名,sal as 工資 from emp order by deptno desc,sal asc

5 寫出對上題的另一解決方法
（請補(bǔ)充）

6 列出'張三'所在部門中每個員工的姓名與部門號
select ename,deptno from emp where deptno = (select deptno from emp where ename = '張三')

7 列出每個員工的姓名，工作，部門號，部門名
select ename,job,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno

8 列出emp中工作為'CLERK'的員工的姓名，工作，部門號，部門名
select ename,job,dept.deptno,dname from emp,dept where dept.deptno=emp.deptno and job='CLERK'

9 對于emp中有管理者的員工，列出姓名，管理者姓名（管理者外鍵為mgr）
select a.ename as 姓名,b.ename as 管理者 from emp as a,emp as b where a.mgr is not null and a.mgr=b.empno

10 對于dept表中，列出所有部門名，部門號，同時列出各部門工作為'CLERK'的員工名與工作
select dname as 部門名,dept.deptno as 部門號,ename as 員工名,job as 工作 from dept,emp
where dept.deptno *= emp.deptno and job = 'CLERK'

11 對于工資高于本部門平均水平的員工，列出部門號，姓名，工資，按部門號排序
select a.deptno as 部門號,a.ename as 姓名,a.sal as 工資 from emp as a
where a.sal>(select avg(sal) from emp as b where a.deptno=b.deptno) order by a.deptno

12 對于emp，列出各個部門中平均工資高于本部門平均水平的員工數(shù)和部門號，按部門號排序
select count(a.sal) as 員工數(shù),a.deptno as 部門號 from emp as a
where a.sal>(select avg(sal) from emp as b where a.deptno=b.deptno) group by a.deptno order by a.deptno

13 對于emp中工資高于本部門平均水平，人數(shù)多與1人的，列出部門號，人數(shù)，按部門號排序
select count(a.empno) as 員工數(shù),a.deptno as 部門號,avg(sal) as 平均工資 from emp as a
where (select count(c.empno) from emp as c where c.deptno=a.deptno and c.sal>(select avg(sal) from emp as b where c.deptno=b.deptno))>1
group by a.deptno order by a.deptno

14 對于emp中低于自己工資至少5人的員工，列出其部門號，姓名，工資，以及工資少于自己的人數(shù)
select a.deptno,a.ename,a.sal,(select count(b.ename) from emp as b where b.sal<a.sal) as 人數(shù) from emp as a
where (select count(b.ename) from emp as b where b.sal<a.sal)>5

轉(zhuǎn)自：http://blog.csdn.net/woolceo/archive/2006/03/02/614094.aspx

"ERLLL"

10

Returns: 7

"EHHEEHLLLLRRRRRR"

2

Returns: 2

"ELLLELLRRRRLRLRLLLRLLLRLLLLRLLRRRL"

10

Returns: 3

"RRLEERLLLLRLLRLRRRLRLRLRLRLLLLL"

19

Returns: 12

This problem statement is the exclusive and proprietary property of TopCoder, Inc. Any unauthorized use or reproduction of this information without the prior written consent of TopCoder, Inc. is strictly prohibited. (c)2003, TopCoder, Inc. All rights reserved.

答案：

 1
 2public class CursorPosition {
 3    public int getPosition(String keystrokes, int N){
 4        int position = 0;
 5        String s = "";
 6        for(int i = 0; i < keystrokes.length(); i++){
 7            s = keystrokes.substring(i, i+1);
 8            if("L".equals(s)){
 9                if(position == 0) continue;
10                position--;
11            }
12            if("R".equals(s)){
13                if(position == N) continue;
14                position++;
15            }
16            if("H".equals(s)){
17                position = 0;
18            }
19            if("E".equals(s)){
20                position = N;
21            }
22
23        }
24
25        return position;
26
27    }
28    /** *//**
29     * @param args
30     */
31    public static void main(String[] args) {
32        CursorPosition cursorPosition = new CursorPosition();
33        int cursor = cursorPosition.getPosition("ERLLL", 10);
34        System.out.println("cursor:" + cursor);
35    }
36
37}
38

 4 3 5
 2 4 5
 0 1 9

"435245019"

Returns: {"435", "245", "019" }

"9"

Returns: {"9" }

"0123456789"

Returns: { }

"3357002966366183191503444273807479559869883303524"

Returns: {"3357002", "9663661", "8319150", "3444273", "8074795", "5986988", "3303524" }

This problem statement is the exclusive and proprietary property of TopCoder, Inc. Any unauthorized use or reproduction of this information without the prior written consent of TopCoder, Inc. is strictly prohibited. (c)2003, TopCoder, Inc. All rights reserved.

答案：

 1public class MatrixTool {
 2
 3    public String[] convert(String s){
 4        if (s == null || s.length() == 0 || s.length() > 50){
 5            return new String[]{};
 6        }
 7        int length = s.length();
 8        int n = (int)Math.sqrt(length);
 9        if(n*n == length){
10            String[] result = new String[n];
11            for(int i = 0; i < n; i++){
12                result[i] = s.substring(i*n, i*n + n);
13            }
14            return result;
15        }else {
16            return new String[]{};
17        }
18    }
19
20    /** *//**
21     * @param args
22     */
23    public static void main(String[] args) {
24        MatrixTool matrix = new MatrixTool();
25        String[] result = matrix.convert("3357002966366183191503444273807479559869883303524");
26        for(int i = 0; i < result.length; i++){
27            System.out.println(result[i]);
28        }
29    }
30
31}
32