[SQL]Remove duplicates

This will also take care of duplicates (return one row for each user_id):

SELECT * FROM (
  SELECT u.*, FIRST_VALUE(u.rowid) OVER(PARTITION BY u.user_id ORDER BY u.date DESC) AS last_rowid
  FROM users u
) u2
WHERE u2.rowid = u2.last_rowid

Ref:
http://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column

[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

[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

[SQL]increment rownum by value

參考:http://stackoverflow.com/questions/10477085/oracle-partition-by-and-row-number-keyword
http://www.techhoney.com/oracle/clause/row_number-clause-in-oracle-sql-plsql/

SELECT  LINE_ATTRIBUTE3,   ROW_NUMBER () over (partition by LINE_ATTRIBUTE3 order by LINE_ATTRIBUTE3)  FROM t_cht_si_ap_ebs_out ;


output
0214V00000009 3
0214V00000010 1
0214V00000011 1
0214V00000012 1
0214V00000012 2
0214V00000013 1
0214V00000014 1
0214V00000014 2
0214V00000014 3
0214V00000015 1
0214V00000015 2
0214V00000015 3


另外還有其他排序方式
參考：

ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN DESC)  

RANK()       OVER(PARTITION BY COLUMN ORDER BY COLUMN DESC) 

DENSE_RANK() OVER(PARTITION BY COLUMN ORDER BY COLUMN DESC) 

https://tw.news.yahoo.com/%E9%98%B2%E8%83%83%E9%A3%9F%E9%81%93%E9%80%86%E6%B5%81-%E5%B0%91%E5%96%9D%E5%92%96%E5%95%A15%E6%8B%9B%E6%90%9E%E5%AE%9A-040404333.html

[SQL] get sysdate to millisecond

利用SYSTIMESTAMP加上TO_CHAR()給格式，可以取到毫秒

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

Reference:

http://www.oracle.com/technetwork/issue-archive/2012/12-jan/o12plsql-1408561.html

http://www.techonthenet.com/oracle/functions/to_char.php

[SQL]multiple rows into a single column

參考：https://community.oracle.com/message/3788432

select deptno,
       rtrim (xmlagg (xmlelement (e, ename || ',')).extract ('//text()'), ',') enames
from emp
group by deptno

[sql]DATE to Char

time zone

select to_char(sysdate, 'MON', 'NLS_DATE_LANGUAGE=american') from dual;


Reference:

http://sqle.ru/index.php/Oracle_PL/SQL/Date_Timezone/Date_Format#Change_the_original_date_format_in_the_TO_CHAR_function

http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/SQL52F46/Default.aspx

http://gogates.blogspot.tw/2011/09/use-of-nlsdatelanguage-parameter-in.html

[SQL]將用分號將字串切割，並切換多行

select regexp_substr(mystring,'[^,]+',1,level) element
        from dual
     connect by level <= length(regexp_replace(mystring,'[^,]+')) + 1

Reference:
http://stackoverflow.com/questions/4004377/splitting-comma-separated-string-in-a-pl-sql-stored-proc

[SQL]反向查詢吃不到INDEX

剛剛在看文件的時候看到
select 的條件若有 <> 時會吃不到Index

另外也儘量不用 not in
可以用not exists or in ( select ... from table1 minus select ... from table2)

以上可以提升效能

http://stackoverflow.com/questions/3545323/why-the-select-from-table-where-id-not-in-list-of-int-ids-query-is-slow-in

[SQL]optimal not in performance

若使用not in 的效能很差
可以改用minus可以提升效能

http://dbaforums.org/oracle/index.php?showtopic=21583

http://www.dba-oracle.com/oracle_news/2005_3_31_tips_using_not_exists_minus_sql.htm

http://www.dba-oracle.com/t_cbo_existence_queries.htm

[SQL] new line sign

new line in plsql is  CHR(13) || CHR(10)

reference:http://www.orafaq.com/forum/t/59679/2/