數(shù)據(jù)庫方向

oracle

1.oracle分頁是如何實(shí)現(xiàn)的

? ? ?Oracle中的表，除了我們建表時設(shè)計(jì)的各個字段，其實(shí)還有兩個字段（此處只介紹2個），分別是ROWID（行標(biāo)示符）和ROWNUM（行號）畅蹂，即使我們使用DESCRIBE命令查看表的結(jié)構(gòu)，也是看不到這兩個列的描述的荣恐，因?yàn)橐盒保麄兤鋵?shí)是只在數(shù)據(jù)庫內(nèi)部使用的，所以也通常稱他們?yōu)閭瘟校╬seudo column）叠穆。

? ? 我們只要將行號查詢出來生成一個結(jié)果集少漆，然后再從這個結(jié)果集中，選擇行號大于我們設(shè)定的那個值就可以了硼被，分頁查找的寫法：

? ? 內(nèi)層控制需要查詢多少行的數(shù)據(jù)

? ? 外層控制從內(nèi)層查詢顯示到的數(shù)據(jù)最小行數(shù)

select *

from (select p.*, rownum rn

from (select ziduan from table_name) p

where rownum <= to_number('start') * to_number('count'))

where rn >= (to_number('start') - 1) * to_number('count') + 1

2. oracle索引在什么時候會失效

? ? Oracle 索引的目標(biāo)是避免全表掃描示损，提高查詢效率，但有些時候卻適得其反嚷硫。

? ? 例如一張表中有上百萬條數(shù)據(jù)检访，對某個字段加了索引，但是查詢時性能并沒有什么提高仔掸，這可能是oracle索引失效造成的脆贵。oracle索引有一些限制條件，如果你違反了這些索引限制條件起暮，那么即使你已經(jīng)加了索引卖氨，oracle還是會執(zhí)行一次全表掃描，查詢的性能不會比不加索引有所提高负懦，反而可能由于數(shù)據(jù)庫維護(hù)索引的系統(tǒng)開銷造成性能更差筒捺。 下面就是總結(jié)的能使Oracle索引失效的七大限制條件。

1. 沒有 WHERE 子句

2. 使用 IS NULL 和 IS NOT NULL

SELECT ... FROM emp WHERE comm IS NULL; comm 列的索引會失效

3. WHERE 子句中使用函數(shù)

如果沒有使用基于函數(shù)的索引纸厉，那么 where 子句中對存在索引的列使用函數(shù)時系吭，會使優(yōu)化器忽略掉這些索引。例如：

select * from staff where trunc(birthdate) = '01-MAY-82';

但是把函數(shù)應(yīng)用在條件上残腌，索引是可以生效的村斟，把上面的語句改成下面的語句贫导，就可以通過索引進(jìn)行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999);

注意：對于 MIN, MAX 函數(shù)蟆盹，Oracle 仍然使用索引孩灯。

4. 使用 LIKE ‘%T’ 進(jìn)行模糊查詢

5. WHERE 子句中使用不等于操作

不等于操作包括：<>, !=, NOT colum >= ?, NOT colum <= ?

對于這個限制條件可以通過 OR 替代，例如： colum <> 0 ===> colum>0 OR colum<0

6. 等于和范圍索引不會被合并使用

SELECT emp_id, emp_m, salary_q ... FROM emp WHERE job='manager' AND deptno>10

job 和 deptno 都是非唯一索引逾滥，這種條件下 oracle 不會合并索引峰档，它只會使用第一個索引。

7. 比較不匹配數(shù)據(jù)類型

dept_id是一個varchar2型的字段寨昙，在這個字段上有索引讥巡，但是下面的語句會執(zhí)行全表掃描。

select * from dept where dept_id = 900198;

這是因?yàn)?oracle 會自動把 where 子句轉(zhuǎn)換成 to_number(dept_id)=900198舔哪，相當(dāng)于使用函數(shù)欢顷，這樣就限制了索引的使用。正確寫法如下：

select * from dept where dept_id = '900198';

3. merge是什么

Merge的語法例如以下：

MERGE [hint] INTO [schema .] table [t_alias] USING [schema .]

{ table | view | subquery } [t_alias] ON ( condition )

WHEN MATCHED THEN merge_update_clause

WHEN NOT MATCHED THEN merge_insert_clause;

MERGE是什么捉蚤，怎樣使用呢抬驴？讓我們先看一個簡單的需求：

需求是，從T1表更新數(shù)據(jù)到T2表中缆巧。假設(shè)T2表的NAME 在T1表中已存在布持，就將MONEY累加，假設(shè)不存在陕悬。將T1表的記錄插入到T2表中题暖。

