老爹寫程式

於 Oracle SQL Tuning Pocket Reference, By Mark Gurry 是這樣說的 :
1. 哪一個比較快是要看情況的. 使用 exists 時, 是由外面的 table 當作 driving table. 使用 in 時, 先執行子查詢, 再把此結果與外面的 table 做 join.
2. 一般而言, exists 會比 in 快. 除非使用 in 時, 子查詢的筆數很少, 外面的筆數很多. (文中的舉例是子查詢2,000筆, 外面的 table 16,000,000 筆)

imp 遇到以下的錯誤, 這是怎樣一回事.
m_row$$ 這個欄位心裡有印象, 記得在以下兩種情況, oracle 都會幫忙建一個 unique index, 裡面就是這個欄位. 心裡有這個印象, 但沒有特別去注意 ...
1. 建 mview 沒有 PK 時.
2. 做 online redefinition 沒有 PK 時.

像我們使用 oracle, 如果要將一個 Table 的資料自 A DB 複製到 B DB, 當然有很多做法, 像使用 MView 就是一個極為簡單的解決方案, 但如果是多個要複製到一個, 例如 A, C DB 複製到 B DB, 又不想因為 A DB 有問題中斷服務, 導致 C DB 的資料沒有複製到 B DB. 目前想到的就是自己使用 Trigger 做 Replication 的行為.
舉一個最簡單的例子, 假設要處理一個產品定義的 Prod Table, Schema 如下, 我們將對這一個 table 所做的 DML 都記錄起來, 然後再將這些 DML 拿到目的 DB 去執行就可以達成我們的目的.
--要複製資料的 table, PK {prod_id}
CREATE TABLE Prod

ORA-14402: updating partition key column would cause a partition change
這是一個發生在 Partition Table 的問題, 例如我們以 Save_Time 當做分割 Partition 的欄位, 且一個月一個 Partition. 所以假設我們的 Table 裏有下列的 Partition.
PARTITION BY RANGE (Save_Time)
(
PARTITION Par_2010_01 VALUES LESS THAN (TO_DATE(' 2010-02-01 ','YYYY-MM-DD')) TABLESPACE TBS_2010_01,
PARTITION Par_2010_02 VALUES LESS THAN (TO_DATE(' 2010-03-01 ','YYYY-MM-DD')) TABLESPACE TBS_2010_02,
PARTITION Par_2010_03 VALUES LESS THAN (TO_DATE(' 2010-04-01 ','YYYY-MM-DD')) TABLESPACE TBS_2010_03,
);

這一個錯誤是因為 Bug# 6966286 導致, 可能發生的版本於 10.2 ~ 11.2.
嚴重可以使 DB Crashed, 有 patch Set , 所以遇到了, 就上一上問題就解了.
如果不行, 也可以 set _library_cache_advice = false

