Learning World

2015年11月5日星期四

[SQL]Inserting values into a table with '&'

如果要insert&符號時，用SQL Plus執行會停下來
可以用以下的方式insert &

create table test (name varchar2(10));
insert into test values ('&123');

1.方式1，將define關掉
set define off;
insert into test values ('&123');

2.用ascii 取代(chr(38))
insert into test values (chr(38)||'123');

2015年9月11日星期五

[SQL] SQL Hint

最近在做大資料Update處理時，在想是否有更快的方法
最後採用的方式是先用Rowid取出每筆資料
再用rowid的方式去 Update每筆資料的內容
這樣只要一開始的select寫得好，後面的Update cost基本上是1

最後又想到可以
用SQL Hint 的 /*+ FIRST_ROWS */
但有發現用rowid update的時候吃不到index

後來在update裡面加上 SQL Hint /*+ROWID(TABLE)*/

但也有說用/*+ FIRST_ROWS */ 取資料反而效能更差
Using Oracle hint “FIRST_ROWS” to improve Oracle database performances

後來我用指定筆數的方式似乎還吃得到index

What is the Difference Between the FIRST_ROWS Hint and ROWNUM in the WHERE Clause?

*參考資料

Oracle SQL hints
SQL優化過程中常見Oracle HINT的用法

2015年8月19日星期三

[SQL]Fast Update database with more than 10 million records

找資料後比較過好用的作法
1.先把要update的欄位取出來
2.用bulk collect的方式，取rowid去update

測試過，原本130萬左右的資料，用原本的update跑了一個晚上(12小時)還跑不完
後來改用這個方法後，跑了一個小時就跑完了!

DECLARE
    CURSOR rec_cur IS
    SELECT DATE_ORIGIN 
    FROM MAIN_TBL WHERE DATE_ORIGIN IS NULL;

    TYPE date_tab_t IS TABLE OF DATE;

    date_tab date_tab_t;

BEGIN
    OPEN rec_cur;
    LOOP
        FETCH rec_cur BULK COLLECT INTO date_tab LIMIT 1000;
        EXIT WHEN date_tab.COUNT() = 0;

        FORALL i IN 1 .. date_tab.COUNT
            UPDATE MAIN_TBL SET DATE_ORIGIN  = '23-JAN-2012' 
            WHERE DATE_ORIGIN IS NULL;

    END LOOP;
    CLOSE rec_cur;
END;

參考：
http://stackoverflow.com/questions/9283316/fast-update-database-with-more-than-10-million-records

http://codedmi.com/questions/2130346/tune-the-plsql-limit-parameter-in-bulk-update

http://stackoverflow.com/questions/17156137/update-million-rows-using-rowids-from-one-table-to-another-oracle

2015年7月29日星期三

[SQL]判斷DML的筆數(INSERT/ UPDATE/ DELETE rowcount)

在PLSQL中，在做DML的動作時(INSERT/ UPDATE/ DELETE)時
可以直接用sql%rowcount來判斷執行的筆數

但是如果commit了，sql%rowcount會被清掉

http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_v1/appdev.102/B19257-01/dynamic.html

另外也可以看insert的資料中某個欄位的值
使用returning

2015年6月15日星期一

[SQL]delete bulk

使用bulk的方式刪檔

http://www.anysql.net/oracle/plsql_bulk_operation.html

DECLARE
  TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  tbrows ARRROWID;
  row    PLS_INTEGER;
  cursor delete_table is select row_id from testobj_rowid;
BEGIN
  open delete_table;
  loop
     fetch delete_table bulk collect into tbrows limit 250;
     FORALL row IN 1 .. tbrows.count()
       DELETE TEST.TESTOBJ WHERE rowid = tbrows(row);
     commit;
     -- dbms_lock.sleep(1);
     exit when delete_table%notfound;
  end loop;
  close delete_table;
END;
/

2015年5月20日星期三

[sql]nvl、nvl2、nullif、coalesce用法

（1）nvl

語法：nvl(expr1, expr2)

功能：如果expr1為null，則nvl函數返回expr2的值，否則返回expr1的值，如果兩個參數都為null，則返回null。

（2）nvl2

語法：nvl2(expr1, expr2, expr3)

功能：如果expr1為null，則nvl2函數返回expr3的值；如果expr1的值不為null，則nvl2函數返回expr2的值。

（3）nullif

語法：nullif(expr1, expr2)

功能：如果expr1和expr2相等，則返回null；如果expr1和expr2不想等，則返回第一個值expr1。

（4）coalesce

語法：coalesce(expr1, expr2, expr3, ..., expr n)

功能：按照順序，返回參數清單中的第一個不為null的值。例如，假如expr1不為空，則返回expr1；加入expr1為空，expr2不為空，則返回expr2，以此類推。如果參數都是null，則返回null。

http://yedward.net/?id=178

2015年4月16日星期四

[SQL]判斷是否有中文字，英文字，數字(正規表示式)

正規表示式說明及範例

--------------------------------------

[0-9] 含數字之字串

[a-z0-9] 含數字或小寫字母之字串

[a-zA-Z0-9] 含數字或字母之字串

[^] 不含括號內的字串

/^xy/ 以 “xy” 開始的字串，例如 “xyz”, “xyab”（若要比對 ^，請使用 \^）

/xy$/ 以 “xy” 結尾的字串，例如 “axy”, “abxy”以 “xy” 結尾的字串，例如 “axy”, “abxy” （若要比對 $，請使用 \$）

======================================================================

[Sample Code]

create table test_table(col1 varchar2(10), col2 varchar2(20));

insert into test_table

select '1', '測試' from dual;

insert into test_table

select '2', '123' from dual;

insert into test_table

select '3', 'abc' from dual;

insert into test_table

select '4', 'abc123' from dual;

DECLARE

li_result number;

BEGIN

select count(*)

into li_result

from test_table

-- where REGEXP_LIKE(col2, '[一-鶿]' ) and col1 = '1';

where REGEXP_LIKE(col2, '[^a-zA-Z0-9]' ) and col1 = '1';

dbms_output.put_line('result1:' || li_result);

select count(*)

into li_result

from test_table

where REGEXP_LIKE(col2, '[0-9]' ) and col1 = '2';

dbms_output.put_line('result2:' || li_result);

select count(*)

into li_result

from test_table

where REGEXP_LIKE(col2, '[a-zA-Z]' ) and col1 = '2';

dbms_output.put_line('result3:' || li_result);

select count(*)

into li_result

from test_table

where REGEXP_LIKE(col2, '[a-zA-Z0-9]' ) and col1 = '2';

dbms_output.put_line('result4:' || li_result);

END;

-- 查詢有中文的資料

SELECT column1, column2, column3, column4, column5

FROM TABLE1

WHERE REGEXP_LIKE(column1, '[一-鶿]');

http://tomkuo139.blogspot.tw/2014/02/oracle-plsql.html

2015年11月5日 星期四

2015年9月11日 星期五

2015年8月19日 星期三

2015年7月29日 星期三

2015年6月15日 星期一

2015年5月20日 星期三

2015年4月16日 星期四

2015年11月5日星期四

2015年9月11日星期五

2015年8月19日星期三

2015年7月29日星期三

2015年6月15日星期一

2015年5月20日星期三

2015年4月16日星期四