大家知道，在等價的情況下捉超，一定需要至少兩條語句胧卤，一條為UPDATE，一條為INSERT,并且語句中必需要與推斷的邏輯狂秦，或者寫在存儲過程中灌侣，假設(shè)是單條語句推捐，就要寫全條件裂问。

寫在UPDATE和INSERT的語句中，顯的比較麻煩并且易出錯牛柒。假設(shè)了解MERGE堪簿，我們能夠不借助存儲過程，直接用單條SQL便實(shí)現(xiàn)了該業(yè)務(wù)邏輯皮壁，且代碼非常簡潔椭更。詳細(xì)例子如下：

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY

WHEN NOT MATCHED THEN

INSERT VALUES (T1.NAME,T1.MONEY);

Merge的四大靈活之處

上面講了Merge的語法和基本使用方法，其實(shí)Merge能夠很靈活蛾魄。

1.UPDATE和INSERT動作可僅僅出現(xiàn)其一(9i必須同一時候出現(xiàn))

--我們可選擇只UPDATE目標(biāo)表

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY;

--也可選擇只INSERT目標(biāo)表而不做不論什么UPDATE動作

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN NOT MATCHED THEN

INSERT VALUES (T1.NAME,T1.MONEY);

2.可對MERGE語句加條件

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY

WHERE T1.NAME='A';

3.可用DELETE子句清除行

/*

在這樣的情況下虑瀑，首先是要先滿足T1.NAME=T2.NAME的記錄湿滓，假設(shè)T2.NAME='A'并不滿足T1.NAME=T2.NAME過濾出的記錄集，那這個DELETE是不會生效的舌狗。在滿足的條件下叽奥，能夠刪除目標(biāo)表的記錄。

*/

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY

DELETE WHERE (T2.NAME = 'A');

4.可采用無條件方式Insert

/*

方法非常easy痛侍，在語法ON keyword處寫上恒不等條件（如1=2）后朝氓，MATCHED語句的INSERT就變?yōu)闊o條件INSERT了，詳細(xì)例如以下

*/

MERGE INTO T2

USING T1

ON (1=2)

WHEN NOT MATCHED THEN

INSERT VALUES (T1.NAME,T1.MONEY);

Merge的誤區(qū)

1. 不能更新ON子句引用的列

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.NAME=T1.NAME;

ORA-38104: 無法更新 ON 子句中引用的列: "T2"."NAME"

2. DELETE子句的WHERE順序必須最后

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY

DELETE WHERE (T2.NAME = 'A')

WHERE T1.NAME='A';

ORA-00933: SQL 命令未正確結(jié)束

3.DELETE 子句僅僅能夠刪除目標(biāo)表主届。而無法刪除源表

/*

這里須要引起注意赵哲，不管DELETE WHERE (T2.NAME = 'A' )這個寫法的T2是否改寫為T1。效果都一樣君丁，都是對目標(biāo)表進(jìn)行刪除枫夺。

*/

SELECT * FROM T1;

NAME MONEY

-------------------- ----------

A 10

B 20

SELECT * FROM T2;

NAME MONEY

-------------------- ----------

A 30

C 20

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY

DELETE WHERE (T2.NAME = 'A' );

SELECT * FROM T1;

NAME MONEY

-------------------- ----------

A 10

B 20

SELECT * FROM T2;

NAME MONEY

-------------------- ----------

C 20

4.更新同一張表的數(shù)據(jù),需操心USING的空值

SELECT * FROM T2;

NAME MONEY

-------------------- ----------

A 30

C 20

/*

需求為對T2表進(jìn)行自我更新。假設(shè)在T2表中發(fā)現(xiàn)NAME=D的記錄绘闷，就將該記錄的MONEY字段更新為100筷屡，假設(shè)NAME=D的記錄不存在，

則自己主動添加簸喂。NAME=D而且MONEY=100的記錄毙死。依據(jù)語法完畢例如以下代碼:

*/

MERGE INTO T2

USING (select * from t2 where NAME='D') T

