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