誤刪 oracle data file 有怎樣的情況呢?
1. 輕微的, select 到的 table 是儲存此 data file 會有 error.
2. DB Crashed.
如果只是狀況一, 可以直接將該 table 所屬的 table space(如果沒有別的 table 使用), table 刪除.

 
Oracle over partition by
假設一個公司的薪資資料如下.
DEPT  NAME  SALARY
-------------------------
RD1 ANDY 120
RD1 KEN 140
RD1 KUO 180
HR1 BEN 180
RD2 YANG 130
RD2 RAY 180
1. 統計每個人佔部門總薪資的比例
select dept,name,salary, salary/sum(salary)over(partition by dept) as ratio from salary
HR1 BEN 180 1
RD1 ANDY 120 0.27
RD1 KEN 140 0.31
RD1 KUO 180 0.40
RD2 YANG 130 0.41
RD2 RAY 180 0.58
2. 統計每個人佔整公司的比例
select dept,name,salary, salary/sum(salary)over(partition by null) as ratio from salary
RD1 ANDY 120 0.12
RD1 KEN 140 0.15
RD1 KUO 180 0.19
HR1 BEN 180 0.19
RD2 YANG 130 0.13
RD2 RAY 180 0.19
3. 排序每個部門薪資由高至低
select dept,name,salary, rank() over(partition by dept order by salary desc) as rank from salary
HR1 BEN 180 1
RD1 KUO 180 1
RD1 KEN 140 2
RD1 ANDY 120 3
RD2 RAY 180 1
RD2 YANG 130 2
4. 排序整公司薪資由高至低 rank()
select dept,name,salary, rank() over(partition by null order by salary desc) as rank from salary
RD1 KUO 180 1
HR1 BEN 180 1
RD2 RAY 180 1
RD1 KEN 140 4
RD2 YANG 130 5
RD1 ANDY 120 6
4. 排序整公司薪資由高至低 dense_rank()
select dept,name,salary, dense_rank() over(partition by null order by salary desc) as rank from salary
RD1 KUO 180 1
HR1 BEN 180 1
RD2 RAY 180 1
RD1 KEN 140 2
RD2 YANG 130 3
RD1 ANDY 120 4
5. 最後要提一個觀念就是 partition by 的欄位值與 order by 是無關的.
select dept,name,salary, rank() over(partition by dept order by salary desc) as rank from salary order by name
RD1 ANDY 120 3
HR1 BEN 180 1
RD1 KEN 140 2
RD1 KUO 180 1
RD2 RAY 180 1
RD2 YANG 130 2

 
ODP 不同版本間的差異
1. 今天又被 ODP 擺一道, 相同的程式碼( OracleDataAdapter.Update ) 在 Production 及 別人的電腦會有 Concurrency violation: the UpdateCommand affected 0 of the expected 1 records. 的錯誤, 卻可以在我的電腦正常執行, 因為已經有研究過 Concurrency 的議題過, 所以很直覺是 ODP 不同版本支援程度不同導致, 使用 ProcessExplorer 確認一下 Oracle.DataAccess 版本分別為 9.2.0.4 與 10.2.0.1, 然後自行指定 OracleDataAdapter.UpdateCommand 問題就解決了.
PS. 當 DataSet 內只有 Insert 的資料, 不會有錯誤, 但當 DataSet 內同時存在 insert/update 的資料時, 才有以上的差異.
2. 今天又來一次了, 兩個人跑相同的 unit test 程式, 一個可以過, 另一個 fail. fail 的地方是 insert 到 DB 後的資料確認, 發現欄位資料不一致. trace 一下卻發現, 有一個版本的 ODP 會將 space 去除, 例如 insert “ABC  ” (最後有兩個空白), select 出來的資料一個是 “ABC  ”(空白依然存在), “ABC”(空白不見了).

Dropping / Recreating Temporary Tablespace
當我們想要改變 temporary tablespace 大小空間. 可依循下步驟.
Step 1 : 建立另一個 temporary tablespace (假設叫 TEMP2).
SQL> CREATE TEMPORARY TABLESPACE temp2
2  TEMPFILE '/oradata/temp2_01.dbf' SIZE 5M REUSE
3  AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Step 2 : 改變資料庫內目前所使用的  temporary tablespace , (當然也可以依據每個 user 改變)
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Oracle 提供 bluk collect 語法可以一次取出多筆紀錄, 比起原先 loop fetch 一筆效能增加不少.
範例一：
(1)定義 type
　　CREATE OR REPLACE TYPE string_array AS TABLE OF VARCHAR2(100);
(2)在存儲過程裏面測試
　　DECLARE
　　v_array string_array;
　　BEGIN
　　SELECT cust_name BULK COLLECT INTO v_array
　　FROM all_tables c;
　　FORALL idx IN 1..v_table.COUNT
　　INSERT INTO cust_test VALUES(v_array(idx));
　　COMMIT;
　　END;
---------------------------------------------------------------------------------------------------------------
PROCEDURE BULK_INSERT
IS
TYPE t_record IS TABLE OF all_talbes%ROWTYPE;
 l_table t_record;
 CURSOR curTab
 IS
SELECT *
FROM all_tables a;
BEGIN
 OPEN curOriTab;
 LOOP
FETCH curTab BULK COLLECT INTO l_table LIMIT 5000;
   
FORALL i IN 1..l_table.COUNT
INSERT INTO cust_test VALUES l_table(i);
EXIT
 WHEN curTab%NOTFOUND;
 END LOOP;
 CLOSE curTab;
END;
---------------------------------------------------------------------------------------------------------------