ON (T.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=100

WHEN NOT MATCHED THEN

INSERT VALUES ('D',200);

--可是查詢發(fā)現(xiàn)。本來T表應(yīng)該由于NAME=D不存在而要添加記錄喻鳄《筇龋可是實(shí)際卻根本無變化。

SQL> SELECT * FROM T2;

NAME MONEY

-------------------------------------------------------

A 30

C 20

/*

原來是由于此時select * from t2 where NAME='D'為NULL,所以出現(xiàn)了無法插入的情況除呵。

我們能夠利用COUNT(*)的值不會為空的特點(diǎn)來等價改造再菊。詳細(xì)例如以下:

*/

MERGE INTO T2

USING (select COUNT(*) CNT from t2 where NAME='D') T

ON (T.CNT<>0)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=100

WHEN NOT MATCHED THEN

INSERT VALUES ('D',100);

SQL> SELECT * FROM T2;

NAME MONEY

-------------------------------

A 30

C 20

D 100

5. 必需要在源表中獲得一組穩(wěn)定的行

---構(gòu)造數(shù)據(jù)，請注意這里多插入一條A記錄颜曾，就產(chǎn)生了ORA-30926錯誤

INSERT INTO T1 VALUES ('A',30);

COMMIT;

---此時繼續(xù)運(yùn)行例如以下

MERGE INTO T2

USING T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY;

ORA-30926: 無法在源表中獲得一組穩(wěn)定的行

/*

oracle中的merge語句應(yīng)該保證on中的條件的唯一性纠拔，T1.NAME=T2.NAME的時候。T1表記錄相應(yīng)到了T2表的兩條記錄泛豪，所以就出錯了稠诲。

解決方法非常easy。比方我們能夠?qū)1表和T2表的關(guān)聯(lián)字段建主鍵诡曙，這樣基本上就不可能出現(xiàn)這種問題臀叙，并且一般而言，MERGE語句的關(guān)聯(lián)字段互相有主鍵价卤。 MERGE的效率將比較高劝萤！或者是將T1表的ID列做一個聚合。這樣歸并成單條慎璧，也能避免此類錯誤床嫌。

*/

如：

MERGE INTO T2

USING (select NAME,SUM(MONEY) AS MONEY FROM T1 GROUP BY NAME)T1

ON (T1.NAME=T2.NAME)

WHEN MATCHED THEN

UPDATE SET T2.MONEY=T1.MONEY+T2.MONEY;

--正常情況下跨释，一般出現(xiàn)反復(fù)的NAME須要引起懷疑，不太應(yīng)該厌处。

4.存儲過程中l(wèi)oop是什么游標(biāo)

5.如何對oracle進(jìn)行調(diào)優(yōu)

一煤傍、語句方面。

1嘱蛋、oracle采用自下而上的解析順序蚯姆，因而能過濾掉最大記錄數(shù)量的條件應(yīng)放在where語句的末尾，如id=洒敏？龄恋。

2、避免使用select *凶伙。

二郭毕、索引。

1函荣、適當(dāng)?shù)挠盟饕匝海艽蟠筇岣邫z索效率。但索引需要空間存儲及定期維護(hù)傻挂，每當(dāng)有記錄在表中增減或索引列被修改時, 索引本身也會被修改乘碑。

2、索引需要注意金拒。避免在索引上使用not兽肤，使用計(jì)算，使用is null或is not null及通配符%绪抛，他們將促使oracle停用索引资铡。

3、如果檢索數(shù)量超過表中30%的記錄數(shù)幢码，則該索引沒有顯著的效率提高笤休。

三、其他注意事項(xiàng)症副。

1店雅、使用exists代替distinct。

2瓦糕、使用union all(union)替換or底洗。

3腋么、order by 語句加在索引列咕娄，最好是主鍵上。

6. where的執(zhí)行順序

語法分析和執(zhí)行順序從右到左珊擂，自下而上

要提高SQL查詢效率where語句條件的先后次序應(yīng)如何寫

（1）選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)：

Oracle的解析器按照從右到左的順序處理FROM子句中的表名圣勒，F(xiàn)ROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理费变，在FROM子句中包含多個表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個被其他表所引用的表圣贸。

（2）WHERE子句中的連接順序：

Oracle采用自下而上的順序解析WHERE子句,根據(jù)這個原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾挚歧。

（3）SELECT子句中避免使用‘*’：

Oracle在解析的過程中, 會將‘*’依次轉(zhuǎn)換成所有的列名, 這個工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時間。

（4）減少訪問數(shù)據(jù)庫的次數(shù)：

Oracle在內(nèi)部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等吁峻。

（5）在SQL*Plus , SQL*Forms和Pro*C中重新設(shè)置ARRAYSIZE參數(shù), 可以增加每次數(shù)據(jù)庫訪問的檢索數(shù)據(jù)量 ,建議值為200滑负。

（6）使用DECODE函數(shù)來減少處理時間：

使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表。

（7）整合簡單,無關(guān)聯(lián)的數(shù)據(jù)庫訪問：

如果你有幾個簡單的數(shù)據(jù)庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關(guān)系)用含。

（8）刪除重復(fù)記錄：

最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧薘OWID)例子：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

（9）用TRUNCATE替代DELETE：

