[SQL] IN CLAUSE OVER 1000 ITEMS

1.使用union all

select * from table1 where ID in (1,2,3,4,...,1000)
union all
select * from table1 where ID in (1001,1002,...)

2.使用or

select * from table1 where ID in (1,2,3,4,...,1000) or 
ID in (1001,1002,...,2000)

3.使用兩個欄位in

select ... where ('bla', colX ) in (
        ('bla', 1),
        ('bla', 2),
        ('bla', 3),
        ('bla', 4),
             ...
        ('bla', 9999)
    ) ...

4.開table

select ... 
where id in (select userId 
             from temptable_with_2000_ids ) 

Ref:http://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause

[EXCEL]比對兩欄，是否有相同值

VLOOKUP(B2, $A$2:$A$7, 1, FALSE)
 以B2儲存格查詢$A$2:$A$7範圍中完全符合的值（FALSE），並傳回其第1欄的值


Ref: http://mark0120.blogspot.tw/2013/12/excel.html

[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'); 

[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的用法

[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

[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

[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;
/

[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

[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

[SQL]PLSQL EXPLAIN PLAN

參考查效能時，cost, bytes 和 cardinality的代表意義

COST沒有單位，是一個相對值，是SQL以CBO方式解析執行計劃時，供ORACLE來評估CBO成本，選擇執行計劃用的。沒有明確的含義，但是在對比是就非常有用。
公式：COST=（Single Block I/O COST + MultiBlock I/O Cost + CPU Cost）/ Sreadtim
 
Operation：當前操作的內容。
Rows：也就是10g版本以前的Cardinality(基數)，Oracle估計當前操作的返回結果集行數。
Bytes：表示執行該步驟後返回的字節數。
Cost（CPU）：表示執行到該步驟的一個執行成本，用於說明SQL執行的代價。
Time：Oracle 估計當前操作的時間。

http://fanli7.net/a/shujuku/20120524/165775.html

[SQL]取得第N筆的值

利用rownum外層再包一個select就可以做到取第N筆的值

SELECT *
FROM (select salary2.*, rownum rnum from
             (select * from salary ORDER BY salary_amount DESC) salary2
      where rownum <= 2 )
WHERE rnum >= 2;

http://www.techonthenet.com/oracle/questions/second_highest.php