將Java進(jìn)行到底
          將Java進(jìn)行到底
          posts - 15,  comments - 66,  trackbacks - 0

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

          1.  索引的使用:

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

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

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

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

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

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

           低效:select * from emp where deptno > 3

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

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

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

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

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

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

             (2). insertupdate維表時(shí)都加上一個(gè)條件來過濾維表中已經(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),因?yàn)殡[式的游標(biāo)將會(huì)執(zhí)行兩次操作,第一次檢索記錄,第二次檢查too many rows這個(gè)exception,而顯式游標(biāo)不執(zhí)行第二次操作。

          3.  據(jù)抽取和上載時(shí)的sql優(yōu)化:

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

          oracle采用自下而上的順序解析where子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他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). 刪除全表時(shí),用truncate 替代 delete,同時(shí)注意truncate只能在刪除全表時(shí)適用,因?yàn)?/SPAN>truncateddl而不是dml

             (3). 盡量多使用commit

          只要有可能就在程序中對(duì)每個(gè)delete,insert,update操作盡量多使用commit,這樣系統(tǒng)性能會(huì)因?yàn)?/SPAN>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:這樣做排序就不必要了,效率會(huì)提高35倍。

             (8). 分離表和索引

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


          轉(zhuǎn)自:http://blog.csdn.net/eigo/archive/2006/03/02/614157.aspx
          posted @ 2006-03-04 20:34 風(fēng)蕭蕭 閱讀(471) | 評(píng)論 (0)編輯 收藏

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

          1 列出emp表中各部門的部門號(hào),最高工資,最低工資
          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 部門號(hào) from emp where job = 'CLERK' group by deptno;

          3 對(duì)于emp中最低工資小于1000的部門,列出job為'CLERK'的員工的部門號(hào),最低工資,最高工資
          select max(sal) as 最高工資,min(sal) as 最低工資,deptno as 部門號(hào) 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ù)部門號(hào)由高而低,工資有低而高列出每個(gè)員工的姓名,部門號(hào),工資
          select deptno as 部門號(hào),ename as 姓名,sal as 工資 from emp order by deptno desc,sal asc

          5 寫出對(duì)上題的另一解決方法
          (請(qǐng)補(bǔ)充)

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

          7 列出每個(gè)員工的姓名,工作,部門號(hào),部門名
          select ename,job,emp.deptno,dept.dname from emp,dept where emp.deptno=dept.deptno

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

          9 對(duì)于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 對(duì)于dept表中,列出所有部門名,部門號(hào),同時(shí)列出各部門工作為'CLERK'的員工名與工作
          select dname as 部門名,dept.deptno as 部門號(hào),ename as 員工名,job as 工作 from dept,emp
          where dept.deptno *= emp.deptno and job = 'CLERK'

          11 對(duì)于工資高于本部門平均水平的員工,列出部門號(hào),姓名,工資,按部門號(hào)排序
          select a.deptno as 部門號(hào),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 對(duì)于emp,列出各個(gè)部門中平均工資高于本部門平均水平的員工數(shù)和部門號(hào),按部門號(hào)排序
          select count(a.sal) as 員工數(shù),a.deptno as 部門號(hào) 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 對(duì)于emp中工資高于本部門平均水平,人數(shù)多與1人的,列出部門號(hào),人數(shù),按部門號(hào)排序
          select count(a.empno) as 員工數(shù),a.deptno as 部門號(hào),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 對(duì)于emp中低于自己工資至少5人的員工,列出其部門號(hào),姓名,工資,以及工資少于自己的人數(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

          posted @ 2006-03-04 20:31 風(fēng)蕭蕭 閱讀(2046) | 評(píng)論 (1)編輯 收藏

          <2006年3月>
          2627281234
          567891011
          12131415161718
          19202122232425
          2627282930311
          2345678

          常用鏈接

          留言簿(8)

          隨筆分類

          隨筆檔案

          文章分類

          文章檔案

          相冊(cè)

          收藏夾

          myfriends

          opensource

          搜索

          •  

          最新評(píng)論

          閱讀排行榜

          評(píng)論排行榜

          主站蜘蛛池模板: 江北区| 黄平县| 平武县| 东明县| 启东市| 方正县| 绿春县| 长宁区| 松滋市| 丹寨县| 仁布县| 门头沟区| 松原市| 龙海市| 武汉市| 宣恩县| 新泰市| 竹山县| 新密市| 牡丹江市| 罗源县| 玉门市| 瑞丽市| 黄山市| 清丰县| 忻州市| 双辽市| 大英县| 平阳县| 石渠县| 安国市| 理塘县| 精河县| 平原县| 独山县| 墨竹工卡县| 伊川县| 衡南县| 天门市| 西平县| 宁陵县|