當(dāng)刪除表中的記錄時,在通常情況下, 回滾段(rollback segments ) 用來存放可以被恢復(fù)的信息. 如果你沒有COMMIT事務(wù),ORACLE會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況) 而當(dāng)運(yùn)用TRUNCATE時, 回滾段不再存放任何可被恢復(fù)的信息矮慕。當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時間也會很短。（TRUNCATE只在刪除全表適用,TRUNCATE是DDL(操作表)不是DML(操作數(shù)據(jù))）啄骇。

（10）盡量多使用COMMIT：

? ? 只要有可能痴鳄，在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會因?yàn)镃OMMIT所釋放的資源而減少，COMMIT所釋放的資源:

? ? a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息缸夹。

? ? b. 被程序語句獲得的鎖痪寻。

? ? c. redo log buffer 中的空間。

? ? d. Oracle為管理上述3種資源中的內(nèi)部花費(fèi)虽惭。

（11）用Where子句替換HAVING子句：

? ? 避免使用HAVING子句橡类，HAVING 只會在檢索出所有記錄之后才對結(jié)果集進(jìn)行過濾。這個處理需要排序,總計(jì)等操作芽唇。如果能通過WHERE子句限制記錄的數(shù)目猫态，那就能減少這方面的開銷。(非 oracle中)on披摄、where亲雪、having這三個都可以加條件的子句中，on是最先執(zhí)行疚膊，where次之义辕，having最后，因?yàn)閛n是先把不符合 條件的記錄過濾后才進(jìn)行統(tǒng)計(jì)寓盗，它就可以減少中間運(yùn)算要處理的數(shù)據(jù)灌砖，按理說應(yīng)該速度是最快的，where也應(yīng)該比having快點(diǎn)的傀蚌，因?yàn)樗^濾數(shù)據(jù)后才進(jìn)行sum基显，在兩個表聯(lián)接時才用on的，所以在一個表的時候善炫，就剩下where跟having比較了撩幽。在這單表查詢統(tǒng)計(jì)的情況下，如果要過濾的條件沒有涉及到要計(jì)算字段，那它們的結(jié)果是一樣的窜醉，只是where可以使用rushmore技術(shù)宪萄，而having就不能，在速度上后者要慢如果要涉及到計(jì)算的字段榨惰，就表示在沒計(jì)算之前拜英，這個字段的值是不確定的，根據(jù)上篇寫的工作流程琅催，where的作用時間是在計(jì)算之前就完成的居凶，而having就是在計(jì)算后才起作用的， 所以在這種情況下藤抡，兩者的結(jié)果會不同排监。在多表聯(lián)接查詢時，on比where更早起作用杰捂。系統(tǒng)首先根據(jù)各個表之間的聯(lián)接條件舆床，把多個表合成一個臨時表后，再由where進(jìn)行過濾嫁佳，然后再計(jì)算挨队，計(jì)算完后再由having進(jìn)行過濾。由此可見蒿往，要想過濾條件起到正確的作用盛垦，首先要明白這個條件應(yīng)該在什么時候起作用，然后再決定放在那里瓤漏。

（12）減少對表的查詢：

? ? ?在含有子查詢的SQL語句中,要特別注意減少對表的查詢腾夯。例子：

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECTTAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

（13）通過內(nèi)部函數(shù)提高SQL效率：

? ? 復(fù)雜的SQL往往犧牲了執(zhí)行效率。能夠掌握上面的運(yùn)用函數(shù)解決問題的方法在實(shí)際工作中是非常有意義的蔬充。

（14）使用表的別名(Alias)：

? ? 當(dāng)在SQL語句中連接多個表時, 請使用表的別名并把別名前綴于每個Column上蝶俱。這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。

（15）用EXISTS替代IN饥漫、用NOT EXISTS替代NOT IN：

? ? 在許多基于基礎(chǔ)表的查詢中榨呆，為了滿足一個條件,往往需要對另一個表進(jìn)行聯(lián)接。在這種情況下庸队，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率积蜻。在子查詢中熄攘，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并迈着。無論在哪種情況下，NOT IN都是最低效的 (因?yàn)樗鼘ψ硬樵冎械谋韴?zhí)行了一個全表遍歷)笨触。為了避免使用NOT IN 宾尚，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS丙笋。

例子：

