1、數據庫優化方案1.高效地進行SQL語句設計：通常情況下,可以采用下面的方法優化 SQLM數據操作的表現：(1)減少對數據庫的查詢次數,即減少對系統資源的請求,使用快照和顯形圖 等分布式數據庫對象可以減少對數據庫的查詢次數.(2)盡量使用相同的或非常類似的 SQL語句進行查詢,這樣不僅充分利用 SQL 共享池中的已經分析的語法樹,要查詢的數據在SGA中命中的可能性也會大大增(3)預防不帶任何條件的SQL®句的執行.沒有任何條件的SQL®句在執行時, 通常要進行FTS,數據庫先定位一個數據塊,然后按順序依次查找其它數據,對 于大型表這將是一個漫長的過程.(4)如果對有些表中的數

2、據有約束,最好在建表的SQL語句用描述完整性來實現,而不是用SQL程序中實現.一、操作符優化：1、IN操作符用IN寫出來的SQL的優點是比較容易寫及清楚易懂,這比較適合現代軟件 開發的風格.但是用IN的SQL性能總是比較低的,從Oracle執行的步驟來分析 用IN的SQL與不用IN的SQL有以下區別：ORACL就圖將其轉換成多個表的連接,如果轉換不成功那么先執行IN里面的子查詢,再查詢外層的表記錄,如果轉換成功那么直接采用多個表的連接方式查詢. 由此可見用IN的SQLS少多了一個轉換的過程.一般的 SQL都可以轉換成功, 但對于含有分組統計等方面的 SQLM不能轉換了.在業務密集的SQL當中盡

3、量不 采用IN操作符.優化sql時,經常碰到使用in的語句,一定要用exists把它給換掉,由于 Oracle在處理In時是按Or的方式做的,即使使用了索引也會很慢.2、NOT IN操作符強列推薦不使用的,由于它不能應用表的索引.用 NOTEXISTS或(外連接 +判斷為空)方案代替3、IS NULL 或 IS NOT NULL操作判斷字段是否為空一般是不會應用索引的,由于B樹索引是不索引空值的.用其它相同功能的操作運算代替,a is not null 改為a>0或a>''等. 不允許字段為空,而用一個缺省值代替空值,如業擴申請中狀態字段不允許 為空,缺省為申請.預

4、防在索引列上使用IS NULL和IS NOTNULL預防在索引中使用任何可以為 空的列,ORACLE等無法使用該索引.對于單列索引,如果列包含空值,索引中 將不存在此記錄.對于復合索引,如果每個列都為空,索引中同樣不存在此記錄. 如果至少有一個列不為空,那么記錄存在于索引中.舉例 ：如果唯一性索引建立在 表的A歹U和B列上,并且表中存在一條記錄的 A,B值為(123,null), ORACLE等不接受下一條具有相同A,B值(123,null )的記錄(插入).然而如果所有的索引 列都為空,ORACL胳認為整個鍵值為空而空不等于空.因此你可以插入1000條 具有相同鍵值的記錄,當然它們都是空！由

5、于空值不存在于索引列中,所以WHERE 子句中對索引列進行空值比較將使 ORACL聘用該索引.低效：索引失效SELECT FROM DEPARTMEWHERE DEPT_CODE ISNOTNULL;高效：索引有效SELECT FROM DEPARTMENT WHERE DEPT_CODE >=0;4、>及 < 操作符大于或小于操作符大于或小于操作符一般情況下是不用調整的,由于它有索引就會采用索引查找,但有的情況下可以對它進行優化, 如一個表有100萬記錄,一個數值型字段A, 30萬記錄的A=0, 30萬記錄的A=1, 39萬記錄的A=2, 1萬記錄的A=&那么執行A

6、>2與A>=3的效果就有很大的區別了,由于 A>2時ORACL會先找出為2 的記錄索引再進行比較,而 A>=3時ORACLE直接找到=3的記錄索引.用>=<代>高效：SELECT FROM DEPARTMENT WHERE DEPT_CODE >=0;低效：SELECT*FROM EMPWHERE DEPTNO >3兩者的區別在于,前者DBMSI直接跳到第一個DEPT?于4的記錄而后者將首先定位到DEPT NO=3勺記錄并且向前掃描到第一個 DEP伙于3的記錄.5、LIKE操作符:LIKE操作符可以應用通配符查詢,里面的通配符組合可能到達幾

7、乎是任意的查詢,但是如果用得不好那么會產生性能上的問題,如 LIKE '%5400%這種查詢不會引用索引,而LIKE'X5400%那么會引用范圍索引.一個實際例子：用YW_YHJBQK 表中營業編號后面的戶標識號可來查詢營業編號YY_BHLIKE '%5400% 這個條 件會產生全表掃描,如果改成YY_BH_IKE ' X5400% OR YY_BH_IKE ' B5400% 那么會利用YY_BH勺索引進行兩個范圍的查詢,性能肯定大大提升.6、用 EXISTS替換 DISTINCT當提交一個包含一對多表信息 比方部門表和雇員表的查詢時,預防在 SELEC

