第二十回 Oracle 裏的 TOP N

剛剛又遇到同事詢問這個問題，平常我也不寫程式，都在處理資料庫的問題，一時要寫 SQL 還真寫不出來，找了一些資訊後，趕緊來筆記一下

假設一個Table S，內容如下

LOC INV
----------------- ----------
aaa 20
aaa 30
aaa 50
aaa 60
bbb 60
bbb 60
bbb 70
bbb 90
bbb 100

試著找出每個 LOC 中，INV 最多的前兩筆，用文字編輯器改一改就是要如下結果
LOC INV
---------- ----------
aaa 60
aaa 50
bbb 100
bbb 90

LOC=aaa 中 INV 最大的兩筆為 60 和 50
LOC=bbb 中 INV 最大的兩筆為 100 和 90

所以先 create 一個 Table S

create table s
( loc varchar2(20) not null,
inv number
);

然後將資料 insert
insert into s values('aaa',20);
insert into s values('aaa',30);
......後略

因為 Oracle 沒有 Top N ，所以每次在用這種找出前幾筆的 Function 時，總是很麻煩得用 ROWNUM 來做
如果是一張表格要抓前兩名，其實也不難

select * from (select * from s order by inv desc) where rownum <=2
出來的結果如下

LOC INV
---------- ----------
bbb 100
bbb 90

但是我要的不是這個東西，因此不知在 Oracle 8i 還是 9i 就有了 RANK() 和 DENSE_RANK() 這兩個函數，這邊只依我原有範例說明用法，詳細的說明 這裏 寫的更清楚。

RANK() 會產生一個 column base 在後面的 over 子句下法
所以如果要用 RANK() 產生 TOP N 的結果就如下所示的寫法

select loc,inv,rank() over (order by inv desc ) test from s
LOC INV TEST
---------- ---------- ----------
bbb 100 1
bbb 90 2
bbb 70 3
aaa 60 4
bbb 60 4
bbb 60 4
aaa 50 7
aaa 30 8
aaa 20 9

會發現多了一個 test column 上面記載了排名，特別注意這邊有三筆 60 的記錄，如果使用 row_num 這三筆會被排名為 4,5,6 ，但在 RANK() 的下法中，會統一被規類為排名4

但使用 DENSE_RANK() 時會有什麼結果呢?
select loc,inv,dense_rank() over (order by inv desc ) test from s
LOC INV TEST
---------- ---------- ----------
bbb 100 1
bbb 90 2
bbb 70 3
aaa 60 4
bbb 60 4
bbb 60 4
aaa 50 5
aaa 30 6
aaa 20 7

由此可發現使用 RANK() 時，因為第 5和第6名均被佔據了，所以下一筆會直接代入7，但使用 DENSE_RANK() ，第5名和第6名仍是存在的

但如此還是無法達成原有的需求，不過因為在 over 子句裏還有一個 partition by 的參數，可以做表分割
所以改為下列寫法
select loc,inv from (
select loc, inv,rank() over(partition by loc order by inv desc ) rank from s)
where rank <= 2;

如此就會依據 loc 去分別計算各區的排名，結果就出來了
LOC INV
---------- ----------
aaa 60
aaa 50
bbb 100
bbb 90

但是，原本我以為如果像 SQL Server 有 Top N 可用，配合 Group BY 時是可以有這種功能的，結果我錯了，SQL Server 到了 2005 後也支援 rank() Function ，才可達到類似功能，不然就得寫 SP 才做的到了。

第十九回 Oracle 8.1.7 RMAN Backup 時出現錯誤訊息

最近將一台設備使用了類似 Log Shipping 的方式轉移，所以在啟動資料庫時有 Resetlogs

之後幾天使用原來的 Backup Scripts 備份 Archive Log 一直都失敗，會出現以下錯誤


RMAN-03022: compiling command: backup
RMAN-03025: performing implicit partial resync of recovery catalog
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: backup
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242: specification does not match any archivelog in the recovery catalog

原有的 Rman Archive Log Backup Scripts 如下

run {
allocate channel t1 type disk;
allocate channel t2 type disk;
backup
filesperset 20
format '/disk01/backup/al_%s_%p_%t'
(archivelog until time 'sysdate-7' delete input skip accessible);
release channel t1;
release channel t2;
}

後來查明原因，這是因為我們 Resetlogs 後，系統還沒有產生 7 天後的 Archvelog 可供備份，所以找不到需要備份的檔案。如果你的 Backup Scripts 中，有使用名字來過濾需要備份的檔案時，找不到該檔案也會出現類似的 Error ，其實這應該只是個 Waring 才對。