(高效)SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND EXISTS (SELECT 1 FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

(低效)SELECT * FROM EMP (基礎(chǔ)表) WHERE EMPNO > 0 AND DEPT NO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

select 1 from table 增加臨時列，每行的列值是寫在select后的數(shù)，這條sql語句中是1

select count(1) from table 不管count(a)的a值如何變化不见，得出的值總是table表的行數(shù)

select sum(1) from table 計(jì)算臨時列的和（臨時列的值*行數(shù)）

（16）識別‘低效執(zhí)行’的SQL語句：

雖然目前各種關(guān)于SQL優(yōu)化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法：

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 ORDER BY 4 DESC;

索引是表的一個概念部分澳化，用來提高檢索數(shù)據(jù)的效率崔步，Oracle使用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu)稳吮。通常，通過索引查詢數(shù)據(jù)比全表掃描要快井濒。當(dāng)Oracle找出執(zhí)行查詢和Update語句的最佳路徑時, Oracle優(yōu)化器將使用索引灶似。同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率。另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗(yàn)證瑞你。那些LONG或LONG RAW數(shù)據(jù)類型, 你可以索引幾乎所有的列酪惭。通常, 在大型表中使用索引特別有效. 當(dāng)然,你也會發(fā)現(xiàn), 在掃描小表時，使用索引同樣能提高效率者甲。雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價春感。索引需要空間來存儲,也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時, 索引本身也會被修改。這意味著每條記錄的INSERT虏缸，DELETE 鲫懒， UPDATE將為此多付出4、5次的磁盤I/O 刽辙。因?yàn)樗饕枰~外的存儲空間和處理窥岩，那些不必要的索引反而會使查詢反應(yīng)時間變慢。 定期的重構(gòu)索引是有必要的（ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>）宰缤。

（18）用EXISTS替換DISTINCT：

當(dāng)提交一個包含一對多表信息(比如部門表和雇員表)的查詢時颂翼，避免在SELECT子句中使用DISTINCT。一般可以考慮用EXIST替換慨灭， EXISTS使查詢更為迅速朦乏，因?yàn)镽DBMS核心模塊將在子查詢的條件一旦滿足后，立刻返回結(jié)果氧骤。例子：

(低效): SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E WHERE D.DEPT_NO = E.DEPT_NO

(高效): SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT 1 FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

（19）SQL語句用大寫的集歇；因?yàn)镺racle總是先解析SQL語句，把小寫的字母轉(zhuǎn)換成大寫的再執(zhí)行语淘。

（20）在Java代碼中盡量少用連接符“＋”連接字符串诲宇。

（21）避免在索引列上使用NOT，通常我們要避免在索引列上使用NOT, NOT會產(chǎn)生在和在索引列上使用函數(shù)相同影響惶翻。當(dāng)Oracle“遇到”NOT,他就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描姑蓝。

（22）避免在索引列上使用計(jì)算。WHERE子句中吕粗，如果索引列是函數(shù)的一部分纺荧。優(yōu)化器將不使用索引而使用全表掃描。

低效: SELECT … FROM DEPT WHERE SAL * 12 > 25000;

高效: SELECT … FROM DEPT WHERE SAL > 25000/12;

（23）用>=替代>：

高效: SELECT * FROM EMP WHERE DEPTNO >=4

低效: SELECT * FROM EMP WHERE DEPTNO >3

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

（24）用UNION替換OR (適用于索引列)：

通常情況下输枯，用UNION替換WHERE子句中的OR將會起到較好的效果。對索引列使用OR將造成全表掃描占贫。注意桃熄，以上規(guī)則只針對多個索引列有效。如果有column沒有被索引型奥，查詢效率可能會因?yàn)槟銢]有選擇OR而降低瞳收。在下面的例子中，LOC_ID 和REGION上都建有索引厢汹。

高效：SELECT LOC_ID 螟深。 LOC_DESC ，REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID 烫葬，LOC_DESC 界弧，REGION FROM LOCATION WHERE REGION = “MELBOURNE”

低效: SELECT LOC_ID ，LOC_DESC 搭综，REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

（25）用IN來替換OR：

這是一條簡單易記的規(guī)則垢箕，但是實(shí)際的執(zhí)行效果還須檢驗(yàn)，在Oracle8i下设凹，兩者的執(zhí)行路徑似乎是相同的:

低效:

SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30

高效：

SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30);

（26）避免在索引列上使用IS NULL和IS NOT NULL：

避免在索引中使用任何可以為空的列舰讹，Oracle將無法使用該索引。對于單列索引闪朱，如果列包含空值月匣，索引中將不存在此記錄。對于復(fù)合索引奋姿，如果每個列都為空锄开，索引中同樣不存在此記錄。如果至少有一個列不為空称诗，則記錄存在于索引中萍悴。舉例：如果唯一性索引建立在表的A列和B列上，并且表中存在一條記錄的A寓免，B 值為(123癣诱，null)， Oracle將不接受下一條具有相同A袜香，B值（123,null）的記錄(插入)撕予。 然而如果所有的索引列都為空，Oracle將認(rèn)為整個鍵值為空而空不等于空蜈首。因此你可以插入1000條具有相同鍵值的記錄,當(dāng)然它們都是空! 因?yàn)榭罩挡淮嬖谟谒饕兄?所以WHERE子句中對索引列進(jìn)行空值比較將使ORACLE停用該索引实抡。