8、TF句中使用DISTINCT. 一般可以考慮用EXIST替換,EXISTS®查詢更為 迅速,由于RDBMS心模塊將在子查詢的條件一旦滿足后,馬上返回結果.例子：低效：SELECTDISTINCT DEPT_NO,DEPT_NAMEFROM DEPT D , EMP EWHERE D.DEPT_NO = E.DEPT_NO高效:SELECT DEPT_NO,DEPT_NAMEFROM DEPT D WHEREEXISTSSELECT'X'FROM EMP EWHERE E.DEPT_NO = D.DEPT_NO;如：用 EXISTS 替代 IN、用 NOT EXISTS

9、fHt NOT IN:在許多基于根底表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯接.在這種情況下,使用EXISTS或NOT EXISTS1常將提升查詢的效率.在子查詢 中,NOT IN子句將執行一個內部的排序和合并.無論在哪種情況下,NOT IN都是 最低效的由于它對子查詢中的表執行了一個全表遍歷.為了預防使用NOT IN , 我們可以把它改寫成外連接Outer Joins 或NOT EXISTS.例子：高效：SELECT*FROM EM 根底表WHERE EMPNO >0ANDEXISTSSELECT'X'FROM DEPTWHERE DEPT.DEPTNO=

10、 EMP.DEPTNO AND LOC='MELB' 低效：SELECT*FROM EM 唧出表WHERE EMPNO >0AND DEPTNOINSELECT DEP TNOFROM DEPT WHERE LOC ='MELB'7、用UNIONS換OR 適用于索引列通常情況下,用UNION®換WHEREP句中的OR將會起到較好的效果.對索引列使用OR將造成全表掃描.注意,以上規那么只針對多個索引列有效.如果有 column沒有被索引,查詢效率可能會由于你沒有選擇 OR而降低.在下面的例 子中,LOC_ID和REGION1都建有索引.高效：SEL

11、ECT LOC_ID,LOC_DESC,REGIONFROM LOCATION WHERE LOC_ID =10UNIONSELECT LOC_ID , LOC_DESC , REGIONFROM LOCATION WHERE REGION ='MELBOURNE' 低效：SELECT LOC_ID,LOC_DESC,REGIONFROM LOCATION WHERE LOC_ID= 10OR REGION = 'MELBOURNE'如果你堅持要用OR,那就需要返回記錄最少的索引列寫在最前面.8、用IN來替換OR這是一條簡單易記的規那么,但是實際的執行效果還須檢

12、驗,在ORACLE8正,兩者的執行路徑似乎是相同的.低效：SELEC T.FROM LOCATION WHEROC_ID =10OR LOC_ID=20OR LOC_ID=30 高效：SELEC TFROM LOCATION WHERE LOC_IN IN 10,20,30;二、SQL®句結構優化1、SELECTS句中預防使用'*2、用 TRUNCATE代 DELETE :用TRUNCATE代DELETED除全表記錄：大數據量的表用次方法當刪除表中的記錄時,在通常情況下,回滾段rollback segments 用來存放 可以被恢復的信息.如果你沒有COMMIT務,ORACL

13、公將數據恢復到刪除之前 的狀態準確地說是恢復到執行刪除命令之前的狀況而當運用TRUNCATE：,回滾段不再存放任何可被恢復的信息.3、用 Where子句替換HAVING子句：預防使用HAVING子句,HAVING只會在檢索出所有記錄之后才對結果集進行過濾.這個處理需要排序,總計等操作.如果能通過 WHERE：句限制記錄的數 目,那就能減少這方面的開銷.非oracle中on、where、having這三個都可以 加條件的子句中,on是最先執行,where次之,having最后,由于on是先把 不符合條件的記錄過濾后才進行統計,它就可以減少中間運算要處理的數據, 按理說應該速度是最快的,where

14、也應該比having快點的4、sql語句用大寫由于oracle總是先解析sql語句,把小寫的字母轉換成大寫的再執行.5、在Java代碼中盡量少用連接符" + 連接字符串！6、預防改變索引列的類型.：當比較不同數據類型的數據時,ORACLE自動對列進行簡單的類型轉換.假設EMPNO一個數值類型的索引列.SELECT- FROM EMP WHERE EMPNO'=23'實際上,經過 ORACLE®轉換,語 旬轉化為：SELECT FROM EMP WHERE EMPNO = TO_NU MB23R(幸運的是,類型轉換沒有發生在索引列上,索引的用途沒有被改變.現在

15、,假設EMP_TYP是一個字符類型的索引列.SELECT FROM EMP WHERE EMP_TYPE =123這個語句被ORACLE換為：SELECT- -FROM EMP WHERETO_NUMBER(EMP_TYPE)=123由于內部發生的類型轉換,這個索引將不會被.到！為了預防ORACLE寸你 的SQL進行隱式的類型轉換,最好把類型轉換用顯式表現出來.注意當字符和數 值比較時,ORACL自優先轉換數值類型到字符類型 7、優化 GROUP BY:提升GROUBY語句的效率,可以通過將不需要的記錄在 GROUBY之前過濾掉.下面兩個查詢返回相同結果但第二個明顯就快了許多.低效：1SELE

