oracle園地

 http://www.searchfull.net/blog/2006/11/18/1163822247129.html
http://blog.roodo.com/emisjerry/archives/cat_3885.html

一、TABLE 結構表
fphm,kshm
2014,00000001
2014,00000002
2014,00000003
2014,00000004
2014,00000005
2014,00000007
2014,00000008
2014,00000009
2013,00000120
2013,00000121
2013,00000122
2013,00000124
2013,00000125

怎樣可以得到下列結果
2014,00000001,00000005
2014,00000009,00000007
2013,00000120,00000122
2013,00000124,00000125

解法 :
SQL> SELECT b.fphm, MIN (b.kshm) Start_HM, MAX (b.kshm) End_HM
2 FROM (SELECT a.*, TO_NUMBER (a.kshm - ROWNUM) cc
3 FROM (SELECT *
4 FROM t
5 ORDER BY fphm, kshm) a) b
6 GROUP BY b.fphm, b.cc
7 /
結果
FPHM START_HM END_HM
---------- -------- --------
2013 00000120 00000122
2013 00000124 00000125
2014 00000001 00000005
2014 00000007 00000009

第二種解法
select b.fphm,min(b.kshm),max(b.kshm)
from (
select a.*,to_number(a.kshm-rownum) cc
from (
select * from t order by fphm,kshm
) a
) b
group by b.fphm,b.cc


2.高效與低效

(1)
低效:
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000;
高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;

(2)
高效:
SELECT *
FROM EMP
WHERE DEPTNO >=4
低效:
SELECT *
FROM EMP
WHERE DEPTNO >3

(3)
高效:
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"

(4)
低效: (索引失效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
高效: (索引有效)
SELECT …
FROM DEPARTMENT
WHERE DEPT_CODE >=0;

不使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0;
使用索引:
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT >0;

(5)
例如:
(低效執行約156.3秒)
SELECT …
FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER’
AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);

(高效,執行約10.6秒)
SELECT …
FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER’;

(6)
方法3 (高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE,
B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342
AND B.EMP_NO = 291;

(7)
使用DECODE函數來減少處理時
使用DECODE函數可以避免重復描述相同記錄或重復連結相同的表
例如:
SELECT COUNT(*)，SUM(SAL)
FROM　EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE　‘SMITH%’;
SELECT COUNT(*)，SUM(SAL)
FROM　EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE　‘SMITH%’;

你可以用DECODE函數更高效
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;



3.使用日期
當使用日期時,需要注意如果有超過5位小數加到日期上,這個日期會到下一天
例如:
1.
SELECT TO_DATE(‘01-JAN-93’+.99999)
FROM DUAL;
Returns:
’01-JAN-93 23:59:59’
2.
SELECT TO_DATE(‘01-JAN-93’+.999999)
FROM DUAL;
Returns:
’02-JAN-93 00:00:00’

4.以筆數多的放在前面
表 TAB1 16,384 筆
表 TAB2 1 筆
選擇TAB2作為基礎表 (最好的方法)
select count(*) from tab1,tab2 執行約0.96秒
選擇TAB2作為基礎表 (不佳的方法)
select count(*) from tab2,tab1 執行約26.09秒


5.不同表的效能
原表 :
SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;

SELECT NAME
FROM DPT
WHERE DPT_NO = 10 ;

SELECT NAME
FROM CAT
WHERE CAT_TYPE = ‘RD’;
高效 :
SELECT E.NAME , D.NAME , C.NAME
FROM CAT C , DPT D , EMP E,DUAL X
WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))
AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD’;
缺點程序可攜性低

6.刪除重覆記錄
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);


7.減少對表的查詢
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND　DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)

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


8.Update 多Column 例子:
低效:
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;

高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;

9.使用EXISTS(或NOT EXISTS)通常能提高查詢的效率.
低效:
SELECT *
FROM EMP
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB’)

高效:
SELECT *
FROM EMP
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X’
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB’)

10.用NOT EXISTS替代NOT IN,為了避免使用NOT IN ,我們可以把它改成外部連接(Outer Joins)或NOT EXISTS.
例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT=’A’);
(方法一: 高效)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = ‘A’

(方法二: 最高效)
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT ‘X’
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A’);

11.用EXISTS替代DISTINCT
低效:
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 ‘X’ FROM EMP E
WHERE E.DEPT_NO = D.DEPT_NO);

12.計算目前執行SQL的效能狀況,以下為找出最低效的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;


13.
Outer Join 外部連接
select d.deptname, e.ename from dept d, emp e where d.empno *= e.enum;
select d.deptname,e.ename from dept d, emp e where d.empno = e.enum (+);


14.檢查 table 或 view 是否存在於 Oracle
SELECT COUNT (table_name) FROM all_tables WHERE UPPER (table_name) = UPPER ('PathologyTest')
SELECT COUNT (view_name) FROM all_views WHERE UPPER (view_name) = UPPER ('v_PathologyTest')