低效: (索引失效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

高效：(索引有效)

SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

（27）總是使用索引的第一個列：

如果索引是建立在多個列上欠母，只有在它的第一個列(leading column)被where子句引用時，優(yōu)化器才會選擇使用該索引吆寨。這也是一條簡單而重要的規(guī)則赏淌，當(dāng)僅引用索引的第二個列時，優(yōu)化器使用了全表掃描而忽略了索引啄清。

（28）用UNION-ALL替換UNION ( 如果有可能的話)：

當(dāng)SQL語句需要UNION兩個查詢結(jié)果集合時六水，這兩個結(jié)果集合會以UNION-ALL的方式被合并，然后在輸出最終結(jié)果前進(jìn)行排序盒延。如果用UNION ALL替代UNION缩擂，這樣排序就不是必要了鼠冕。效率就會因此得到提高添寺。需要注意的是，UNION ALL 將重復(fù)輸出兩個結(jié)果集合中相同記錄懈费。因此各位還是要從業(yè)務(wù)需求分析使用UNION ALL的可行性计露。 UNION 將對結(jié)果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內(nèi)存。對于這塊內(nèi)存的優(yōu)化也是相當(dāng)重要的憎乙。下面的SQL可以用來查詢排序的消耗量：

低效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'

高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95' UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'

（29）用WHERE替代ORDER BY：

ORDER BY 子句只在兩種嚴(yán)格的條件下使用索引票罐。

ORDER BY 中所有的列必須包含在相同的索引中并保持在索引中的排列順序。

ORDER BY 中所有的列必須定義為非空泞边。

WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列该押。

例如: 表DEPT包含以下列:

DEPT_CODE PK NOT NULL

DEPT_DESC NOT NULL

DEPT_TYPE NULL

低效: (索引不被使用)

SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE

高效: (使用索引)

SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0

（30）避免改變索引列的類型:

當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時， ORACLE自動對列進(jìn)行簡單的類型轉(zhuǎn)換阵谚。 假設(shè)EMPNO是一個數(shù)值類型的索引列：SELECT … FROM EMP WHERE EMPNO = ‘123'蚕礼。 實(shí)際上,經(jīng)過Oracle類型轉(zhuǎn)換, 語句轉(zhuǎn)化為: SELECT … FROM EMP WHERE EMPNO = TO_NUMBER('123') 。

幸運(yùn)的是,類型轉(zhuǎn)換沒有發(fā)生在索引列上,索引的用途沒有被改變∩沂玻現(xiàn)在,假設(shè)EMP_TYPE是一個字符類型的索引列：SELECT … FROM EMP WHERE EMP_TYPE = 123 奠蹬。

這個語句被Oracle轉(zhuǎn)換為: SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123。因?yàn)閮?nèi)部發(fā)生的類型轉(zhuǎn)換嗡午， 這個索引將不會被用到! 為了避免Oracle對你的SQL進(jìn)行隱式的類型轉(zhuǎn)換囤躁，最好把類型轉(zhuǎn)換用顯式表現(xiàn)出來。注意當(dāng)字符和數(shù)值比較時荔睹，Oracle會優(yōu)先轉(zhuǎn)換數(shù)值類型到字符類型狸演。

（31）需要當(dāng)心的WHERE子句:

某些SELECT語句中的WHERE子句不使用索引。這里有一些例子：

(1)‘!=' 將不使用索引僻他。索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中宵距。

(2)‘||'是字符連接函數(shù)。就象其他函數(shù)那樣, 停用了索引中姜。

(3)‘+'是數(shù)學(xué)函數(shù)消玄。就象其他數(shù)學(xué)函數(shù)那樣, 停用了索引跟伏。

(4)相同的索引列不能互相比較,這將會啟用全表掃描。

（32）

a. 如果檢索數(shù)據(jù)量超過30%的表中記錄數(shù)翩瓜，使用索引將沒有顯著的效率提高受扳。

b. 在特定情況下，使用索引也許會比全表掃描慢兔跌，但這是同一個數(shù)量級上的區(qū)別勘高。而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍！

（33）避免使用耗費(fèi)資源的操作：

帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執(zhí)行耗費(fèi)資源的排序(SORT)功能坟桅。DISTINCT需要一次排序操作华望，而其他的至少需要執(zhí)行兩次排序。通常仅乓，帶有 UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫赖舟。如果你的數(shù)據(jù)庫的SORT_AREA_SIZE調(diào)配得好。使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強(qiáng)夸楣。

（34）優(yōu)化GROUP BY：

提高GROUP BY 語句的效率宾抓，可以通過將不需要的記錄在GROUP BY 之前過濾掉。下面兩個查詢返回相同結(jié)果但第二個明顯就快了許多豫喧。

低效: SELECT JOB , AVG(SAL) FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'

高效: SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP JOB

http://blog.chinaunix.net/uid-21187846-id-3022916.html

7. drop delete truncate 有什么區(qū)別

1.drop(DDL) 連同表結(jié)構(gòu)及表中數(shù)據(jù)一并刪除（一段時間內(nèi)可回滾石洗，表重命名）

2.delete(DML)和truncate(DDL) 都是刪除表數(shù)據(jù)，保留表結(jié)構(gòu)紧显。兩者區(qū)別如下：

delete 對數(shù)據(jù)執(zhí)行的是虛擬刪除讲衫，只是將數(shù)據(jù)打上一個標(biāo)記，讓數(shù)據(jù)不可見孵班，因此數(shù)據(jù)依然是存在的涉兽，表所屬用戶的表空間，沒有被回收重父；

truncate將表中的數(shù)據(jù)做物理刪除花椭，因此原先裝載數(shù)據(jù)表空間可以被回收重復(fù)利用；

delete可以被恢復(fù)(事務(wù)回滾)房午，而truncate不能被恢復(fù)

delete執(zhí)行完畢可以通過事務(wù)進(jìn)行回滾矿辽，而truncate繞過事務(wù)，所以無法回滾郭厌，相應(yīng)在速度上要比delete快

刪除語法：

drop table 表名袋倔；

delete from 表名；

truncate table 表名折柠；

在速度上宾娜，一般來說，drop> truncate > delete扇售。

8.存儲過程中如何動態(tài)賦值

1前塔、直接法 :=

如：v_flag := 0;

2嚣艇、select into

如：假設(shè)變量名為v_flag，select count（*） into v_flag from students;

3华弓、execute immediate 變量名（一般是sql的select語句） into 變量名

如：