16、CT JOB,AVG(SAL)FROM EMP GROUPby JOBHAVING JOB= 'PRESIDENT' OR JOB ='MANAGER'高效：1SELECT JOB,AVG(SAL)FROM EMP WHERE JOB ='PRESIDENT'OR JOB='MANAGER'GROUPby JOB數據庫優化方案1 .利用表分區分區將數據在物理上分隔開,不同分區的數據可以制定保存在處于不同磁盤 上的數據文件里.這樣,當對這個表進行查詢時,只需要在表分區中進行掃描, 而不必進行全表掃描,明顯縮短了查詢時間,另外處于不同

17、磁盤的分區也將對這 個表的數據傳輸分散在不同的磁盤I/O , 一個精心設置的分區可以將數據傳輸對 磁盤I/O競爭均勻地分散開.對數據量大的時時表可采取此方法. 可按月自動建 表分區.,|2 .別名的使用別名是大型數據庫的應用技巧,就是表名、列名在查詢中以一個字母為別名, 查詢速度要比建連接表快1.5倍.3 .索引Index的優化設計索引可以大大加快數據庫的查詢速度,索引把表中的邏輯值映射到平安的RowID,因此索引能進行快速定位數據的物理地址.對一個建有索引的大型表的查詢時,索引數據可能會用完所有的數據塊緩存空間, ORACL環得不頻繁地進 行磁盤讀寫來獲取數據,因此在對一個大型表進行分區之后

18、, 可以根據相應的分 區建立分區索引.但是個人覺得不是所有的表都需要建立索引,只針對大數據量 的表建立索引.缺點：第一,創立索引和維護索引要消耗時間, 這種時間隨著數據量的增加 而增加.第二,索引需要占物理空間,除了數據表占數據空間之外,每一個索引 還要占一定的物理空間,如果要建立聚簇索引,那么需要的空間就會更大.第三,當對表中的數據進行增加、刪除和修改的時候,索引也要動態的維護,這樣就降 低了數據的維護速度.索引需要維護：為了維護系統性能,索引在創立之后,由于頻繁地對數據進 行增加、刪除、修改等操作使得索引頁發生碎塊,因此,必須對索引進行維護.4 .調整硬盤I/O這一步是在信息系統開發之前完

19、成的.數據庫治理員可以將組成同一個表空 問的數據文件放在不同的硬盤上, 做到硬盤之間I/O負載均衡.在磁盤比較富裕 的情況下還應該遵循以下原那么：將表和索引分開；創造用戶表空間,與系統表空間system分開磁盤；創立表和索引時指定不同的表空問；創立回滾段專用的表空間,預防空間競爭影響事務的完成；創立臨時表空間用于排序操作,盡可能的預防數據庫碎片存在于多個表空問中.我們在使用物化視圖的過程中根本可以“把它當作一個實際的數據表來看待,不用再擔憂視圖本身的根底表的效率、優化等物化視圖1 .對于復雜而高消耗的查詢,如果使用頻繁,應建成物化視圖2 .物化視圖是一種典型的以空間換時間的性能優化方式3 .對

20、于更新頻繁的表慎用物化視圖4 .選擇適宜的刷新方式一般的視圖是虛擬的,而物化視圖是實實在在的數據區域,是要占據存儲空 問的.當然,物化視圖在創立和治理上和一般的視圖有不同的地方.相比來講,物 化視圖占用了一定的存儲空間,另外系統刷新物化視圖也需要消耗一定的資源, 但是它卻換來了效率和靈活性.減少IO與網絡傳輸次數1 .盡量用較少的數據庫請求,獲取到需要的數據,能一次性取出的不分屢次取出2 .對于頻繁操作數據庫的批量操作,應采用存儲過程,減少不必要的網絡傳輸 死鎖與阻塞1 .對于需要頻繁更新的數據,盡量預防放在長事務中,以免導致連鎖反響2 .不是迫不得已,最好不要在 ORACL頸機制外再加自己設

21、計的鎖3 .減少事務大小,及時提交事務4 .盡量預防跨數據庫的分布式事務,由于環境的復雜性,很容易導致阻塞5 .慎用位圖索引,更新時容易導致死鎖自動增加表分區：該程序可以做為一個 Oracle的JOB執行在每月的28日前執行考慮2月28 大的原因,自動為該用戶下的分區表增加分區.create or replace procedure guan_add_partition /*/*為一個用戶下所有分區表自動增加分區.分區的列為date類型,分區名類似:p200706./*create by David*/asv_table_name varchar2(50);v_partition_name varchar2(50);v_month char(6);v_add_month_1 char(6);v_sql_string varchar2(2000);v_add_month varchar2(20);cursor cur_part is select distinct u.table_name,max(p.partition_name)max_part_name from user_tables u,user_tab_partitions pwhere u.table_name=p.table_name and u.partitioned =