v_sqlfalg := 'select count(*) from user_tables where table_name='''||v_tablename || '''';

execute immediate v_sqlfalg into v_flag;

其中食零，v_tablename也是變量

當(dāng)然2和3實(shí)質(zhì)是一樣的。只不過3中的select語句是根據(jù)變量生成的寂屏。

在存儲過程中贰谣，是不能直接寫select語句的。

mysql

1. mysql如何分頁

limit 基本實(shí)現(xiàn)方式

一般情況下迁霎，客戶端通過傳遞 pageNo（頁碼）吱抚、pageSize（每頁條數(shù)）兩個參數(shù)去分頁查詢數(shù)據(jù)庫中的數(shù)據(jù)，在數(shù)據(jù)量較锌剂（元組百/千級）時使用 MySQL自帶的 limit 來解決這個問題：

select * from table limit (pageNo-1)*pageSize, pageSize;

收到客戶端{(lán)pageNo:1,pagesize:10}

select * from table limit 0, 10;

收到客戶端{(lán)pageNo:5,pageSize:30}

select * from table limit 120,30;

建立主鍵或者唯一索引

在數(shù)據(jù)量較小的時候簡單的使用 limit 進(jìn)行數(shù)據(jù)分頁在性能上面不會有明顯的緩慢秘豹，但是數(shù)據(jù)量達(dá)到了萬級到百萬級sql語句的性能將會影響數(shù)據(jù)的返回。這時需要利用主鍵或者唯一索引進(jìn)行數(shù)據(jù)分頁芝此；

假設(shè)主鍵或者唯一索引為 good_id

收到客戶端{(lán)pageNo:5,pagesize:10}

select col1,col2 from table where good_id > (pageNo-1)*pageSize limit pageSize;

-–返回good_id為40到50之間的數(shù)據(jù)

基于數(shù)據(jù)再排序

當(dāng)需要返回的信息為順序或者倒序時憋肖，對上面的語句基于數(shù)據(jù)再排序因痛。order by ASC/DESC(順序或倒序 默認(rèn)為順序)

select col1,col2 from table where good_id > (pageNo-1)*pageSize order by good_id limit pageSize;

–-返回good_id為40到50之間的數(shù)據(jù),數(shù)據(jù)依據(jù)good_id順序排列

基于索引使用prepare

MySQL中,可用如下方法:

（第一個問號表示pageNum婚苹，第二個？表示每頁元組數(shù)）

PREPARE stmt_name FROM

SELECT * FROM 表名稱 WHERE id_pk > (鸵膏？* 膊升？) ORDER BY id_pk ASC LIMIT M。

--適應(yīng)場景: 大數(shù)據(jù)量谭企。

--原因: 索引掃描,速度會很快. prepare語句又比一般的查詢語句快一點(diǎn)廓译。

利用MySQL支持ORDER操作可以利用索引快速定位部分元組,避免全表掃描

--比如: 讀第1000到1019行元組(pk是主鍵/唯一鍵)。

SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

利用"子查詢/連接+索引"快速定位元組的位置,然后再讀取元組. 道理同上

--如(id是主鍵/唯一鍵):

利用子查詢示例:

SELECT * FROM your_table WHERE id <=

(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc LIMIT $pagesize

利用連接示例:

SELECT * FROM your_table AS t1

JOIN(SELECT id FROM your_table ORDER BY

id desc LIMIT ($page-1)*$pagesize AS t2

WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

2. limit 5债查，10查詢的是多少條數(shù)據(jù)

10

3.想在一個有重復(fù)列上建立索引 怎么辦

https://www.cnblogs.com/liyasong/archive/2018/09/12/mysql_index.html

CREATE?INDEX?index_name?ON?table_name (key_part,...)

ALTER?TABLE?t1?ADD?INDEX?index_name (key_part)

創(chuàng)建組合索引

CREATE TABLE address (

? ? provincial VARCHAR (10),

? ? city VARCHAR (10),

? ? county VARCHAR (10),

? ? INDEX (provincial, city, county)

)

　　這里的索引是這樣創(chuàng)建的：首先按照省排序非区，然后，再根據(jù)同一個省的內(nèi)容盹廷，按照市進(jìn)行排序征绸，最后，按照縣去排序俄占。即管怠，首先按照第一列進(jìn)行索引排序，如果第一列內(nèi)容一致缸榄，那么按照第二列進(jìn)行排序渤弛，以此類推。

創(chuàng)建前綴索引

創(chuàng)建前綴索引語法如下(這里的10 表示截取前10個字符)：

CREATE?INDEX?key_part_name?ON?table_name (key_part(10));

ALTER?TABLE?table_name?ADD?INDEX?index_name (key_part(10))

????前綴索引可以兼顧索引大小和查詢速度甚带∷希可以利用相對更小的空間佳头，用更快的速度，查出數(shù)據(jù)晴氨。但是它也有缺點(diǎn)：前綴索引無法用于 ORDER BY 和 GROUP BY 操作畜晰，也不能用于索引覆蓋。

查看interfacerecord表的索引

show index from interfacerecord;

4.刪除數(shù)據(jù)后如何進(jìn)行數(shù)據(jù)恢復(fù)

要確保mysql開啟了binlog日志功能

在/etc/my.cnf文件里的[mysqld]區(qū)塊添加：

log-bin=mysql-bin

然后重啟mysql服務(wù)使功能生效

可以通過命令“show binlog events;”查看配置是否成功 瑞筐。

通過mysqlbinlog恢復(fù)MySQL數(shù)據(jù)的兩種方法：

(1)時間date

通過cmd運(yùn)行到binlog所在的目錄凄鼻，再錄入：

mysqlbinlog –stop-date=”2011-02-27 12:12:59″ jbms_binlog.000001 | mysql -uroot -proot

恢復(fù)到2011-02-27 12:12:59之前的數(shù)據(jù)，如果是start-date,即從該時間點(diǎn)后的開始算起

(2)位置position：

先將binlog日志轉(zhuǎn)換成txt：

mysqlbinlog –start-date=”2011-02-27 13:10:12″ –stop-date=”2011-02-27 13:47:21″ jbms_binlog.000002 > temp/002.txt

功能是將時間段內(nèi)的日志文件轉(zhuǎn)換成txt聚假，注意binlog和temp文件夾是同一目錄下块蚌。

查看生成的txt文件，獲取編輯位置和時間等數(shù)據(jù)膘格，然后通過命令：

mysqlbinlog –stop-position=”98″ jbms_binlog.000002 | mysql -uroot -proot

即可恢復(fù)位置為“98”處操作的數(shù)據(jù)峭范，其中stop也可以換成start。

注意：如果通過drop table name;將表刪除瘪贱，要想恢復(fù)數(shù)據(jù)纱控，必須建立一個表名、字段和數(shù)據(jù)類型相一致的空表菜秦，否則數(shù)據(jù)無法恢復(fù)甜害，報表不存在的錯誤。